Wouldn’t “Automatic SQL Plan Management” be the 11g “killer app” ?

I’m just wondering… be careful it is part of Oracle Tuning Pack 11g !

Anyway, before you answer this question, lets illustrate with an example how the “non-automatic”, “no tuning Pack” SQL Plan Management works. This 11g new feature completes (replaces ?) the outlines by providing a new plan stability capability to Oracle database 11g Enterprise Edition.

If you want the truth behind this example, you should refer before anything else to the 11g documentation :

• Oracle 11g Reference :
  • optimizer_capture_sql_plan_baselines (Tuning Pack)

Step 1 : Sample schema

We’ll need a table with a couple of rows to demonstrate this feature :

create table gark
    (id number not null);
begin
  for i in 1..10000 loop
    insert into gark(id)
               values (i);
  end loop;
  commit;
end;
/

exec dbms_stats.gather_table_stats(user, 'GARK')

Step 2 : Query and Plan

We’ll need to use SQL*Plus ! I haven’t been able to use SQL*Developer as the last session query, from my experience is not the one you’ve just run even with “set serveroutput off” and I don’t want to use “explain plan” neither “set autotrace on”. Connected as the sample user (Let’s say SCOTT), run :

set serveroutput off
select a.id,
       b.id
  from gark a,
       gark b
 where a.id=500
   and b.id=a.id;

select * from table(dbms_xplan.display_cursor);

The query plan should look like this :

Execution Plan
----------------------------------------------------------
Plan hash value: 2625395012
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    24 |    23   (5)|
|*  1 |  HASH JOIN         |      |     4 |    24 |    23   (5)|
|*  2 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
|*  3 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="A"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)

Step 3 : Register the plan in the SQL Plan Baselines

To perform this operation, you have to know the query SQL_ID in addition to the PLAN_HASH_VALUE that is display in the plan output :

accept plan_hash_value prompt "Enter value for plan_hash_value: "
2625395012

select distinct sql_id, plan_hash_value
    from v$sql_plan
    where plan_hash_value=&plan_hash_value;

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
4pznd20f4x8tf      2625395012

The query below display the registered SQL Plan baselines. It should be empty unless we’ve set the optimizer_capture_sql_plan_baselines parameter to true :

col SQL_HANDLE format a24
col PLAN_NAME format a29
col SCHEMA format a8

select SQL_HANDLE,
       PLAN_NAME,
       PARSING_SCHEMA_NAME SCHEMA,
       ENABLED,
       ACCEPTED,
       SQL_TEXT
  from dba_sql_plan_baselines;

no row selected.

You can now register the current query plan with the script below :

accept sql_id prompt "Enter value for sql_id: "
4pznd20f4x8tf

declare
  gg binary_integer;
begin
  gg:=dbms_spm.load_plans_from_cursor_cache(
                 sql_id=>'&sql_id');
end;
/

PL/SQL procedure successfully completed.

select SQL_HANDLE,
       PLAN_NAME,
       PARSING_SCHEMA_NAME SCHEMA,
       ENABLED,
       ACCEPTED,
       SQL_TEXT
  from dba_sql_plan_baselines;
SQL_HANDLE               PLAN_NAME                     SCHEM ENA ACC
------------------------ ----------------------------- ----- --- ---
SQL_TEXT
--------------------------------------------------------------------
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b SCOTT YES YES
select a.id,
  b.id
from gark a,
  gark b
where a.id=500
and b.id=a.id

Step 4 : the registered SQL Plan Baseline prevents an other plan to be used

To check the behavior of of the SQL Plan Management feature, we’ll perform what follow :

• Create an index the query could benefit from
• Check the content of the SQL Plan Baselines after the index creation
• Flush the Shared Pool and check the plan will be parsed
• Run the query and check its plan

1- Create an index

create index gark_idx
       on gark(id);

Index created.

2- Look at the SQL Plan baselines with the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE table function ; The registered plan does not use the index :

accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa

