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

Stored Outlines - 10g

Store Outlines - 10g
==============

A stored outline is a collection of hints associated with a specific SQL statement that allows a
standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.

Plan stability is based on the preservation of execution plans at a point in time where the performance of a statement is considered acceptable.

The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS views should be used to
display information about existing outlines.


Creating outlines:-

Outlines can be created automatically by oracle by setting the parameter create_stored_outlines or
manually for sql statements.


The parameter create_stored_outlines can be set at session / Instance level.

For creating the outlines manually we can use the create outline DDL or dbms_outln packge.

Eg:-

SQL> grant execute_Catalog_role to manzoor;

Grant succeeded.

SQL> grant create any outline to manzoor;

Grant succeeded.

sql>conn manzoor/manzoor
connected

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 department 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.


begin
    for i in 1003..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.


SQL> select count(*) from emp;

  COUNT(*)
----------
    299001


Now we are going to create an outline for the below query:-


SQL> create outline emp_dept for category empanddept on
    select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;


Check the list of hints crated of this outline.

SQL> SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'EMP_DEPT';

      NODE      STAGE   JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------------------------------------
         1          1          0 USE_HASH(@"SEL$1" "A"@"SEL$1")
         1          1          0 LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
         1          1          2 FULL(@"SEL$1" "A"@"SEL$1")
         1          1          1 FULL(@"SEL$1" "B"@"SEL$1")
         1          1          0 OUTLINE_LEAF(@"SEL$1")
         1          1          0 ALL_ROWS
         1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
         1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS


Now execute the query.

SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;

    EMP_NO E_NAME                         DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
      1000 Manzoor                        Software
      1019 OPMQIQAMXYJHBTVRVFIWZJLZAOYTCM Software
      1032 KZTBKZKSZKGHHOMXUVANIIWVQQYBOE Software
      1048 GDBQSACOZRCONFFPMMAGHXZDSGAATC Software
      1072 UCYVYCALRSNXMLGPYAAQOJGEPQYKKD Software
      1073 YVCATTWMYUNRYUHPKIPHZHCJKSXFJF Software
      1083 DQRRSUSNHHYUCAJGFBDAVXOBDNDSGE Software
      1085 ZHXULTHXKGKTWOINPSCZXOGJTCLGQO Software
      1089 JFJGRUXLLRKASMVVPSNYVJYRJYKHFL Software


Check whether the outline is used or not.

SQL> select name, owner, category, used,enabled from dba_outlines where name = 'EMP_DEPT';

NAME                           OWNER                          CATEGORY                       USED   ENABLED
------------------------------ ------------------------------ ------------------------------ ------ --------
EMP_DEPT                       MANZOOR                        EMPANDDEPT                     UNUSED ENABLED


The above query shows that the outline is unsed.


Now let's have a look on the current explain plan.


SQL> select sql_id from V$sql where sql_text like '%a.emp_no, a.e_name%';

SQL_ID
-------------
5q10ntkvp78qh

SQL> SET LONG 10000;

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


