SQL Plan Management

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.