Friday, November 16, 2012

SQL Plan Management -11g


SQL Plan Management 11g (SPM)
=============================

The dbms_spm package is the interface provided by oracle to perform controlled manipulation of plan history and sql plan base lines maintained for various SQL statements.

SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by recording and evaluating the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan
baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

SQL Baselines -

The sql base line (base sqlplan) can be captured automatically by setting the parameter optimizer_capture_sql_plan_baselines to true which will caputre the baseline for all the repeated sql statements or we can load the sql plans manually when the parameter is set to false.

When a SQL statement is hard parsed, the cost based optimizer produces several execution
plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer
compares the plan it just produced with the plans in the SQL plan baseline.

If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline
doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the
accepted plans in the SQL plan baseline and uses the one with the lowest cost.

If the execution plan originally produced by the optimizer has a lower cost than those present
in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used
until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Oracle call this a "conservative plan selection strategy", as the optimizer preferentially
uses a tried an tested execution plan, even if a new plan looks like it might perform better.

Only when the newer plan is proved to perform well will it be accepted for use.

The plans can be loaded manually using the load_plans_from_sqlset or load_plans_from_cursor_cache procedures of dbms_spm package.

Here we can see the example of creating a baseline using the manual method.

Example:-


SQL> create table emp (emp_no number, e_name varchar2(30), depart_id number);

Table created.

SQL> create table department (depart_id number primary key, department_name varchar2 (30))

Table created.

SQL> alter table emp add constraint emp_pk primary key (emp_no);

Table altered.

SQL> alter table emp add constraint dep_fk foreign key (depart_id) references department (depart_id);

Table altered.

Insert some values to deparment and emp tables:-

SQL> select * from department;

 DEPART_ID DEPARTMENT_NAME
---------- ------------------------------
       100 Software
       101 Hardware
       102 HR
       103 Admin
       104 Transport
       105 Health
       106 Electricity
       107 Wealth
       108 Miscellanious

9 rows selected.


SQL > begin
    for i in 1000..300000 loop
               insert into emp values (i,dbms_random.string('U',30),round(dbms_random.value(100,108)));
               if i mod 10000 = 0 then
                       commit;
               end if;
    end loop;
    end;
    /

PL/SQL procedure successfully completed.

Now execute the below query:-

SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;

    DEPART DEPART_NAME                      COUNT(*)
---------- ------------------------------ ----------
       100 Software                            18826
       101 Hardware                            37311
       102 HR                                  37225
       103 Admin                               37542
       104 Transport                           37416
       105 Health                              37558
       106 Electricity                         37244
       107 Wealth                              37249
       108 Miscellanous                        18627

9 rows selected.

Lets look at the plan of the above query.

SQL> select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';

SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bzd4sb9qvcmkf            0
select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1


SQL> select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bzd4sb9qvcmkf, child number 0
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1

Plan hash value: 1683054800

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |       |       |   528 (100)|          |
|   1 |  SORT GROUP BY      |            |   361K|    14M|   528   (3)| 00:00:07 |
|*  2 |   HASH JOIN         |            |   361K|    14M|   518   (1)| 00:00:07 |
|   3 |    TABLE ACCESS FULL| DEPARTMENT |     9 |   270 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP        |   361K|  4585K|   514   (1)| 00:00:07 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.


Lets load the above plan as the baseline for this query.


declare
kk PLS_INTEGER;
begin
kk := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => 'bzd4sb9qvcmkf');
end;
/

Let check the status of the baseline.

SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f       select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,  SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
                               department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
                               ame
                               order by 1

We see that this base sql shows as accepted plan and not the fixed one, which means when any other optimal plan is found for this statement then it will be added to the history .

Currently there is no index has been used in the depart_id column. Lets create a index and then
we will check the change.

SQL> create index dept_idx on emp(depart_id);

Index created.

Now we will execute the same query.


SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;

    DEPART DEPART_NAME                      COUNT(*)
