http://daliborm.blogspot.co.uk/2011/04/sql-plan-management.html?_sm_au_=ikVRsWF6StTJv16P
Overview
Goal of this page is to help teams stabilize their queries execution plan. It contains scripts queries and documents which should help DBAs and SDEs to build more stable service.
Automatic Plan Capture
alter system set optimizer_capture_sql_plan_baselines = true scope = both; alter system set optimizer_use_sql_plan_baselines = true scope = both;
Queries with plan recommendations
select sql_handle, plan_name, fixed from dba_sql_plan_baselines where accepted = 'NO' and enabled = 'YES' and creator = 'OLTP_USER' and module = 'ServiceName';
More details about plan recommendation
select sql_text, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines where sql_handle = 'SYS_SQL_008fa0f9324d1f14';
Compare execution plans
select * from table( dbms_xplan.display_sql_plan_baseline( sql_handle=>'SYS_SQL_008fa0f9324d1f14', format=>'basic'));
Compare all execution plans
select t.plan_table_output from ( select distinct sql_handle from dba_sql_plan_baselines where accepted = 'NO' and creator = 'OLTP_USER') pb, table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle, NULL, 'basic')) t;
Identify sql_handle for given query in plan baselines
select sql_text from v$sql where sql_id = 'anuq0jdxngy4k'; select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text = ' ';
Evolve execution plans
set serveroutput on set long 10000 declare report clob; begin report := dbms_spm.evolve_sql_plan_baseline( sql_handle => 'SYS_SQL_006093f2f146bd13'); dbms_output.put_line(report); end; /
Evolve all execution plans
var report clob; exec :report := dbms_spm.evolve_sql_plan_baseline();
Drop plan baseline
set serveroutput on set long 10000 declare report natural; begin report := dbms_spm.drop_sql_plan_baseline( 'SYS_SQL_006093f2f146bd13', 'SYS_SQL_PLAN_0b22ad06749f2b9c'); dbms_output.put_line(report); end; /
Drop all baselines
set serveroutput on set long 10000 DECLARE CURSOR delete_cursor IS select distinct sql_handle, plan_name from dba_sql_plan_baselines; v_counter NUMBER(38) DEFAULT 0; report natural; BEGIN FOR c_pdr in delete_cursor LOOP report := dbms_spm.drop_sql_plan_baseline(c_pdr.sql_handle, c_pdr.plan_name); dbms_output.put_line('Query: ' || c_pdr.sql_handle || ' plan: '||c_pdr.plan_name); END LOOP; END; /
Alter plan baseline
set serveroutput on declare l_plans_altered pls_integer; begin l_plans_altered := dbms_spm.alter_sql_plan_baseline( sql_handle => 'SYS_SQL_006093f2f146bd13', plan_name => 'SYS_SQL_PLAN_d90440b9ed3324c0', attribute_name => 'ENABLED', attribute_value => 'NO'); dbms_output.put_line('Plans Altered: ' || l_plans_altered); end; /
set serveroutput on declare l_plans_altered pls_integer; begin l_plans_altered := dbms_spm.alter_sql_plan_baseline( sql_handle => 'SYS_SQL_006093f2f146bd13', plan_name => 'SYS_SQL_PLAN_d90440b9ed3324c0', attribute_name => 'ACCEPTED', attribute_value => 'NO'); dbms_output.put_line('Plans Altered: ' || l_plans_altered); end; /
Query used in package
select t.plan_table_output from ( select distinct sql_handle from dba_sql_plan_baselines where accepted = 'NO' and creator = 'OLTP_USER' and last_verified is null) pb, table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle, NULL, 'basic')) t;
Remove all
alter system set optimizer_capture_sql_plan_baselines = false scope = both; alter system set optimizer_use_sql_plan_baselines = false scope = both;
ORA-38141
When you see this error:
ORA-38141: SQL plan baseline SQL_PLAN_gzs1skpuycuhdfe752e07 does not exist
Cause for the error:
- fail over to standby without parameters set properly.
- renaming baseline plan name.
Background:
SQL Plan Baseline is using outline tables. There is no 1 to 1 connection between sql_id and sql_handle. When Oracle evolves plan it gets signature. Then it uses signature to build outline data:
SELECT (xmltype(od.comp_data).extract('/outline_data')).getClobVal() FROM sys.sqlobj$data od WHERE od.signature = 12104247321009801218;
And then it prepares record.
SELECT PLAN_TABLE_OUTPUT FROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 ) AS SYS.DBMS_XPLAN_TYPE_TABLE))
So if you get in this trouble you will have to drop incorrect plan names and recreate them. I couldn't find better solution.
alter system set optimizer_capture_sql_plan_baselines = false; alter system set optimizer_use_sql_plan_baselines = false; DECLARE CURSOR delete_cursor IS select distinct sql_handle, plan_name from dba_sql_plan_baselines where rownum < 10000; v_counter NUMBER(38) DEFAULT 0; report natural; BEGIN FOR c_pdr in delete_cursor LOOP report := dbms_spm.drop_sql_plan_baseline(c_pdr.sql_handle, c_pdr.plan_name); -- dbms_output.put_line('Query: ' || c_pdr.sql_handle || ' plan: '||c_pdr.plan_name); END LOOP; END; /
alter system set optimizer_capture_sql_plan_baselines = true scope = both; alter system set optimizer_use_sql_plan_baselines = true scope = both;
ORA-06502
SYS_SQL_a76d706cc288f226 An uncaught error happened in display_sql_plan_baseline : ORA-06502: PL/SQL: numeric or value error
It is bug: Bug 8366552, Base Bug 7459168
Drop all baseline plans for that handle.