Tuesday 2 September 2014

12C SQL PLAN MANAGEMENT (SPM)

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




1 comment: