-- Oracle 11g SQL Plan Management (SPM) --

What is SQL Plan Management (SPM)

A big part of the performance of an application depends on its query execution, for the most part the Oracle Optimizer does a fair job without human intervention, however problems may arise for many reasons, until now there has not been guarantee that a plan execution will always change for better, many people have opted to use stored outlines (freeze execution plans) or lock their optimizer statistics, however the risk her is that you may be as well avoiding a more optimal execution plan. 

SQL Plan Management provides a framework for completely transparently controlled  execution plan evolution. It is a mechanism that records and evaluates your SQL plans over time and creates SQL Plan baselines which is composed by a group of plans known to be efficient. The idea is to use this as a preventive method to preserve the performance of SQL statements. With SPM the optimizer automatically managed execution plans and ensures that only known or verified plans are used. In other words when a new plan is found for a SQL Statement, it will not be used until it has been verified by the database to have comparable or better performance than the current plan.

Let me explain that better, to produce this guarantee only accepted (trusted) execution plans will be used; any plan evolution will be tracked and evaluated at a later point in time and only accepted as verified if the new plan causes no runtime change or an improvement of the runtime.

The main advantages of SPM is that it minimizes potential performance regressions that may occur due to event such as new deployments or upgrades. 

SPM Components

A) SQL Plan Baseline Capture

Process of creating SQL plan baselines that represent accepted executions plans for all relevant SQL statements. For it to work you must seed the SQL management base with the execution plans, which will become the new SQL plan baseline. There are 2 ways to achieve this

•  A.1 - Automatic Plan Capture: Automatic plan capture can be switched on by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (The default value is false), once enabled the SPM repository will be populated with any repeatable SQL statement.
• A.2 - Bulk load execution plans: This is specially useful when a database upgraded from previous a version of Oracle to 11g, or for new deployments. You can use achieve this in three different ways.
• A.2.1 - Using SQL Tuning Sets (STS): Capture the plan for a workload into a STS, then load the plan into the SQL Management Base as SQL Plan baslines using the PL/SQL Procedure DBMS_SPM.LOAD_PLANS_FROM_SQLSET. These are the steps to follow for this approach
• Create a STS that contain the execution plan for each of the SQL Statements.
• Load the STS into a staging table and export the staging table into a flat file.
• import the staging table from a flat file into the Database and unload the STS.
• Use DBMS_SPM.LOAD_PLANS_FROM_SQLSET to load the execution plans into the SQL Management base.
• A.2.2 - Using the plan in the Cursor Cache: Identify the SQL Statements you wish to capture and load them using the procedure DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
• A.2.3 - Unpack existing SQL plan baselines from as staging table:  If an application is developed in house, you can export a good plan from the test system into production using the following procedure.
• Create a staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure.
• Pack the plan beselines you want to export into the table using DBMS_SPM.PACK_STGTAB_BASLINE function.
• Export the staging table into a flat using the export command or data pump.
• Transfer this flat file to the target system. 
• Import the staging table from the flat file using the import command or data pump
• Unpack the SQL plan baselines from the staging table into the SQL Management base using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.

B) SQL Plan Baseline selection

Process of ensuring that only accepted execution plans are used for statements with a SQL Plan baseline and track all new executions in the plan history for a particular statement. Each time a SQL Statement gets compiled, the optimizer uses the traditional cost-based search method to build the best cost plan. Having the initialization parameter OPTIMIZER_USE_PLAN_BESELINES is set to TRUE (Default value) the optimizer will try to find a matching plan in the SQL Plan baselines. If a match if found then it is used, if not the newly generated plan will be added to the plan history; it will have to be verified before it can be accepted as a SQL Plan baseline. Instead of executing the new plan the optimizer will cost each of the accepted plans for the SQL statement and pick the one with the lower cost.

C) SQL Plan Baseline evolution

Process of evaluating all unverified executions  plans for a given statement in the plan history to be come either accepted or rejected. When a new plan if found, it is added to the plan history as a non-accepted plan that needs to be verified. it is possible to evolve a SQL statement execution plan using DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE using either of the following methods

    - Accept the plan only if it performs better than the existing SQL plan baselines.

    - Accept the plan without doing performance verification

    - Run the performance comparison and generate a report without evolving the new plan.

NOTE: The plan history includes all accepted and unaccepted plans, An unaccepted plan can be unverified (newly found but not verified) or rejected (verified but not found to be a performer ).  The Plan baselines are stored in the plan history in the SQL Management base in the SYSAUX tablespace. It is possible to influence the optimizer's choice of plan by marking a SQL Plan as fixed, Fixed SQL plans baselines tells the optimizer that they are preferred.

How to Enable / Disable SQL Plan Management tracing

You can enable tracing by executing the command below connected as a DBA. This will enable traces for all SPM session which will be located in /diag/rdbms///trace/

exec dbms_spm.configure('spm_tracing',1);

The following command will disable the tracing.

exec dbms_spm.configure('spm_tracing',0);

You can verify if tracing is enable or disables by executing the following command

SELECT parameter_name, parameter_value  FROM sys.smb$config WHERE parameter_name='SPM_TRACING';

In addition you can also enable tracing at the session level as follow

alter session set events 'trace [sql_planmanagement.*]';