---------- ------------------------------ ----------
       100 Software                            18826
       101 Hardware                            37311
       102 HR                                  37225
       103 Admin                               37542
       104 Transport                           37416
       105 Health                              37558
       106 Electricity                         37244
       107 Wealth                              37249
       108 Miscellanous                        18627

9 rows selected.

Now lets look the plan now.

SQL> select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';


SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

bzd4sb9qvcmkf            1
select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1


SQL> select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',1));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bzd4sb9qvcmkf, child number 1
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1

Plan hash value: 1683054800

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |       |       |     6 (100)|          |
|   1 |  SORT GROUP BY      |            |    82 |  3526 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |            |    82 |  3526 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENT |    82 |  2460 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP        |    82 |  1066 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_bbkzr9zjctcuze5d89d94 used for this statement


27 rows selected.



As per the above plan the sql base line SQL_PLAN_bbkzr9zjctcuze5d89d94 has been used. Its not using
the index which we have created.

SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f       select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,  SQL_PLAN_bbkzr9zjctcuz635a35b2 YES NO  NO
                               department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
                               ame
                               order by 1

SYS_SQL_b5cbf74fe2ccb35f       select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,  SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
                               department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
                               ame
                               order by 1


From the base line view we could see that we have two plans but the new one is not selected, lets check the plan of both the sql handle.


