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

or

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