To download the pdf of this article click http://goo.gl/HyMdaS
September 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
12C SQL PLAN MANAGEMENT (SPM)
SQL Plan baselines were introduced in 11G, its a remedy to stop the database to perform badly in case the optimizer fails to select a cost effective plan in case there are changes made in the database by any means, be it major changes or minor changes. So SQL Plan Baselines have that proactive approach to stop the bad performance in future by reproducing alternate plans and storing them in a repository, later verifying their performance, and just in case they dont behave well and they dont give guarantee of good performance oracle simply ignores them and puts them in history but at the initial stage if they do behave very well oracle accepts them inside the SQL Plan Baseline and keep them marked as accepted, subsequent execution plans are kept in history till they are verified and confirm they dont degrade performance. In other words the mission of SQL plan baselines is to preserve the performance of oracle database when repeating SQL statements tend to execute and affect the performance of the database regardless of changes in the database. So it is a conservative and a proactive approach.
So in simple words SPM does the following :-
1) It captures the plans at the initial stage and moves them into the baseline where plans are marked as accepted, (DBA can also manually load the plans in baseline)
2) New and alternate plans are moved to history (non accepted), only the plans in baseline are used which are enabled and accepted
3) In evolution phase plans in history are compared to plans in baseline, if the plans are proven to perform well they are pushed to baseline if they give guarantee they will not degrade performance.
## New in 12C Evolution is automatically done as an Auto Task SYS_AUTO_SPM_EVOLVE_TASK.
So lets get into SQL PLAN MANAGEMENT (SPM) more deep now, Oracle captures the execution plans of the statements if the DBA enables the behaviour with optimizer_capture_sql_plan_baselines parameter which by default is FALSE. And once oracle captures the initial plan it stores them in the baseline so that in future if any changes do happen, the same accepted plan will be used to avoid performance degradation which is mainly due to Expensive/Alternate/New plans. Because the truth is that some plan changes and they cause performance degradation. A very important thing to know is that SQL plan baselines reproduces an execution plan whereas SQL Profiles will only correct optimizer cost estimates and that is one big reason why SQL Plan Baslines were introduced in 11G and as we know SQL Profiles were introduced in 10G. Remember that along with statement logs of the SQL statements, SQL Profiles are also stored in the SQL MANAGEMENT BASE which is a repository of SQL Plan Baselines (SPM). Because some statements are tuned with SQL Tuning Advisor and once the recommendation is implementation oracle can put the plan used with sql profiles in the sql plan baselines and this interaction requires tuning pack to be available and enabled because SPM itself does not require tuning pack, it is free remember, the interaction of sql tuning advisor at nightly window task (Auto Task of SQL TUNING ADVISOR) with SPM requires the tuning pack. Not clear about the interaction part ? well let me explain this, when sql tuning advisor is run and it finds a good tuned plan and when we implement the recommendation lets say of accepting an sql profile of it, then oracle can easily put a brand new plan into the baseline directly and moreover it can even move the plan from plan history (non accepted plan) into the plan baseline where the plan is marked as accepted. So can we say that sql tuning advisor does evolve as well ? well yes it does.
Before SPM we had SQL Profiles. Before Profiles we had Stored Outlines (SO). And before SO we had CBO Hints. With SPM we can have stable plans and we can have more than one per SQL. In oracle database 12C, new enhancement is that when a new plan is put into the SQL management Base (SMB) which is the repository of SQL PLAN BASLINES, plan rows are persisted in SMB which did not use to happen in 11G. In 11g oracle used to compile the statement everytime when we used to display plans by using display_sql_plan_baseline function of DBMS_SPM package to show the execution plan. Moreover automatic evolving of SQL plan baseline is also available in Oracle 12C which is done with help of SPM evolve advisor and internally the task name is SYS_AUTO_SPM_EVOLVE_TASK. SPM evolve advisor basically evolves plans that are in history and that may or may not be accepted. So offcourse a non accepted plans are compared to plans in the baselines, if they prove to perform well and they give guarantee that they will not cause performance regession when changes occur they are marked as accepted. In Oracle 12C new functions are available which are create_evolve_Task and execute_evolve_task to allow DBA's to fetch a report and implement the results anytime anywhere which means accept non accepted plans which are better after evolution phase and if they give guarantee not to regress the performance of database in case of changes of any kind happen in the database. We will see later in this white paper with some examples of what changes are introduced in 12C. But now let us continue with essentials of SQL PLAN MANAGEMENT (SPM).
Note :- As we know SMB the repository of SQL PLAN MANAGEMENT is in sysaux tablespace, We need to make sure that sysaux tablespace is always online in order for us to use sql plan management objects otherwise performance can be compromised and can degrade the performance of oracle database to a considerable amount.
_______________________
In order to use SQL Plan Management you will need the EXECUTE privilege on the DBMS_SPM package and to evolve a plan in an existing SQL plan baseline you will need the ADMINISTER SQL MANAGEMENT OBJECT privilege.
A very important point to note is that if any user is granted the ADMINISTER SQL MANAGEMENT OBJECT privilege he or she is able to execute any subprogram in the DBMS_SPM package.
______________________
SQL PLAN BASELINES are controlled by two intialization parameters which are :
SQL> SHOW PARAMETER SQL_PLAN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
_______________________
SO let us examine pratically how SQL PLAN BASELINES works in Oracle.
There is a select query below and DBA's wants to load it into the baseline as the plan is good.
SQL> select sql_id from v$sql where sql_text like 'select * from emp where empn%';
SQL_ID
-------------
2cv6qqj01b9wu
We can load plans into the baselines from shared sql area (cursor cache), SQL tuning set (STS), Staging table and Stored outline.
Let us now see how to load the plans from all the 4 methods.
Method 1 (Cursor Cache)
SQL> declare
a pls_integer;
begin
a:= dbms_spm.load_plans_from_cursor_cache(sql_id=>'2cv6qqj01b9wu');
end;
/
PL/SQL procedure successfully completed.
SQL> select signature,SQL_HANDLE,enabled,accepted,cpu_time from dba_sql_plan_baselines where sql_text like 'select * from emp where empno%';
SIGNATURE SQL_HANDLE ENA ACC CPU_TIME
---------- ------------------------------ --- --- ----------
8.1104E+18 SYS_SQL_708dd8dbda3c5c92 YES YES 7998
So it is confirmed that the plan is accepted and enabled to be used by sql plan baseline. Let us see a report output from SQL PLAN BASELINE with the help of DISPLAY_SQL_PLAN_BASELINE function of DBMS_SPM package.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
Method 2 SQL tuning set (STS)
Load plans from sql tuning set into the baseline.
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'STS_SALES1', -
basic_filter => 'sql_text like ''SELECT CUSTOMER_ID, ACCOUNT_NUMBER%''' );
Method 3 (Staging table)
Once stage table is created and packed and after it is transported and imported with data pump into target database, execute the following. In this example we are making this plan as fixed for some business reasons of a production database.
DECLARE
my_plans NUMBER;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'stage1'
, fixed => 'yes'
);
END;
/
Method 4 (Stored outline)
SQL> GRANT CREATE ANY OUTLINE TO SCOTT;
Grant succeeded.
SQL> select sql_id,hash_value, child_number from v$sql where sql_text like 'select ename from emp%';
SQL_ID HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
3frqmfujukdts 2745775928 0
[oracle@node4 ~]$ sqlplus scott
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 1 00:25:09 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Aug 31 2014 16:51:24 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> ALTER SESSION SET create_stored_outlines=TRUE;
Session altered.
SQL> BEGIN
DBMS_OUTLN.create_outline(
hash_value => 2745775928,
child_number => 0,
category => 'DEV');
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT name, category,owner FROM dba_outlines;
NAME CATEGORY OWNER
---------------------------------------- -------------------- --------------------
SYS_OUTLINE_14090100280772402 DEV SCOTT
SQL> variable v clob
SQL> execute :v := dbms_spm.migrate_stored_outline(attribute_name=>'outline_name',attribute_value=>'SYS_OUTLINE_14090100280772402', fixed=>'NO');
PL/SQL procedure successfully completed.
The column name origin will display STORED-OUTLINE if it has been migrated from stored outline, so following query is useful for that purpose. Let us see by running the command,
SQL> select sql_handle,sql_text,origin,plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT ORIGIN PLAN_NAME ENA ACC
-------------------- ------------------------------ -------------- -------------------------------------------------- --- ---
SQL_cf69d4c0f3821825 select ename from emp STORED-OUTLINE SYS_OUTLINE_14090100280772402 YES YES
___________________________________________
In the following example we will evolve the sql plan baseline, note here we are evolving plans with respect to a particular SQL Handle and all plans corresponding to a sql statement or a SQL Handle will be evolved.
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92');
dbms_output.put_line(c);
end;
/
PL/SQL procedure successfully completed.
__________________________________________________
In below example after checking the values we configure setting for SQL MANAGEMENT BASE (SMB). The quota available in SYSAUX tablespace and retention period in the sysaux tablespace. Be default SMB can take 10% of SYSUAX tablespace and it can be increased till 50% and retention period is 53 weeks by default, almost 1 year which can also be increased to 523 weeks (maximum) and 5 weeks minimum.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SQL> BEGIN
DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 30
PLAN_RETENTION_WEEKS 105
_____________________________________________________
The following example shows how to unaccept a plan in Oracle 11G. We will also see in this white paper later how syntax differs from 11G R1 to 11G R2.
SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'ENABLED',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
In below example we make a plan as fixed which means this plan will always be used and that is why it is called as FIXED. Remember that there can be 2 fixed plans also for a statement and in that case the best cheapest plan will be chosen by the optimizer. So we can mark more than 1 plan as fixed for a statement, lets see how we can change FIXED attribute for a plan.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
## Attribute of a plan can be enabled, fixed, autopurged,
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
The Oracle database according to plan retention settings purges plans that have not been used and offcourse which are longer than the plan retention period, and how does oracle determines about it ?? The answer is identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. So autopurge setting works this way, if you plan not to purge that plan, just configure autopurge to NO simply. let us see how we can do it.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'AUTOPURGE',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
SQL> select AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92' ;
AUT OPTIMIZER_COST
--- --------------
NO 1
The following example shows to set time_limit values, this means time limit set for every plan verification, Time limit is in number of minutes. This applies only if verify = 'YES'and the parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans, if the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance, When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',time_limit=>20);
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = 20
VERIFY = YES
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________________________________________________________
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',verify=>'NO');
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = NO
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________
SQL> select plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92';
PLAN_NAME ENA ACC FIX
------------------------------ --- --- ---
SQL_PLAN_713fsvgd3sr4k695cc014 NO YES NO
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
plan_name=>'SQL_PLAN_713fsvgd3sr4k695cc014',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
_______________________________________________
Let us take an example of some changes introduced in Oracle 12C when it comes to SQL PLAN MANAGEMENT (SPM).
Automatic Evolution Task := This task will run automatically and it will accept the plans automatically if they are proven to perform well in the evolution phase, so default behaviour is accepting if proven to perform well and we can enable or disable this setting by setting up the parameter for Auto Evolution Task as follows :=
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'ACCEPT_PLANS'
, value => 'true'
);
END;
/
Lets check and confirm :-
SQL> COL PARAMETER_NAME FORMAT a25
SQL> COL VALUE FORMAT a10
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
( (PARAMETER_NAME = 'ACCEPT_PLANS') OR (PARAMETER_NAME = 'TIME_LIMIT') ) );
PARAMETER_NAME VALUE
------------------------- ----------
TIME_LIMIT 3600
ACCEPT_PLANS TRUE
We can also set LOCAL_TIME_LIMIT parameter, For example lets say we want to limit the task time to 30 minutes. By default it is 1 hour.
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'LOCAL_TIME_LIMIT'
, value => 1800
);
END;
/
If you do not have the automatic evolve task enabled, you are most welcome to run it manually
Let us see how we do it manually
SQL> VARIABLE v1 NUMBER
SQL> VARIABLE task_name VARCHAR2(50)
SQL> VARIABLE exe_name VARCHAR2(50)
SQL> VARIABLE evol_out CLOB
SQL> EXECUTE :task_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_cf69d4c0f3821825',plan_name => 'SYS_OUTLINE_14090100280772402');
PL/SQL procedure successfully completed.
SQL> SELECT :task_name FROM DUAL;
:TASK_NAME
--------------------------------------------------------------------------------------------------------------------------------
TASK_44
## Execute the evolve task
SQL> EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:task_name);
PL/SQL procedure successfully completed.
SQL> SELECT :exe_name FROM DUAL;
:EXE_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXEC_27
SQL> EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## Following code shows how to report for evolve task
SQL> set long 10000
SQL> SELECT :evol_out FROM DUAL;
:EVOL_OUT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------------
-------------
Task Information:
---------------------------------------------
Task Name : TASK_44
Task Owner : SYS
Execution Name : EXEC_27
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
:EVOL_OUT
--------------------------------------------------------------------------------
Status : COMPLETED
Started : 09/01/2014 00:38:47
Finished : 09/01/2014 00:38:47
Last Updated : 09/01/2014 00:38:47
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
--------------------------------------------------------------------------------
.........................
.
.........................................
## Implement the evolve task
SQL> EXECUTE :v1 := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## To drop the baseline we execute the following code
EXEC :v1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_cf69d4c0f3821825');
_______________________________________________
In 11G R1 and R2 there is a slight difference in making changes to an accepted plan, In 11gR2, the sql plans are accepted and unaccepted using a different procedure which is used in 11gR1.
Also, once plans are accepted in 11gR2, they do not become UNACCEPTED. To avoid using that plan, the attribute ENABLED must be set to 'NO'.
In 11gR1
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ACCEPTED',
ATTRIBUTE_VALUE => 'YES');
END;
/
You will get below error if you use this procedurein 11G R2.
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
_______________________
In Oracle 11gR2
To ACCEPT the baseline use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE as follows:
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.evolve_sql_plan_baseline
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
VERIFY=>'NO' ,
COMMIT=>'YES');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
The parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans
If the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
The parameter COMMIT specifies whether to update the ACCEPTED status of non-accepted plans from 'NO' to 'YES'
When set to 'YES', baseline will be updated to ACCEPTED status and a report will be generated
When set to 'NO', a report will be generated without actually changing the status of the baseline(s) to ACCEPTED.
_______________________
To UNACCEPT the baseline set ENABLED attribute to NO using
DBMS_SPM.ALTER_SQL_PLAN_BASELINE:
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ENABLED',
ATTRIBUTE_VALUE => 'NO');
END;
/
Prior to 11gR2, the plan could be "UNACCEPTED" by executing ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ACCEPTED' and ATTRIBUTE_VALUE=> 'NO'.
The status of ACCEPTED in DBA_SQL_PLAN_BASELINES would be updated to 'NO'.
FROM 11gR2, to change a plan to be "unaccepted", you need to execute ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ENABLED' and ATTRIBUTE_VALUE=> 'NO'.
________________________
Sometimes because of some problems faced we need to enable tracing and see what's going in the background to see what went wrong, lets see how we can enable tracing for SPM
Normally to trace all sessions of SPM we can do the following
SQL>exec dbms_spm.configure('spm_tracing',1);
To disable we can do the following
SQL> exec dbms_spm.configure('spm_tracing',0);
To check and verify we can do the following
SQL> SELECT parameter_name, parameter_value FROM sys.smb$config WHERE parameter_name='SPM_TRACING';
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPM_TRACING 0
Lets take an example :=
SQL> exec dbms_spm.configure('spm_tracing',1);
PL/SQL procedure successfully completed.
SQL> DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'try',
sqlset_owner => 'TEST',
commit_rows => 5);
END;
/
PL/SQL procedure successfully completed.
This gives trace similar to:
.../diag/rdbms/demo/demo/trace/demo_ora_15811.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /.../orcl/oracle
System name: Linux
Node name: tlax66
Release: 2.6.9-78.0.0.0.1.ELsmp
Version: #1 SMP Thu Aug 29 18:15:25 EDT 2014
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 10311, image: oracle@orcl (TNS V1-V3)
_______________________
There have been some bugs and knows issues when using SPM in different versions, Some have been solved with some workarounds and some by uprading or patching. Please refer to below notes
Known Issues
Document 1187767.1 Baseline Not Used Under Plsql When OFE Is Changed
Document 1204524.1 Dbms_spm.Load_plans_from_sqlset Getting Xml Parsing Error
Document 12732879.8 Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
Document 11719151.8 Bug 11719151 - SQL Plan Management capture causes slowness
Document 9910484.8 Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
Document 14009271.8 Bug 14009271 - Cannot reproduce execution plan imported from SQL Tuning Sets / Outlines
_____________________________
References
http://docs.oracle.com/database/121/TGSQL/toc.htm
http://docs.oracle.com/database/121/ADMIN/toc.htm
September 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
12C SQL PLAN MANAGEMENT (SPM)
SQL Plan baselines were introduced in 11G, its a remedy to stop the database to perform badly in case the optimizer fails to select a cost effective plan in case there are changes made in the database by any means, be it major changes or minor changes. So SQL Plan Baselines have that proactive approach to stop the bad performance in future by reproducing alternate plans and storing them in a repository, later verifying their performance, and just in case they dont behave well and they dont give guarantee of good performance oracle simply ignores them and puts them in history but at the initial stage if they do behave very well oracle accepts them inside the SQL Plan Baseline and keep them marked as accepted, subsequent execution plans are kept in history till they are verified and confirm they dont degrade performance. In other words the mission of SQL plan baselines is to preserve the performance of oracle database when repeating SQL statements tend to execute and affect the performance of the database regardless of changes in the database. So it is a conservative and a proactive approach.
So in simple words SPM does the following :-
1) It captures the plans at the initial stage and moves them into the baseline where plans are marked as accepted, (DBA can also manually load the plans in baseline)
2) New and alternate plans are moved to history (non accepted), only the plans in baseline are used which are enabled and accepted
3) In evolution phase plans in history are compared to plans in baseline, if the plans are proven to perform well they are pushed to baseline if they give guarantee they will not degrade performance.
## New in 12C Evolution is automatically done as an Auto Task SYS_AUTO_SPM_EVOLVE_TASK.
So lets get into SQL PLAN MANAGEMENT (SPM) more deep now, Oracle captures the execution plans of the statements if the DBA enables the behaviour with optimizer_capture_sql_plan_baselines parameter which by default is FALSE. And once oracle captures the initial plan it stores them in the baseline so that in future if any changes do happen, the same accepted plan will be used to avoid performance degradation which is mainly due to Expensive/Alternate/New plans. Because the truth is that some plan changes and they cause performance degradation. A very important thing to know is that SQL plan baselines reproduces an execution plan whereas SQL Profiles will only correct optimizer cost estimates and that is one big reason why SQL Plan Baslines were introduced in 11G and as we know SQL Profiles were introduced in 10G. Remember that along with statement logs of the SQL statements, SQL Profiles are also stored in the SQL MANAGEMENT BASE which is a repository of SQL Plan Baselines (SPM). Because some statements are tuned with SQL Tuning Advisor and once the recommendation is implementation oracle can put the plan used with sql profiles in the sql plan baselines and this interaction requires tuning pack to be available and enabled because SPM itself does not require tuning pack, it is free remember, the interaction of sql tuning advisor at nightly window task (Auto Task of SQL TUNING ADVISOR) with SPM requires the tuning pack. Not clear about the interaction part ? well let me explain this, when sql tuning advisor is run and it finds a good tuned plan and when we implement the recommendation lets say of accepting an sql profile of it, then oracle can easily put a brand new plan into the baseline directly and moreover it can even move the plan from plan history (non accepted plan) into the plan baseline where the plan is marked as accepted. So can we say that sql tuning advisor does evolve as well ? well yes it does.
Before SPM we had SQL Profiles. Before Profiles we had Stored Outlines (SO). And before SO we had CBO Hints. With SPM we can have stable plans and we can have more than one per SQL. In oracle database 12C, new enhancement is that when a new plan is put into the SQL management Base (SMB) which is the repository of SQL PLAN BASLINES, plan rows are persisted in SMB which did not use to happen in 11G. In 11g oracle used to compile the statement everytime when we used to display plans by using display_sql_plan_baseline function of DBMS_SPM package to show the execution plan. Moreover automatic evolving of SQL plan baseline is also available in Oracle 12C which is done with help of SPM evolve advisor and internally the task name is SYS_AUTO_SPM_EVOLVE_TASK. SPM evolve advisor basically evolves plans that are in history and that may or may not be accepted. So offcourse a non accepted plans are compared to plans in the baselines, if they prove to perform well and they give guarantee that they will not cause performance regession when changes occur they are marked as accepted. In Oracle 12C new functions are available which are create_evolve_Task and execute_evolve_task to allow DBA's to fetch a report and implement the results anytime anywhere which means accept non accepted plans which are better after evolution phase and if they give guarantee not to regress the performance of database in case of changes of any kind happen in the database. We will see later in this white paper with some examples of what changes are introduced in 12C. But now let us continue with essentials of SQL PLAN MANAGEMENT (SPM).
Note :- As we know SMB the repository of SQL PLAN MANAGEMENT is in sysaux tablespace, We need to make sure that sysaux tablespace is always online in order for us to use sql plan management objects otherwise performance can be compromised and can degrade the performance of oracle database to a considerable amount.
_______________________
In order to use SQL Plan Management you will need the EXECUTE privilege on the DBMS_SPM package and to evolve a plan in an existing SQL plan baseline you will need the ADMINISTER SQL MANAGEMENT OBJECT privilege.
A very important point to note is that if any user is granted the ADMINISTER SQL MANAGEMENT OBJECT privilege he or she is able to execute any subprogram in the DBMS_SPM package.
______________________
SQL PLAN BASELINES are controlled by two intialization parameters which are :
SQL> SHOW PARAMETER SQL_PLAN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
_______________________
SO let us examine pratically how SQL PLAN BASELINES works in Oracle.
There is a select query below and DBA's wants to load it into the baseline as the plan is good.
SQL> select sql_id from v$sql where sql_text like 'select * from emp where empn%';
SQL_ID
-------------
2cv6qqj01b9wu
We can load plans into the baselines from shared sql area (cursor cache), SQL tuning set (STS), Staging table and Stored outline.
Let us now see how to load the plans from all the 4 methods.
Method 1 (Cursor Cache)
SQL> declare
a pls_integer;
begin
a:= dbms_spm.load_plans_from_cursor_cache(sql_id=>'2cv6qqj01b9wu');
end;
/
PL/SQL procedure successfully completed.
SQL> select signature,SQL_HANDLE,enabled,accepted,cpu_time from dba_sql_plan_baselines where sql_text like 'select * from emp where empno%';
SIGNATURE SQL_HANDLE ENA ACC CPU_TIME
---------- ------------------------------ --- --- ----------
8.1104E+18 SYS_SQL_708dd8dbda3c5c92 YES YES 7998
So it is confirmed that the plan is accepted and enabled to be used by sql plan baseline. Let us see a report output from SQL PLAN BASELINE with the help of DISPLAY_SQL_PLAN_BASELINE function of DBMS_SPM package.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
Method 2 SQL tuning set (STS)
Load plans from sql tuning set into the baseline.
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'STS_SALES1', -
basic_filter => 'sql_text like ''SELECT CUSTOMER_ID, ACCOUNT_NUMBER%''' );
Method 3 (Staging table)
Once stage table is created and packed and after it is transported and imported with data pump into target database, execute the following. In this example we are making this plan as fixed for some business reasons of a production database.
DECLARE
my_plans NUMBER;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'stage1'
, fixed => 'yes'
);
END;
/
Method 4 (Stored outline)
SQL> GRANT CREATE ANY OUTLINE TO SCOTT;
Grant succeeded.
SQL> select sql_id,hash_value, child_number from v$sql where sql_text like 'select ename from emp%';
SQL_ID HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
3frqmfujukdts 2745775928 0
[oracle@node4 ~]$ sqlplus scott
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 1 00:25:09 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Aug 31 2014 16:51:24 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> ALTER SESSION SET create_stored_outlines=TRUE;
Session altered.
SQL> BEGIN
DBMS_OUTLN.create_outline(
hash_value => 2745775928,
child_number => 0,
category => 'DEV');
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT name, category,owner FROM dba_outlines;
NAME CATEGORY OWNER
---------------------------------------- -------------------- --------------------
SYS_OUTLINE_14090100280772402 DEV SCOTT
SQL> variable v clob
SQL> execute :v := dbms_spm.migrate_stored_outline(attribute_name=>'outline_name',attribute_value=>'SYS_OUTLINE_14090100280772402', fixed=>'NO');
PL/SQL procedure successfully completed.
The column name origin will display STORED-OUTLINE if it has been migrated from stored outline, so following query is useful for that purpose. Let us see by running the command,
SQL> select sql_handle,sql_text,origin,plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT ORIGIN PLAN_NAME ENA ACC
-------------------- ------------------------------ -------------- -------------------------------------------------- --- ---
SQL_cf69d4c0f3821825 select ename from emp STORED-OUTLINE SYS_OUTLINE_14090100280772402 YES YES
___________________________________________
In the following example we will evolve the sql plan baseline, note here we are evolving plans with respect to a particular SQL Handle and all plans corresponding to a sql statement or a SQL Handle will be evolved.
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92');
dbms_output.put_line(c);
end;
/
PL/SQL procedure successfully completed.
__________________________________________________
In below example after checking the values we configure setting for SQL MANAGEMENT BASE (SMB). The quota available in SYSAUX tablespace and retention period in the sysaux tablespace. Be default SMB can take 10% of SYSUAX tablespace and it can be increased till 50% and retention period is 53 weeks by default, almost 1 year which can also be increased to 523 weeks (maximum) and 5 weeks minimum.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SQL> BEGIN
DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 30
PLAN_RETENTION_WEEKS 105
_____________________________________________________
The following example shows how to unaccept a plan in Oracle 11G. We will also see in this white paper later how syntax differs from 11G R1 to 11G R2.
SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'ENABLED',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
In below example we make a plan as fixed which means this plan will always be used and that is why it is called as FIXED. Remember that there can be 2 fixed plans also for a statement and in that case the best cheapest plan will be chosen by the optimizer. So we can mark more than 1 plan as fixed for a statement, lets see how we can change FIXED attribute for a plan.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
## Attribute of a plan can be enabled, fixed, autopurged,
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
The Oracle database according to plan retention settings purges plans that have not been used and offcourse which are longer than the plan retention period, and how does oracle determines about it ?? The answer is identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. So autopurge setting works this way, if you plan not to purge that plan, just configure autopurge to NO simply. let us see how we can do it.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'AUTOPURGE',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
SQL> select AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92' ;
AUT OPTIMIZER_COST
--- --------------
NO 1
The following example shows to set time_limit values, this means time limit set for every plan verification, Time limit is in number of minutes. This applies only if verify = 'YES'and the parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans, if the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance, When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',time_limit=>20);
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = 20
VERIFY = YES
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________________________________________________________
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',verify=>'NO');
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = NO
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________
SQL> select plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92';
PLAN_NAME ENA ACC FIX
------------------------------ --- --- ---
SQL_PLAN_713fsvgd3sr4k695cc014 NO YES NO
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
plan_name=>'SQL_PLAN_713fsvgd3sr4k695cc014',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
_______________________________________________
Let us take an example of some changes introduced in Oracle 12C when it comes to SQL PLAN MANAGEMENT (SPM).
Automatic Evolution Task := This task will run automatically and it will accept the plans automatically if they are proven to perform well in the evolution phase, so default behaviour is accepting if proven to perform well and we can enable or disable this setting by setting up the parameter for Auto Evolution Task as follows :=
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'ACCEPT_PLANS'
, value => 'true'
);
END;
/
Lets check and confirm :-
SQL> COL PARAMETER_NAME FORMAT a25
SQL> COL VALUE FORMAT a10
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
( (PARAMETER_NAME = 'ACCEPT_PLANS') OR (PARAMETER_NAME = 'TIME_LIMIT') ) );
PARAMETER_NAME VALUE
------------------------- ----------
TIME_LIMIT 3600
ACCEPT_PLANS TRUE
We can also set LOCAL_TIME_LIMIT parameter, For example lets say we want to limit the task time to 30 minutes. By default it is 1 hour.
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'LOCAL_TIME_LIMIT'
, value => 1800
);
END;
/
If you do not have the automatic evolve task enabled, you are most welcome to run it manually
Let us see how we do it manually
SQL> VARIABLE v1 NUMBER
SQL> VARIABLE task_name VARCHAR2(50)
SQL> VARIABLE exe_name VARCHAR2(50)
SQL> VARIABLE evol_out CLOB
SQL> EXECUTE :task_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_cf69d4c0f3821825',plan_name => 'SYS_OUTLINE_14090100280772402');
PL/SQL procedure successfully completed.
SQL> SELECT :task_name FROM DUAL;
:TASK_NAME
--------------------------------------------------------------------------------------------------------------------------------
TASK_44
## Execute the evolve task
SQL> EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:task_name);
PL/SQL procedure successfully completed.
SQL> SELECT :exe_name FROM DUAL;
:EXE_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXEC_27
SQL> EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## Following code shows how to report for evolve task
SQL> set long 10000
SQL> SELECT :evol_out FROM DUAL;
:EVOL_OUT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------------
-------------
Task Information:
---------------------------------------------
Task Name : TASK_44
Task Owner : SYS
Execution Name : EXEC_27
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
:EVOL_OUT
--------------------------------------------------------------------------------
Status : COMPLETED
Started : 09/01/2014 00:38:47
Finished : 09/01/2014 00:38:47
Last Updated : 09/01/2014 00:38:47
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
--------------------------------------------------------------------------------
.........................
.
.........................................
## Implement the evolve task
SQL> EXECUTE :v1 := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## To drop the baseline we execute the following code
EXEC :v1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_cf69d4c0f3821825');
_______________________________________________
In 11G R1 and R2 there is a slight difference in making changes to an accepted plan, In 11gR2, the sql plans are accepted and unaccepted using a different procedure which is used in 11gR1.
Also, once plans are accepted in 11gR2, they do not become UNACCEPTED. To avoid using that plan, the attribute ENABLED must be set to 'NO'.
In 11gR1
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ACCEPTED',
ATTRIBUTE_VALUE => 'YES');
END;
/
You will get below error if you use this procedurein 11G R2.
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
_______________________
In Oracle 11gR2
To ACCEPT the baseline use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE as follows:
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.evolve_sql_plan_baseline
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
VERIFY=>'NO' ,
COMMIT=>'YES');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
The parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans
If the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
The parameter COMMIT specifies whether to update the ACCEPTED status of non-accepted plans from 'NO' to 'YES'
When set to 'YES', baseline will be updated to ACCEPTED status and a report will be generated
When set to 'NO', a report will be generated without actually changing the status of the baseline(s) to ACCEPTED.
_______________________
To UNACCEPT the baseline set ENABLED attribute to NO using
DBMS_SPM.ALTER_SQL_PLAN_BASELINE:
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ENABLED',
ATTRIBUTE_VALUE => 'NO');
END;
/
Prior to 11gR2, the plan could be "UNACCEPTED" by executing ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ACCEPTED' and ATTRIBUTE_VALUE=> 'NO'.
The status of ACCEPTED in DBA_SQL_PLAN_BASELINES would be updated to 'NO'.
FROM 11gR2, to change a plan to be "unaccepted", you need to execute ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ENABLED' and ATTRIBUTE_VALUE=> 'NO'.
________________________
Sometimes because of some problems faced we need to enable tracing and see what's going in the background to see what went wrong, lets see how we can enable tracing for SPM
Normally to trace all sessions of SPM we can do the following
SQL>exec dbms_spm.configure('spm_tracing',1);
To disable we can do the following
SQL> exec dbms_spm.configure('spm_tracing',0);
To check and verify we can do the following
SQL> SELECT parameter_name, parameter_value FROM sys.smb$config WHERE parameter_name='SPM_TRACING';
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPM_TRACING 0
Lets take an example :=
SQL> exec dbms_spm.configure('spm_tracing',1);
PL/SQL procedure successfully completed.
SQL> DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'try',
sqlset_owner => 'TEST',
commit_rows => 5);
END;
/
PL/SQL procedure successfully completed.
This gives trace similar to:
.../diag/rdbms/demo/demo/trace/demo_ora_15811.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /.../orcl/oracle
System name: Linux
Node name: tlax66
Release: 2.6.9-78.0.0.0.1.ELsmp
Version: #1 SMP Thu Aug 29 18:15:25 EDT 2014
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 10311, image: oracle@orcl (TNS V1-V3)
_______________________
There have been some bugs and knows issues when using SPM in different versions, Some have been solved with some workarounds and some by uprading or patching. Please refer to below notes
Known Issues
Document 1187767.1 Baseline Not Used Under Plsql When OFE Is Changed
Document 1204524.1 Dbms_spm.Load_plans_from_sqlset Getting Xml Parsing Error
Document 12732879.8 Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
Document 11719151.8 Bug 11719151 - SQL Plan Management capture causes slowness
Document 9910484.8 Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
Document 14009271.8 Bug 14009271 - Cannot reproduce execution plan imported from SQL Tuning Sets / Outlines
_____________________________
References
http://docs.oracle.com/database/121/TGSQL/toc.htm
http://docs.oracle.com/database/121/ADMIN/toc.htm
Great Write Up!
ReplyDelete