How to Create SQL Plan Baseline from AWR

In another post How to Create SQL Plan Baseline from Cursor Cache. Here we will demonstrate how to create SQL plan baseline from AWR.

By using the SQL in Find Oracle SQL Execution History Details, we get the best execution plan for sql_id=’6fqb4wf9sgfyv’ and plan_hash_value= 815656277.

Create STS (  SQL Tuning Set )

 BEGIN  
  DBMS_SQLTUNE.CREATE_SQLSET (  
     sqlset_name  => 'STS_6fqb4wf9sgfyv',  
     description  => 'SQL Tuning Set for 6fqb4wf9sgfyv');  
 END;  

Load the Good Plan into STS

DECLARE  
  cur sys_refcursor;  
 BEGIN  
  OPEN cur FOR  
   SELECT VALUE(P)  
   FROM TABLE(   dbms_sqltune.select_workload_repository(begin_snap=>101300,end_snap=>101304,basic_filter=>'sql_id = ''6fqb4wf9sgfyv''',attribute_list=>'ALL')  
        ) p;  
    DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_6fqb4wf9sgfyv', populate_cursor=>cur);  
  CLOSE cur;  
 END;  

  /
PL/SQL procedure successfully completed.

Check the Good SQL Loaded into STS

SQL>  select OWNER,NAME,DESCRIPTION,STATEMENT_COUNT from DBA_SQLSET where OWNER='SYS' and NAME='STS_6fqb4wf9sgfyv';

OWNER                          NAME
------------------------------ ------------------------------
DESCRIPTION                                                                                                                                                                                                                                  STATEMENT_COUNT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
SYS                            STS_6fqb4wf9sgfyv
SQL Tuning Set for 6fqb4wf9sgfyv                                                                                                                                                                                                                   1
SQL> select * from table(dbms_xplan.display_sqlset('STS_6fqb4wf9sgfyv','6fqb4wf9sgfyv'));
...
..
.

Load the Plan From STS

SQL> DECLARE
 good_plans pls_integer;
 BEGIN
  good_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => 'STS_6fqb4wf9sgfyv',
   basic_filter=>'plan_hash_value = ''815656277'''
   );
 END;   

 /

PL/SQL procedure successfully completed.

Check the Plan Loaded Successfully

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,REPRODUCED,AUTOPURGE,OPTIMIZER_COST , EXECUTIONS,ELAPSED_TIME,CPU_TIME from DBA_SQL_PLAN_BASELINES where SQL_TEXT like 'SELECT O.ORDER_ID, O.ORDER_RE%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX REP AUT OPTIMIZER_COST EXECUTIONS ELAPSED_TIME   CPU_TIME
------------------------------ ------------------------------ --- --- --- --- --- -------------- ---------- ------------ ----------
SQL_4e7889c88773e98f           SQL_PLAN_4wy49t23r7ucgbe7808e2 YES YES NO  YES YES         229777          1     61311779   53542413
SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_4wy49t23r7ucgbe7808e2'));
SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SQL_4e7889c88773e98f'));

dbms_spm.drop_sql_plan_baseline Fails With ‘ORA-14552: Cannot Perform A DDL, Commit Or Rollback Inside A Query Or DML

SYMPTOM

Drop a sql execution plan baseline with following errors:

SQL> select dbms_spm.drop_sql_plan_baseline(sql_handle=>    -
                       'SQL_89d3699974845bdb') from dual;
select dbms_spm.drop_sql_plan_baseline(sql_handle=>        -
                       'SQL_89d3699974845bdb') from dual
*
ERROR at line 1:
ORA-14552: cannot perform a DDL,commit or rollback inside 
           a query or DML
ORA-06512: at "SYS.DBMS_SPM", line 1210
ORA-06512: at "SYS.DBMS_SPM", line 1200
ORA-06512: at line 1

dbms_spm.drop_sql_plan_baseline updates dictionary tables. So you should not use it in a query.

SOLUTION

SQL> exec :xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>   -
                                   'SQL_89d3699974845bdb');

PL/SQL procedure successfully completed.

SQL> select :xx from dual;

:XX
----------
1

SQL> print :xx

XX
----------
1
Drop all sql execution plan baselines.
SQL> set serveroutput on
SQL> declare
     v_count pls_integer;
     v_sql_handle varchar2(50);
     cursor c1 is
     select sql_handle from dba_sql_plan_baselines
         where sql_handle like 'SQL_%';
     begin
        open c1 ;
        loop
            fetch c1 into v_sql_handle;
            exit when c1%notfound;
            v_count := dbms_spm.drop_sql_plan_baseline( 
                             sql_handle=>v_sql_handle );
           dbms_output.put_line(v_count);
      end loop;
  end;

 /
1
1
..
.
PL/SQL procedure successfully completed.

SQL> select count(*) from dba_sql_plan_baselines;

    COUNT(*)
 ----------
         0