SQL> select * from table (dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_b5cbf74fe2ccb35f'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_b5cbf74fe2ccb35f
SQL text: select a.depart_id depart, b.department_name Depart_Name, count(*) from
          emp a , department b where a.depart_id = b.depart_id group by
          a.depart_id,b.department_name order by 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bbkzr9zjctcuz635a35b2         Plan id: 1666856370
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 1965216693

-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |    82 |  3526 |   175   (2)| 00:00:03 |
|   1 |  SORT GROUP BY         |            |    82 |  3526 |   175   (2)| 00:00:03 |
|*  2 |   HASH JOIN            |            |    82 |  3526 |   174   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL   | DEPARTMENT |    82 |  2460 |     2   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| DEPT_IDX   |    82 |  1066 |   171   (1)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bbkzr9zjctcuze5d89d94         Plan id: 3856178580
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1683054800

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    82 |  3526 |     6  (34)| 00:00:01 |
|   1 |  SORT GROUP BY      |            |    82 |  3526 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |            |    82 |  3526 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENT |    82 |  2460 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP        |    82 |  1066 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

51 rows selected.

From the above we could see that the plan which uses the index is having less cpu cost
than the full table scan plan.

But still the optimizer uses the high cost plan since this is the plan which been
accepted.

Lets check the execution time.

SQL> set timing on;
SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;

    DEPART DEPART_NAME                      COUNT(*)
---------- ------------------------------ ----------
       100 Software                            18826
       101 Hardware                            37311
       102 HR                                  37225
       103 Admin                               37542
       104 Transport                           37416
       105 Health                              37558
       106 Electricity                         37244
       107 Wealth                              37249
       108 Miscellanous                        18627

9 rows selected.

Elapsed: 00:00:00.44

It is taking 44 milli seconds, Let have a look on the report which will provide us a clear picture on the
differernce between the new plan (which uses the index) and the base line plan (full table scan).
We can use the function evolve_sql_plan_baseline to generate this report.

Here we passing the below parameters:-


sql_handle

SQL  statement identifier. Unless plan_name is specified, NULL  means to consider all statements with non-accepted plans in  their SQL plan baselines.

plan_name

Plan identifier. Default NULL means to consider all non accepted  plans in the SQL plan baseline of either the identified  SQL statement or all SQL statements if sql_handle is NULL.

verify

 Specifies whether to execute the plans and compare the  performance before changing non-accepted plans into accepted  plans. A performance verification involves executing a  non-accepted plan and a plan chosen from corresponding SQL  plan baseline and comparing their performance statistics. If  non-accepted plan shows performance improvement, it is  changed to an accepted plan.

■ 'YES' (Default) - verifies that a non-accepted plan gives
better performance before changing it to an accepted plan
■ 'NO' - directs not to execute plans but only to change
non-accepted plans into accepted plans

commit

Specifies whether to update the ACCEPTED status of  non-accepted plans from 'NO' to 'YES'.

■ 'YES' (Default) - perform updates of qualifying  non-accepted plans and generate a report that shows the
updates and the result of performance verification when  verify = 'YES'.

■ 'NO' - generate a report without any updates. Note that commit = 'NO' together with verify = 'NO' represents a  no-op.

SQl> set long 10000;
SQL> select dbms_spm.EVOLVE_SQL_PLAN_BASELINE (
     sql_handle => 'SYS_SQL_b5cbf74fe2ccb35f',
     plan_name => 'SQL_PLAN_bbkzr9zjctcuz635a35b2',
     verify => 'YES',
     commit => 'NO')
     from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_B5CBF74FE2CCB35F',PLAN_NA
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_b5cbf74fe2ccb35f
  PLAN_NAME  = SQL_PLAN_bbkzr9zjctcuz635a35b2
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = NO

Plan: SQL_PLAN_bbkzr9zjctcuz635a35b2
------------------------------------
  Plan was verified: Time used 2.06 seconds.
  Plan passed performance criterion: 2.93 times better than baseline plan.

                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                       9              9
  Elapsed Time(ms):               111.211         86.951              1.28
  CPU Time(ms):                   107.983         86.653              1.25
  Buffer Gets:                       1894            641              2.95
  Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:               3510              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0


###############################################################
From the above report, we came to know that the new plan which uses the index is much better than the baseline plan


Now we will accept the new plan.

SQL> select dbms_spm.EVOLVE_SQL_PLAN_BASELINE (
     sql_handle => 'SYS_SQL_b5cbf74fe2ccb35f',
     plan_name => 'SQL_PLAN_bbkzr9zjctcuz635a35b2',
     verify => 'YES',
     commit => 'YES')
     from dual;


DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_B5CBF74FE2CCB35F',PLAN_NA
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_b5cbf74fe2ccb35f
  PLAN_NAME  = SQL_PLAN_bbkzr9zjctcuz635a35b2
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_bbkzr9zjctcuz635a35b2
------------------------------------
  Plan was verified: Time used 2.15 seconds.
  Plan passed performance criterion: 2.93 times better than baseline plan.
  Plan was changed to an accepted plan.

                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                       9              9
  Elapsed Time(ms):               170.138         87.028              1.95
  CPU Time(ms):                   121.481         86.764               1.4
  Buffer Gets:                       1894            641              2.95
  Physical Read Requests:              27              0
  Physical Write Requests:              0              0
  Physical Read Bytes:            2940928              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1



####

Now we have accepted the plan.


SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f       select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,  SQL_PLAN_bbkzr9zjctcuz635a35b2 YES YES NO
                               department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
                               ame
                               order by 1

SYS_SQL_b5cbf74fe2ccb35f       select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,  SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
                               department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
                               ame
                               order by 1


Now the plan has been accepted. Let run the query now.

SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1;

    DEPART DEPART_NAME                      COUNT(*)
---------- ------------------------------ ----------
       100 Software                            18826
       101 Hardware                            37311
       102 HR                                  37225
       103 Admin                               37542
       104 Transport                           37416
       105 Health                              37558
       106 Electricity                         37244
       107 Wealth                              37249
       108 Miscellanous                        18627

9 rows selected.

Elapsed: 00:00:00.11


We could see the considerable performance improvement on the query.



SQL> select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';

SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
bzd4sb9qvcmkf            0

select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame order by 1


SQL> select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bzd4sb9qvcmkf, child number 0
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1

Plan hash value: 1965216693

-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |       |       |   184 (100)|          |
|   1 |  SORT GROUP BY         |            |    58 |   928 |   184   (6)| 00:00:03 |
|*  2 |   HASH JOIN            |            |   298K|  4671K|   176   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL   | DEPARTMENT |     9 |   108 |     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| DEPT_IDX   |   298K|  1167K|   171   (1)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_bbkzr9zjctcuz635a35b2 used for this statement


27 rows selected.



Altering sql baselines:-
=======================

The funtion ALTER_SQL_PLAN_BASELINE is used to change the attribute of a single plan or all
plans associated with the sql statment.

Here lets make the plan fixed.

SQL> set serveroutput on;
SQL> declare
     kk pls_integer;
    begin
    kk := dbms_spm.alter_sql_plan_baseline
    (
    sql_handle => 'SYS_SQL_b5cbf74fe2ccb35f',
    plan_name  => 'SQL_PLAN_bbkzr9zjctcuz635a35b2',
    attribute_name => 'fixed',
    attribute_value => 'YES'
   );
   dbms_output.put_line ('No of plans altered :' || kk);
   end;
   /


No of plans altered :1

PL/SQL procedure successfully completed.

SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f       select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,  SQL_PLAN_bbkzr9zjctcuz635a35b2 YES YES YES
                               department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
                               ame
                               order by 1

SYS_SQL_b5cbf74fe2ccb35f       select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,  SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
                               department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
                               ame
                               order by 1



We have made the plan which uses the index as the fixed plan, if we drop the index now then it will use
plan with full table scan.

SQL> drop index dept_idx;

Index dropped.

Now execute the same query.

SQL>select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;
      DEPART DEPART_NAME                      COUNT(*)
---------- ------------------------------ ----------
       100 Software                            18826
       101 Hardware                            37311
       102 HR                                  37225
       103 Admin                               37542
       104 Transport                           37416
       105 Health                              37558
       106 Electricity                         37244
       107 Wealth                              37249
       108 Miscellanous                        18627

9 rows selected.

Check for the sql_id and the child number in V$SQL.

select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';

SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

bzd4sb9qvcmkf            1
select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1


View the Explain plan.

SQL>select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',1));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bzd4sb9qvcmkf, child number 1
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1

Plan hash value: 1683054800

--------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |       |       |   526 (100)|
|   1 |  SORT GROUP BY      |            |    58 |   928 |   526   (3)| 00:00:07
|*  2 |   HASH JOIN         |            |   298K|  4671K|   518   (1)| 00:00:07
|   3 |    TABLE ACCESS FULL| DEPARTMENT |     9 |   108 |     3   (0)| 00:00:01
|   4 |    TABLE ACCESS FULL| EMP        |   298K|  1167K|   513   (1)| 00:00:07
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_bbkzr9zjctcuze5d89d94 used for this statement


27 rows selected.

We can see that the sql plan baseline SQL_PLAN_bbkzr9zjctcuze5d89d94 has been used for this statement.



Creating Sql plan baseline using the group of sqls from the AWR.
-------------------------------------------------------------------------------

This can be achvied by creating SQL tuning sets (STS) and loading STS to the baseline.


1) Create STS



