http://oradba4all.blogspot.com/2012/12/step-by-step-implementing-sql-baselines.html
As soon as we implement base lines those base lines most of the times enabled & accepted but not fixed. some times enabled but not accepted and fixed. In this situation we are going to set attribute Accepted=YES and Fixed=Yes According to situation .
Here is the scenario:-
Step 1)
Below SQL having mutliple plans and base lines only one plan ( SQL_PLAN_gkr45qw2g7s887b647207) has been enabled & accepted but not fixed .
SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE SQL_HANDLE='SYS_SQL_f95c85b704f3e108';
OUTPUT:-
-------
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8821e522a4 YES NO NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8825319cac YES NO NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s887b647207 YES YES NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8896528f2c YES NO NO
(or)
SQL>SELECT sql_handle,plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE LOWER(sql_text) LIKE '%X_Xx_XXX_tx_XXXX_iXx%';
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8821e522a4 YES NO NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8825319cac YES NO NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s887b647207 YES YES NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8896528f2c YES NO NO
Step 2) Its optional step if you know best plan or if you are sure above any of the best plan you can skip this step 2 & directly go to step 3.
----Note: SQL Handel I got it from Above query . You can query by using sql_handle or sql_text to find baseline enabled or not or fixed or not
---- We can use the EVOLVE_SQL_PLAN_BASELINE function to compare the performance between the two plans. We note that the second plan has not been automatically evolved or accepted because it does not pass the performance improvement criteria which has been laid down
SQL> SET LONG 10000
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_f95c85b704f3e108') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_F95C85B704F3E108')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_f95c85b704f3e108
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_F95C85B704F3E108')
--------------------------------------------------------------------------------
COMMIT = YES
Plan: SQL_PLAN_gkr45qw2g7s8821e522a4
------------------------------------
Plan was verified: Time used 114.56 seconds.
Plan passed performance criterion: 52662.78 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_F95C85B704F3E108')
--------------------------------------------------------------------------------
Rows Processed: 0 0
Elapsed Time(ms): 891.824 .041 21751.8
CPU Time(ms): 476.928 .111 4296.65
Buffer Gets: 211188 4 52797
Physical Read Requests: 132 0
Physical Write Requests: 0 0
Physical Read Bytes: 1081344 0
Physical Write Bytes: 0 0
Executions: 1 1
Plan: SQL_PLAN_gkr45qw2g7s8896528f2c
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_F95C85B704F3E108')
--------------------------------------------------------------------------------
------------------------------------
Plan was verified: Time used 1.86 seconds.
Plan failed performance criterion: .01 times worse than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): .041 .071 .58
CPU Time(ms): .111 .111 1
Buffer Gets: 4 4 1
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_F95C85B704F3E108')
--------------------------------------------------------------------------------
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
Plan: SQL_PLAN_gkr45qw2g7s8825319cac
------------------------------------
Plan was verified: Time used 1.6 seconds.
Plan failed performance criterion: performance equal to baseline plan.
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_F95C85B704F3E108')
--------------------------------------------------------------------------------
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): .041 .05 .82
CPU Time(ms): .111 0
Buffer Gets: 4 4 1
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_F95C85B704F3E108')
--------------------------------------------------------------------------------
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 3
Number of plans accepted: 1
SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE SQL_HANDLE='SYS_SQL_f95c85b704f3e108';
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8821e522a4 YES YES NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8825319cac YES NO NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s887b647207 YES YES NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8896528f2c YES NO NO
From above complete output SQL_PLAN_gkr45qw2g7s8821e522a4 plan was 52662.78 times better than baseline plan So its automatcially accepted baseline.
Step 3)
Now Drop the Rest of PLAN_NAMES other the accepted one SQL_PLAN_gkr45qw2g7s8821e522a4.
Conn / as sunil
We Know SQL_PLAN_gkr45qw2g7s8821e522a4 is best plan when compare to other plan so set attribute Fixed=Yes as below ( same this applicable if you want to set ACCEPTED=YES as well if you need to set it manually )
SQL> var spm number;
SQL> exec :spm := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SYS_SQL_f95c85b704f3e108',plan_name =>'SQL_PLAN_gkr45qw2g7s8821e522a4', attribute_name => 'FIXED',attribute_value => 'YES');
PL/SQL procedure successfully completed.
OUTPUT:-
-------
SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE SQL_HANDLE='SYS_SQL_f95c85b704f3e108';
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8821e522a4 YES YES YES
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8825319cac YES NO NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s887b647207 YES YES NO
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8896528f2c YES NO NO
Now Drop the Rest of the plans other than fixed:-
--------------------------------------------------
Conn sunil/XXXXXXXXXXX ( since all this baselines i have created under my schema so I have logged into my schema )
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_f95c85b704f3e108',plan_name=>'SQL_PLAN_gkr45qw2g7s8825319cac');
END;
/
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_f95c85b704f3e108',plan_name=>'SQL_PLAN_gkr45qw2g7s887b647207');
END;
/
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_f95c85b704f3e108',plan_name=>'SQL_PLAN_gkr45qw2g7s8896528f2c');
END;
/
SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE SQL_HANDLE='SYS_SQL_f95c85b704f3e108';
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_f95c85b704f3e108 SQL_PLAN_gkr45qw2g7s8821e522a4 YES YES YES
Reference: -
http://www.dba-oracle.com/t_dba_sql_plan_baselines.htm
Disclaimer: If you feel this content related to your content please feel to contact me to remove this information. I am sharing the knowledge with people whoever in the need which is learning daily basis.
No comments:
Post a Comment