Plan hash value: 865241754

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |       |       |     6 (100)|          |
|*  1 |  COUNT STOPKEY      |            |       |       |            |          |
|   2 |   NESTED LOOPS      |            |   288K|    20M|     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENT |     9 |   270 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP        | 32090 |  1347K|     0   (0)|          |
----------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10 p="p">   4 - filter("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - dynamic sampling used for this statement


## The plan also shows that the outline is not used.

To enable the outlines we need to enable query rewrites and indicate which outline category the instance or
session should use. This is done using the ALTER SYSTEM and ALTER SESSION commands. In the following example we
will enable stored outlines for the current session.

SQL> alter session set QUERY_REWRITE_ENABLED = TRUE;

Session altered.

SQL> ALTER SESSION SET use_stored_outlines=EMPANDDEPT;

Session altered.

Now execute the query:-

SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;

    EMP_NO E_NAME                         DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
      1000 Manzoor                        Software
      1019 OPMQIQAMXYJHBTVRVFIWZJLZAOYTCM Software
      1032 KZTBKZKSZKGHHOMXUVANIIWVQQYBOE Software
      1048 GDBQSACOZRCONFFPMMAGHXZDSGAATC Software
      1072 UCYVYCALRSNXMLGPYAAQOJGEPQYKKD Software
      1073 YVCATTWMYUNRYUHPKIPHZHCJKSXFJF Software
      1083 DQRRSUSNHHYUCAJGFBDAVXOBDNDSGE Software
      1085 ZHXULTHXKGKTWOINPSCZXOGJTCLGQO Software
      1089 JFJGRUXLLRKASMVVPSNYVJYRJYKHFL Software

9 rows selected.

SQL> SELECT name, category, used FROM user_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
EMP_DEPT                       EMPANDDEPT                     UNUSED


Now we can see that the stored outlines is used. Let look at the plan now.

SQL> select sql_id, child_number from V$sql where sql_Text like '%a.emp_no, a.e_name, b.department_%';


SQL_ID        CHILD_NUMBER
------------- ------------
5q10ntkvp78qh            0
5q10ntkvp78qh            1
5q10ntkvp78qh            2

There are 3 childs are available for this sql now. Lets look the plan for each.




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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5q10ntkvp78qh, child number 0
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10

Plan hash value: 865241754

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |       |       |     6 (100)|          |
|*  1 |  COUNT STOPKEY      |            |       |       |            |          |
|   2 |   NESTED LOOPS      |            |   288K|    20M|     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENT |     9 |   270 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP        | 32090 |  1347K|     0   (0)|          |
----------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10 p="p">   4 - filter("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - dynamic sampling used for this statement


27 rows selected.


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5q10ntkvp78qh, child number 1
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10

Plan hash value: 865241754

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |       |       |     6 (100)|          |
|*  1 |  COUNT STOPKEY      |            |       |       |            |          |
|   2 |   NESTED LOOPS      |            |   288K|    20M|     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENT |     9 |   270 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP        | 32090 |  1347K|     0   (0)|          |
----------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10 p="p">   4 - filter("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - dynamic sampling used for this statement


27 rows selected.

SQL> select * from table (dbms_xplan.display_cursor('5q10ntkvp78qh',2));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5q10ntkvp78qh, child number 2
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10

Plan hash value: 3200020666

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

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

   1 - filter(ROWNUM<10 p="p">   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - outline "EMP_DEPT" used for this statement


27 rows selected.



## We could see that the child 1 and the child 2 plans are not using the stored outlines
and also the cost is high when compared to child 3 plan which is using the outline.


Now lets create index on the depart_id on emp table and check.


SQL> create index dept_idx on emp(depart_id);

Index created.


Now lets execute the same query.

SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;

    EMP_NO E_NAME                         DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
      1000 Manzoor                        Software
      1001 Ahamed                         Hardware
      1002 Jameel                         Hardware
      1003 KZPWKAVBCXRXCOEBQLLQBIJQWZPGDA HR
      1004 VPZVDOTYLJWCMFYOUIWSCYAHGYEDOB Hardware
      1005 WSWNBQHCMZHNDGGXLFWIJCBOBODYSP Wealth
      1006 VDFJDDNBBTQKVJFYSMVNMQDQYHALTG Hardware
      1007 YQEQRGTHYFVGESHAHOKRLITMULFUWK Health
      1008 ZRYGBCCMXXHCRFYDYNRRWCAPSBKLOQ Admin

9 rows selected.


Lets see what is the execution plan which is used now.


SQL> select sql_id, child_number from V$sql where sql_Text like '%a.emp_no, a.e_name, b.department_%';

SQL_ID        CHILD_NUMBER
------------- ------------
5q10ntkvp78qh            0


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5q10ntkvp78qh, child number 0
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10

Plan hash value: 3200020666

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

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

   1 - filter(ROWNUM<10 p="p">   2 - access("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - outline "EMP_DEPT" used for this statement


27 rows selected.


## We could see that even though the index is created the index is not created
and still it used the stored outline for the explain plan. Now we d

SQL> conn manzoor/manzoor
Connected

SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;

    EMP_NO E_NAME                         DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
      1000 Manzoor                        Software
      1019 OPMQIQAMXYJHBTVRVFIWZJLZAOYTCM Software
      1032 KZTBKZKSZKGHHOMXUVANIIWVQQYBOE Software
      1048 GDBQSACOZRCONFFPMMAGHXZDSGAATC Software
      1072 UCYVYCALRSNXMLGPYAAQOJGEPQYKKD Software
      1073 YVCATTWMYUNRYUHPKIPHZHCJKSXFJF Software
      1083 DQRRSUSNHHYUCAJGFBDAVXOBDNDSGE Software
      1085 ZHXULTHXKGKTWOINPSCZXOGJTCLGQO Software
      1089 JFJGRUXLLRKASMVVPSNYVJYRJYKHFL Software

9 rows selected.

Let see the explain plan now.


SQL> select sql_id, child_number from V$sql where sql_Text like '%a.emp_no, a.e_name, b.department_%';

SQL_ID        CHILD_NUMBER
------------- ------------
5q10ntkvp78qh            0
5q10ntkvp78qh            1


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5q10ntkvp78qh, child number 1
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b where
a.depart_id = b.depart_id and rownum < 10

Plan hash value: 2690663879

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |     6 (100)|          |
|*  1 |  COUNT STOPKEY               |            |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP        | 32090 |  1347K|     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |            |   288K|    20M|     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | DEPARTMENT |     9 |   270 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | DEPT_IDX   |    10 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10 p="p">   5 - access("A"."DEPART_ID"="B"."DEPART_ID")

Note
-----
   - dynamic sampling used for this statement


Now the outline is not used.

SQL Performance Analyzer (SPA)

SQL Performance Analyzer:-
=========================
Before you make any change on the database (hardware, init parameters, indexes , Mviews etc), we
can use the SPA to find out how much the change will impact (improve / reduce) the sql performance.

Here we use the sql tunings sets with SPA as an example.


Test Case:-
==========

SQL> create user test_user identified by test default tablespace ahamed_tblspc temporary tablespace temp1;

User created.

SQL> ALTER USER test_user QUOTA UNLIMITED ON ahamed_tblspc;

User altered.

SQL> grant dba to test_user;

Grant succeeded.

SQL> conn test_user
Enter password:
Connected.


SQL> create table test_object as select * from dba_objects;

Table created.

SQL> SELECT COUNT(*) FROM TEST_OBJECT;

  COUNT(*)
----------
    575768

SQL> exec dbms_stats.gather_table_stats('TEST_USER','TEST_OBJECT');

PL/SQL procedure successfully completed.


SQL> set timing on;

SQL> select count(*) from test_object where object_id = 2000;

  COUNT(*)
----------
         8

Elapsed: 00:00:01.17
SQL> select count(*) from test_object where object_id between 1000 and 2000;

  COUNT(*)
----------
      7920

Elapsed: 00:00:01.20
SQL> select count(*) from test_object where object_id > 3000;

  COUNT(*)
----------
    552312

Elapsed: 00:00:01.15
SQL> select count(*) from test_object where object_id < 3000;

  COUNT(*)
----------
     23448

Elapsed: 00:00:01.21


The above query took 1.21 seconds for its execution lets

We are using the object_id column in the where clause, all the above queries
will be using the full table scan since we dont have the index on the object_id
column.

Now lets use the SPA to analyze how much the sql performance will
get increased if we use an index on the object_id column.


Lets create the sqlsets using dbms_sqltune package.


PROCEDURE CREATE_SQLSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT


SQL> exec dbms_sqltune.create_sqlset (sqlset_name => 'tuning_test_object', description => 'For testing SPA');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43


Now using the select_cursor_cache function we can retrive the sql which been parsed by
the test_user and then using the load_sqlset procedure we need to load the sqls to the sqlset tuning_test_object.


FUNCTION SELECT_CURSOR_CACHE RETURNS SQLSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BASIC_FILTER                   VARCHAR2                IN     DEFAULT
 OBJECT_FILTER                  VARCHAR2                IN     DEFAULT
 RANKING_MEASURE1               VARCHAR2                IN     DEFAULT
 RANKING_MEASURE2               VARCHAR2                IN     DEFAULT
 RANKING_MEASURE3               VARCHAR2                IN     DEFAULT
 RESULT_PERCENTAGE              NUMBER                  IN     DEFAULT
 RESULT_LIMIT                   NUMBER                  IN     DEFAULT
 ATTRIBUTE_LIST                 VARCHAR2                IN     DEFAULT


PROCEDURE LOAD_SQLSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN
 POPULATE_CURSOR                REF CURSOR              IN
 LOAD_OPTION                    VARCHAR2                IN     DEFAULT
 UPDATE_OPTION                  VARCHAR2                IN     DEFAULT
 UPDATE_CONDITION               VARCHAR2                IN     DEFAULT
 UPDATE_ATTRIBUTES              VARCHAR2                IN     DEFAULT
 IGNORE_NULL                    BOOLEAN                 IN     DEFAULT
 COMMIT_ROWS                    BINARY_INTEGER          IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT



declare
sql_cur dbms_sqltune.sqlset_cursor;
begin
open sql_cur for
select value(a) from table
(
dbms_sqltune.select_cursor_cache
(
basic_filter => 'sql_text like ''%test_object where object_id%'' and parsing_schema_name = ''TEST_USER''',
attribute_list  => 'ALL'
)
) a;
dbms_sqltune.load_sqlset
(
sqlset_name => 'tuning_test_object',
populate_cursor => sql_cur
);

end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.69


Now we can view the dba_sqlset_statements view to find out the sql's which
are loaded in the sqlset.


SQL> SELECT sql_text FROM   dba_sqlset_statements WHERE  sqlset_name = 'tuning_test_object';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from test_object where object_id = 2000
select count(*) from test_object where object_id > 3000
select count(*) from test_object where object_id < 3000
 SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, cast(NU
SELECT VALUE(A) FROM TABLE ( DBMS_SQLTUNE.SELECT_CURSOR_CACHE ( BASIC_FILTER =>
select count(*) from test_object where object_id between 1000 and 2000

6 rows selected.

Elapsed: 00:00:00.17

Now we can start using the SPA. Package name dbms_sqlpa.

1) Create the analysis task.


FUNCTION CREATE_ANALYSIS_TASK RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN     DEFAULT
 BASIC_FILTER                   VARCHAR2                IN     DEFAULT
 ORDER_BY                       VARCHAR2                IN     DEFAULT
 TOP_SQL                        NUMBER                  IN     DEFAULT
 TASK_NAME                      VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT



SQL> variable v_task varchar2(64);
SQL> exec :v_task := dbms_sqlpa.create_analysis_task(sqlset_name => 'tuning_test_object', sqlset_owner => 'TEST_USER');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24
SQL> print :v_task;

V_TASK
--------------------------------------------------------------------------------
TASK_127


2) Execute the analysis task.

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'before_change');
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.59


SQL> conn test_user/test
Connected.

SQL> create index idx_obj_id on test_object (object_id);

Index created.

Elapsed: 00:00:01.82

SQL> EXEC DBMS_STATS.gather_table_stats('TEST_USER','TEST_OBJECT',CASCADE=> true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.26

SQL> conn / as sysdba
Connected.

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/

Now run the comparison analysis task

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => :v_task,
    execution_type   => 'compare performance',
    execution_params => dbms_advisor.arglist(
                          'execution_name1',
                          'before_change',
                          'execution_name2',
                          'after_change')
    );
END;
/


FUNCTION REPORT_ANALYSIS_TASK RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TASK_NAME                      VARCHAR2                IN
 TYPE                           VARCHAR2                IN     DEFAULT
 LEVEL                          VARCHAR2                IN     DEFAULT
 SECTION                        VARCHAR2                IN     DEFAULT
 OBJECT_ID                      NUMBER                  IN     DEFAULT
 TOP_SQL                        NUMBER                  IN     DEFAULT
 EXECUTION_NAME                 VARCHAR2                IN     DEFAULT
 TASK_OWNER                     VARCHAR2                IN     DEFAULT
 ORDER_BY                       VARCHAR2                IN     DEFAULT




SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON


SQL> SELECT DBMS_SQLPA.report_analysis_task('TASK_127','TEXT','ALL') from dual;
General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : TASK_127                        SQL Tuning Set Name        : tuning_test_object
  Task Owner   : SYS                             SQL Tuning Set Owner       : TEST_USER
  Description  :                                 Total SQL Statement Count  : 6

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name  : EXEC_118               Started             : 11/15/2012 08:44:20
  Execution Type  : COMPARE PERFORMANCE    Last Updated        : 11/15/2012 08:44:21
  Description     :                        Global Time Limit   : UNLIMITED
  Scope           : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status          : COMPLETED              Number of Errors    : 0

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                       After Change Execution:
 ---------------------------------------------  ---------------------------------------------
  Execution Name      : before_change            Execution Name      : after_change
  Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
  Status              : COMPLETED                Status              : COMPLETED
  Started             : 11/15/2012 08:34:28      Started             : 11/15/2012 08:43:38
  Last Updated        : 11/15/2012 08:34:40      Last Updated        : 11/15/2012 08:43:43
  Global Time Limit   : UNLIMITED                Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                   Per-SQL Time Limit  : UNUSED
  Number of Errors    : 0                        Number of Errors    : 0

 ---------------------------------------------
 Comparison Metric: ELAPSED_TIME
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------

Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  97.89%
 Improvement Impact  :  97.89%
 Regression Impact   :  0%

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall               6                  4
 Improved              4                  4
 Unchanged             2                  0

Top 6 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric  | Metric | Impact | Plan   |
| object_id | sql_id        | Workload  | Frequency | Before  | After  | on SQL | Change |
------------------------------------------------------------------------------------------
|        18 | 6nrcat73nzqhw |    28.03% |         1 | 1415497 |    935 | 99.93% | y      |
|        21 | cr2bsxvmby2zb |     24.5% |         1 | 1236756 |    352 | 99.97% | y      |
|        16 | 0xhqjft6b43s9 |     23.2% |         1 | 1170776 |     64 | 99.99% | y      |
|        17 | 5ygm3n7fs0715 |    22.16% |         1 | 1153815 |  35505 | 96.92% | y      |
|        20 | avd78ah9m0x86 |      .03% |         1 |   36901 |  35139 |  4.77% | n      |
|        19 | 7p7xh2a92f4qm |      .02% |         1 |   32632 |  31473 |  3.55% | n      |
------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------


Now we could see that there is around 97% of improvement on the sqls.