SQL> begin
          dbms_sqltune.create_sqlset
          (
           sqlset_name => 'manzoor_schema_queries',
           description   => 'SQL set containing Manzoor schema queries'
           );
          end;
          /

PL/SQL procedure successfully completed.


 2) Fetch the SQL from the AWR and load it to the STS. In this example
     we are fetching the queries which are executed by Manzoor schema and loading it
      to the STS.

SQL>     declare
               cur        dbms_sqltune.sqlset_cursor;
               begin
                 open cur for select value(p) from table
                   (
                      dbms_sqltune.SELECT_WORKLOAD_REPOSITORY
                        (
                           begin_snap => 119,
                           end_snap    => 167,
                           basic_filter => 'parsing_schema_name = ''MANZOOR''')
                     ) p;
                     dbms_sqltune.load_sqlset
                       (
                          sqlset_name       =>      'manzoor_schema_queries',
                          populate_cursor =>      cur
                       );
    end;
    /

PL/SQL procedure successfully completed.


3)  View the Contents of the STS.
 


select * from table (dbms_sqltune.select_sqlset('manzoor_schema_queries'));

or

SQL> select sql_id from table (dbms_sqltune.select_sqlset('manzoor_schema_queries'));

SQL_ID
-------------
2f8g6twkxb9kk
2t7vq1zz3vxs0
3s1hh8cvfan6w
4bwrxw78c3mkt
4skyphxhrysfd
5suzhbyd00h6y
5xkzm5zygmmy2
6nnntb2b3gr0j
7hys3h7ysgf9m
96gu8dckuy91w
99qa3zyarxvms
a03rbpj8vf87r
a7hv3uyyaqhpf
acstsbcy1t89n
acstsbcy1t89n
acstsbcy1t89n
arkb04w4cm40x
bbcmhs3ygxqph
bsvpwnht1m9bm
cpm405mcw3bkj
cy33kh23mvvqd
fbgavzya57gb1
fbgavzya57gb1
fg4skgcja2cyj
fxw7k56bs4xjq
g3f3cw3zy5aat
g59vz2u4cu404


