dba_sql_plan_baselines – Make DBA Life Easy

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'));

How to Create SQL Plan Baseline from Cursor Cache

Here is an example of how to create SQL plan baseline on a 12.1 Oracle database.

1)Get all the details of the sql:

SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,PLAN_HASH_VALUE,
            EXECUTIONS,ELAPSED_TIME/EXECUTIONS/1000000 Sec_per_exec 
      from  v$sql 
      where sql_id='gjpdb42w841yt' 
      order by ELAPSED_TIME/EXECUTIONS/1000000;

SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS SEC_PER_EXEC
------------- ---------- ------------ --------------- ---------- ------------
gjpdb42w841yt 3095529433 0            3153495478      26         .25 
gjpdb42w841yt 3095529433 3            3153495478      44        2.46 
gjpdb42w841yt 3095529433 1            3153495478       1        7.07 
gjpdb42w841yt 3095529433 2            1409003088      58     1831.29

2) For 12.1, there are following options for 12.1 database. choose one suitable to your situation.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_text          IN  CLOB,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_handle        IN  VARCHAR2,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   attribute_name   IN VARCHAR2,
   attribute_value  IN VARCHAR2,
   fixed            IN VARCHAR2 := 'NO',
   enabled          IN VARCHAR2 := 'YES')
  RETURN PLS_INTEGER;

3) Create a sql baseline for sql_id=’gjpdb42w841yt’ and PLAN_HASH_VALUE=3153495478 .

SQL>var cnt number
SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
     sql_id => 'gjpdb42w841yt', -
     plan_hash_value =>'3153495478');

4) To let newly created sql baseline be used immediately, purge the sql cursor from shared pool as per How To Flush an Object Out The Library Cache by Using DBMS_SHARED_POOL Package.

5) Check the sql baseline :

SQL> select SQL_HANDLE,substr(SQL_TEXT,1,30),ENABLED,
            ACCEPTED,FIXED,REPRODUCED,AUTOPURGE 
      from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE            SUBSTR(SQL_TE ENA   ACC  FIX  REP AUT
--------------------- ------------  ---  ---- ---- ---- ---
SQL_29078efcd1d51d34  select * from YES   YES  NO   YES YES

6) Check the execution plan for this baseline.

SET LONG 10000
SET LINES 200
SET PAGES 300
SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=> 
'SQL_PLAN_2k1wfzm8xa79n2943fd10'));

SET LONG 10000 
SET LINES 200 
SET PAGES 300 
SELECT * 
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>
'SQL_29078efcd1d51d34'));

How to Migrate SQL Plan Baselines Between Oracle Databases

After database upgrading / Patching, or database is migrated onto new infrastructure, it is common to see database performance is degrading due to SOL execution plans changed.

What we can do is by creating SQL plan baselines from old database, then copied them on to new database.

1) Identify the SQL plan baseline(s) to be migrated from source database:

SQL> select sql_handle, plan_name, sql_text, enabled, accepted 
     from   dba_sql_plan_baselines 
     where enabled='YES' 
       and accepted='YES' 
       and PLAN_NAME='SQL_PLAN_7ns0utmam6vc64a63fd18';

SQL_HANDLE           PLAN_NAME                      SQL_TEXT ENA ACC
-------------------- ------------------------------ -------- --- ---
SQL_7a601accd5336d86 SQL_PLAN_7ns0utmam6vc64a63fd18 UPDATE . YES YES

2) Create a staging table on source database:

SQL> BEGIN
        DBMS_SPM.CREATE_STGTAB_BASELINE(
        table_name => 'spm_tab',
        table_owner => 'testuser',
        tablespace_name => 'USERS');
END; 

/

PL/SQL procedure successfully completed.


SQL> select table_name,owner from dba_tables where owner='TETSUSER';

TABLE_NAME   OWNER
------------ ---------
SPM_TAB      TETSUSER

3) Pack SQL plan baseline(s) into above staging table ( spm_tab):

var xx number;
begin
:xx := DBMS_SPM.PACK_STGTAB_BASELINE('spm_tab', 'testuser', -
      sql_handle => 'SQL_7a601accd5336d86',  -
      plan_name =>  'SQL_PLAN_7ns0utmam6vc64a63fd18' ); 
end;

/

PL/SQL procedure successfully completed.

4) Export staging table with datapump:

$ expdp directory=exp_dir dumpfile=testuser_spm_tab.dmp 
  tables=testuser.spm_tab

5) Transfer the datapump file onto target database server.

6) Import staging table onto target database:

$ impdp directory=imp_dir dumpfile=testuser_spm_tab.dmp 
tables=testuser.spm_tab remap_schema=... remap_tablespace=...

7) Unpack the SQL plan baselines in staging table into target database:

SQL> var xx number;
     begin
      :xx := DBMS_SPM.UNPACK_STGTAB_BASELINE('SPM_TAB', 'TESTUSER');
     end;

     /

PL/SQL procedure successfully completed.

8) Verify the SQL plan baselines have been migrated onto target database:

SQL>select sql_handle, plan_name, sql_text, enabled, accepted, fixed 
      from dba_sql_plan_baselines
      where SQL_HANDLE='SQL_7a601accd5336d86';

SQL_HANDLE           PLAN_NAME                    SQL_TEXT  ENA ACC FIX
-------------------- ------------------------------ ------- --- --- ---
SQL_7a601accd5336d86 SQL_PLAN_7ns0utmam6vc64a63fd18 UPDATE  YES YES NO

9) Display execution plans for this SQL handle of a SQL plan baseline:

SQL> set linesize 120
SQL> set pagesize 2000
SQL> select * from TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( -
     'SQL_7a601accd5336d86','SQL_PLAN_7ns0utmam6vc64a63fd18'));

Syntax:

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
   sql_handle      IN VARCHAR2 := NULL,
   plan_name       IN VARCHAR2 := NULL,
   format          IN VARCHAR2 := 'TYPICAL')
 RETURN dbms_xplan_type_table;

Parameter	Description
sql_handle	SQL statement handle. It identifies a SQL statement whose plan(s) are to be displayed.
plan_name	Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.
format	Format string determines what information stored in the plan displayed. One of three format values (‘BASIC’, ‘TYPICAL’, ‘ALL’) can be used, each representing a common use case.