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 Performance Guide - 15.Using SQL Plan Management
- Oracle 11g PL/SQL Packages and Types Reference : DBMS_SPM
- Oracle 11g Reference :
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.