4) Create baseline using the STS.


SQL> declare
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'manzoor_schema_queries');
end;
/

5) View the baseline.

SQL> select sql_handle, sql_text , enabled, accepted from dba_sql_plan_baselines;

SQL_HANDLE                               SQL_TEXT                                                                         ENA ACC
---------------------------------------- -------------------------------------------------------------------------------- --- ---
SQL_24a5515c01211454                     select count(*) from emp                                                         YES YES
SQL_1278d3e5ddb954cd                     SELECT PLAN_TABLE_OUTPUT FROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 )  YES YES
SQL_9be38e7cab117c72                     SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'),  YES YES
SQL_4b1157e1544bc5a0                     select sum(sal) from emp where empno between :a and :b                           YES YES
SQL_4b1157e1544bc5a0                     select sum(sal) from emp where empno between :a and :b                           YES YES
SQL_ad5456be0f0aac5f                     update emp set ORA_ARCHIVE_STATE = 1 where join_date < to_date ('01-JAN-2011','D YES YES
SQL_9ed6c64b78007214                     select table_name from user_tables                                               YES YES
SQL_294c437e331fa51f                     SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))        YES YES
SQL_b0605bfc6cefbcfb                     insert /* + append */ into emp select * from emp                                 YES YES
SQL_2fabb0ed611c90e0                     delete from emp                                                                  YES YES
SQL_823bf3a9309ffdd0                     select * from dba_tab_privs where grantee = 'AHAMED'                             YES YES
SQL_823bf3a9309ffdd0                     select * from dba_tab_privs where grantee = 'AHAMED'                             YES YES
SQL_823bf3a9309ffdd0                     select * from dba_tab_privs where grantee = 'AHAMED'                             YES YES
SQL_eaa2be30102d9d49                     SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlv YES YES
SQL_30a83b4102b41c40                     SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB YES YES
SQL_e838970c4838b3ba                     select a.ename, b.dept_name from emp a, dept b where a.deptno = b.dept_id and a. YES YES
SQL_bff897b9dbcabe27                     SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P YES YES
SQL_9f1cc00482584a97                     create table emp as                                                              YES YES
SQL_ee5069d793e00152                     select * from table (dbms_xplan.display)                                         YES YES

or

SQL>  select owner,name, STATEMENT_COUNT  from DBA_SQLSET;

OWNER      NAME                                     STATEMENT_COUNT
---------- ---------------------------------------- ---------------
SYS        manzoor_schema_queries                                24


