How to Create SQL Plan Baseline from Cursor Cache

Here is an example of how to create SQL plan baseline

SQL_ID : br243pw211cyp :- This is SQL ID where we are going to create baseline

1. Load all available plans in cache to SPM :

******************************************************

variable cnt number;
execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'br243pw211cyp');

2. Check details of SQL_Handle and PLAN_NAME created corrosponding to above step :

************************************************************************************************************

col SQL_TEXT for a30 ;
set lines 300 pages 300 ;
set long 10 ;
SELECT sql_handle, sql_text, plan_name, enabled , created FROM dba_sql_plan_baselines where created > sysdate-1/240;
SQL_HANDLE              SQL_TEXT    PLAN_NAME                      ENA CREATED
----------------------- ----------- ------------------------------ --- ---------------------------------
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x31b95bb2 YES 30-JUN-15 09.34.03.000000 AM
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x469ee624 YES 30-JUN-15 09.34.03.000000 AM
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x8338d7df YES 30-JUN-15 09.34.03.000000 AM
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x85955c39 YES 30-JUN-15 09.34.03.000000 AM

3. Disable all plans loaded from cache listed in above step :(take value of SQL_HANDLE & PLAN_NAME)

*************************************************************************************************************************

variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x31b95bb2',ATTRIBUTE_NAME =>'enabled',ATTRIBUTE_VALUE =>'NO');
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x469ee624',ATTRIBUTE_NAME =>'enabled',ATTRIBUTE_VALUE =>'NO');
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x8338d7df',ATTRIBUTE_NAME =>'enabled',ATTRIBUTE_VALUE =>'NO');
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x85955c39',ATTRIBUTE_NAME =>'enabled',ATTRIBUTE_VALUE =>'NO');


col SQL_TEXT for a30 ;
set lines 300 pages 300 ;
set long 10 ;
SELECT sql_handle, sql_text, plan_name, enabled , created FROM dba_sql_plan_baselines where created > sysdate-1/240;
SQL_HANDLE              SQL_TEXT    PLAN_NAME                      ENA CREATED
----------------------- ----------- ------------------------------ --- ---------------------------------
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x31b95bb2 YES 30-JUN-15 09.34.03.000000 AM
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x469ee624 YES 30-JUN-15 09.34.03.000000 AM
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x8338d7df YES 30-JUN-15 09.34.03.000000 AM
SQL_3195b658d293b41d    SELECT      SQL_PLAN_335dqb3997d0x85955c39 YES 30-JUN-15 09.34.03.000000 AM

4 . Force good plan on SQL_HANDLE created for SQL_id : br243pw211cyp

********************************************************************************************

Create a sql baseline for sql_id=’gjpdb42w841yt’ and PLAN_HASH_VALUE=2344645063

exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'br243pw211cyp',plan_hash_value =>2344645063 ,sql_handle =>'SQL_3195b658d293b41d');

5. Enable one of SQL_Handle created in step 1 

*******************************************************

variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x31b95bb2',ATTRIBUTE_NAME =>'enabled',ATTRIBUTE_VALUE =>'YES');

6. DROP unwanted PLAN_NAME from SPM 

****************************************************

variable cnt number;
exec :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x469ee624');
variable cnt number;
exec :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x8338d7df');
variable cnt number;
exec :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE =>'SQL_3195b658d293b41d',PLAN_NAME=>'SQL_PLAN_335dqb3997d0x85955c39');

Check you Explain Plan