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');
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
No comments:
Post a Comment