SQL> select SQL_ID,SQL_TEXT from DBA_SQLSET_STATEMENTS;

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
g59vz2u4cu404 select count(*) from emp
g3f3cw3zy5aat SELECT PLAN_TABLE_OUTPUT FROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 )
fxw7k56bs4xjq BEGIN dbms_stats.gather_table_stats('MANZOOR','EMP'); END;
fg4skgcja2cyj SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'),
fbgavzya57gb1 select sum(sal) from emp where empno between :a and :b
fbgavzya57gb1 select sum(sal) from emp where empno between :a and :b
cy33kh23mvvqd update emp set ORA_ARCHIVE_STATE = 1 where join_date < to_date ('01-JAN-2011','D
cpm405mcw3bkj select table_name from user_tables
bsvpwnht1m9bm SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
bbcmhs3ygxqph insert /* + append */ into emp select * from emp
arkb04w4cm40x delete from emp
acstsbcy1t89n select * from dba_tab_privs where grantee = 'AHAMED'
acstsbcy1t89n select * from dba_tab_privs where grantee = 'AHAMED'
acstsbcy1t89n select * from dba_tab_privs where grantee = 'AHAMED'
a7hv3uyyaqhpf SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlv
a03rbpj8vf87r SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB
99qa3zyarxvms insert into plan_table (statement_id, timestamp, operation, options,object_node,
96gu8dckuy91w select a.ename, b.dept_name from emp a, dept b where a.deptno = b.dept_id and a.
7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
6nnntb2b3gr0j INSERT INTO EMP VALUES (:B1 , DBMS_RANDOM.STRING('U',30))
5xkzm5zygmmy2 create table emp as
5suzhbyd00h6y begin
              for i in 1..50000 loop
              insert into emp values (i, dbms_random.string('U',1
4skyphxhrysfd EXPLAIN PLAN SET STATEMENT_ID='PLUS1415267676' FOR select * from emp where emp_i
4bwrxw78c3mkt INSERT INTO EMP VALUES (:B1 , DBMS_RANDOM.STRING('U',10), 'SOFTWARE',5000,SYSDAT
3s1hh8cvfan6w SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
2t7vq1zz3vxs0 begin
                  for i in 1..50000 loop
                  insert into emp values (i, dbms_random.stri




-- Baselines are created using AWR.


SQL Management Base

The SQL management base resides in the SYSAUX tablespace and stores SQL plan baselines, as well as statement logs,
plan histories and SQL profiles. Space usage is controlled by altering two name-value attributes using the CONFIGURE
procedure of the DBMS_SPM package.

space_budget_percent (default 10) : Maximum size as a percentage of SYSAUX space. Allowable values 1-50.
plan_retention_weeks (default 53) : Number of weeks unused plans are retained before being purged. Allowable values 5-523 weeks.
The current settings are visible using the DBA_SQL_MANAGEMENT_CONFIG view.

SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        10
PLAN_RETENTION_WEEKS                        53

2 rows selected.

SQL>
The following example shows both values being reset.

BEGIN
  DBMS_SPM.configure('space_budget_percent', 11);
  DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/

SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        11
PLAN_RETENTION_WEEKS                        54

2 rows selected.

SQL>



Others function/procedures in dbms_spm.

CREATE_STGTAB_BASELINE

Creates a staging table that used for transporting SQL plan
baselines from one system to another

DROP_SQL_PLAN_BASELINE

drops a single plan, or all plans associated with a SQL statement


LOAD_PLANS_FROM_SQLSET Function

Loads plans stored in a SQL tuning set (STS) into SQL plan
baselines

MIGRATE_STORED_OUTLINE Functions

Migrates existing stored outlines to SQL plan baselines

PACK_STGTAB_BASELINE Function

Packs (exports) SQL plan baselines from SQL management base
into a staging table

UNPACK_STGTAB_BASELINE Function

Unpacks (imports) SQL plan baselines from a staging table into
SQL management base

No comments:

Post a Comment