select * from table(
   dbms_xplan.display_sql_plan_baseline(
          sql_handle=>'&sql_handle'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL handle: SYS_SQL_9ff3489361e958aa
SQL text: select a.id, b.id from gark a, gark b where a.id=500
          and b.id=a.id
----------------------------------------------------------------
----------------------------------------------------------------
Plan name: SYS_SQL_PLAN_61e958aa45bb399b
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
----------------------------------------------------------------
Plan hash value: 2625395012
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    24 |    23   (5)|
|*  1 |  HASH JOIN         |      |     4 |    24 |    23   (5)|
|*  2 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
|*  3 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ID"="A"."ID")
   2 - filter("A"."ID"=500)
   3 - filter("B"."ID"=500)
ERROR: User has no SELECT privileges on objects of the SQL
       plan baseline

3- Flush the shared Pool :

alter system flush shared_pool;

System altered.

accept sql_id prompt "Enter value for sql_id: "
4pznd20f4x8tf

select sql_id,
       plan_hash_value
  from v$sql
 where sql_id='&sql_id';

no rows selected

4- Run the query and figured out that the query plan does not change besides the new index and the fact it has been re-parsed :

set serveroutput off

select a.id,
       b.id
  from gark a,
       gark b
 where a.id=500
   and b.id=a.id;

select * from table(dbms_xplan.display_cursor);

Execution Plan
----------------------------------------------------------
Plan hash value: 2625395012
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    24 |    23   (5)|
|*  1 |  HASH JOIN         |      |     4 |    24 |    23   (5)|
|*  2 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
|*  3 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ID"="A"."ID")
   2 - filter("A"."ID"=500)
   3 - filter("B"."ID"=500)

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

If you display the SQL plan baselines, you’ll see a new plan for the query has been registered which attributes are ACCEPTED=’NO’ and ORIGIN=’AUTO-CAPTURE’. This plan is not used by the query :

select SQL_HANDLE,
       PLAN_NAME,
       PARSING_SCHEMA_NAME SCHEMA,
       ENABLED,
       ACCEPTED,
       SQL_TEXT
  from dba_sql_plan_baselines;
SQL_HANDLE               PLAN_NAME                     SCHEM ENA ACC
------------------------ ----------------------------- ----- --- ---
SQL_TEXT
--------------------------------------------------------------------
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b SCOTT YES YES
select a.id,
  b.id
from gark a,
  gark b
where a.id=500
and b.id=a.id
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa SCOTT YES NO
select a.id,
  b.id
from gark a,
  gark b
where a.id=500
and b.id=a.id

accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa

select * from table(
   dbms_xplan.display_sql_plan_baseline(
          sql_handle=>'&sql_handle'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL handle: SYS_SQL_9ff3489361e958aa
SQL text: select a.id, b.id from gark a, gark b where a.id=500
          and b.id=a.id
----------------------------------------------------------------
----------------------------------------------------------------
Plan name: SYS_SQL_PLAN_61e958aa45bb399b
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
----------------------------------------------------------------
Plan hash value: 2625395012
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    24 |    23   (5)|
|*  1 |  HASH JOIN         |      |     4 |    24 |    23   (5)|
|*  2 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
|*  3 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ID"="A"."ID")
   2 - filter("A"."ID"=500)
   3 - filter("B"."ID"=500)

----------------------------------------------------------------
Plan name: SYS_SQL_PLAN_61e958aafcd784aa
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
----------------------------------------------------------------
Plan hash value: 886284999
-------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |     6 |   3 (34)|
|   1 |  MERGE JOIN CARTESIAN|          |     1 |     6 |   3 (34)|
|*  2 |   INDEX RANGE SCAN   | GARK_IDX |     1 |     3 |   1  (0)|
|   3 |   BUFFER SORT        |          |     1 |     3 |   2 (50)|
|*  4 |    INDEX RANGE SCAN  | GARK_IDX |     1 |     3 |   1  (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ID"=500)
   4 - access("B"."ID"=500)

Step 5 : Evolve the plan
The evolve_sql_plan_baseline enable to accept the new SQL plan :

accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa

var v_out clob
exec :v_out:=dbms_spm.evolve_sql_plan_baseline(-
   sql_handle=>'&sql_handle')
print v_out

select SQL_HANDLE,
       PLAN_NAME,
       ENABLED,
       ACCEPTED
from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                     ENA ACC
------------------------ ----------------------------- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b YES YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa YES YES

Once accepted, if you execute the query, the new plan (with the index) will be used :

select a.id,
       b.id
from gark a,
     gark b
where a.id=500
and b.id=a.id;

select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------
SQL_ID  4pznd20f4x8tf, child number 0
-------------------------------------
select a.id, b.id from gark a, gark b where a.id=500  and
b.id=a.id
Plan hash value: 886284999
-------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |     6 |   3 (34)|
|   1 |  MERGE JOIN CARTESIAN|          |     1 |     6 |   3 (34)|
|*  2 |   INDEX RANGE SCAN   | GARK_IDX |     1 |     3 |   1  (0)|
|   3 |   BUFFER SORT        |          |     1 |     3 |   2 (50)|
|*  4 |    INDEX RANGE SCAN  | GARK_IDX |     1 |     3 |   1  (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ID"=500)
   4 - access("B"."ID"=500)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_61e958aafcd784aa used for this
  statement

Step 6 : One Step Back

What if I just accept a new plan and it is indeed not efficient ? You can of cause drop the plan from the SQL Plan baselines as described in Step 7 or you can use the FIXED attribute which set the plans that have to be used. Using this attribute enables you to easily go back and forward between a set of plans :

accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aa45bb399b

declare
  v_out binary_integer;
begin
   v_out := dbms_spm.ALTER_SQL_PLAN_BASELINE(
           sql_handle =>'&sql_handle',
           plan_name=>'&plan_name',
           attribute_name=>'fixed',
           attribute_value=>'YES');
end;
/

select SQL_HANDLE,
       PLAN_NAME,
       FIXED,
       ACCEPTED
from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                     FIX ACC
------------------------ ----------------------------- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b YES YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa NO  YES

select a.id,
       b.id
  from gark a,
       gark b
 where a.id=500
   and b.id=a.id;

select * from table(dbms_xplan.display_cursor);

Execution Plan
----------------------------------------------------------
Plan hash value: 2625395012
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    24 |    23   (5)|
|*  1 |  HASH JOIN         |      |     4 |    24 |    23   (5)|
|*  2 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
|*  3 |   TABLE ACCESS FULL| GARK |     2 |     6 |    11   (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ID"="A"."ID")
   2 - filter("A"."ID"=500)
   3 - filter("B"."ID"=500)

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

To re set the fixed attribute run what’s below :

accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aa45bb399b

declare
  v_out binary_integer;
begin
   v_out := dbms_spm.ALTER_SQL_PLAN_BASELINE(
           sql_handle =>'&sql_handle',
           plan_name=>'&plan_name',
           attribute_name=>'fixed',
           attribute_value=>'NO');
end;
/

select SQL_HANDLE,
       PLAN_NAME,
       FIXED,
       ACCEPTED
from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                     FIX ACC
------------------------ ----------------------------- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b NO  YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa NO  YES

Step 7. Drop the SQL Plan baselines

To drop the registered SQL Plan baselines, you can use the drop_sql_plan_baseline function from dbms_spm as below :

col schema format a5
col SQL_HANDLE format a24
select SQL_HANDLE,
  PLAN_NAME,
  PARSING_SCHEMA_NAME SCHEMA,
  ENABLED,
  ACCEPTED
from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                     SCHEM ENA ACC
------------------------ ----------------------------- ----- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b SCOTT YES YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa SCOTT YES YES

accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aa45bb399b

DECLARE
  gg binary_integer;
BEGIN
  gg:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(
          SQL_HANDLE => '&sql_handle',
          PLAN_NAME => '&plan_name');
END;
/

accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aafcd784aa

DECLARE
  gg binary_integer;
BEGIN
  gg:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(
          SQL_HANDLE => '&sql_handle',
          PLAN_NAME => '&plan_name');
END;
/

col schema format a5
col SQL_HANDLE format a24
select SQL_HANDLE,
  PLAN_NAME,
  PARSING_SCHEMA_NAME SCHEMA,
  ENABLED,
  ACCEPTED
from dba_sql_plan_baselines;

no rows selected

Step 8. Introducing Automatic SQL Plan Management

In order to use Automatic SQL Plan Management, you’ll have to licence Oracle Tuning Pack and set optimizer_capture_sql_plan_baselines to true. Once done :

• The instances capture the “repeatable” SQL and their plan during the “normal” period.
• The new automatic maintenance schedule runs the SQL Tuning Advisor and set the SQL plans. As with 10g the SQL Tuning Advisor does not only consider the statistics but goes much deeper into SQL and plan analysis to evolve the plan…
• While new plans can be consider, the SQL Tuning Advisor evolves plans during the Maintenance Window making plans probably more accurate and probably less candidate to sudden changes.

It looks a fantastic 11g feature, don’t you think ? The question now is what performance enhancement I should expect from it and a good way to evaluate it would be to run the command below on a production system… Who dares ?

alter system set optimizer_capture_sql_plan_baselines=true

Step 9 : Drop the sample schema

drop index gark_idx;
drop table gark;

Step 10 : To conclude

No doubt that in manual mode SQL Plan Management highly improves the way we manage plan stability. It enables to easily switch from one plan to the other, it can be exported/imported from one database to the other and as a good news **considering the best 11g new features seem to be part of options**, it’s part of the classic “EE”. Now I miss a real production use case of Automatic SQL Plan Management.