Understanding Oracle SQL Plan Management SPM – Part 2

In my previous post Understanding SQL Plan Management – Part 1, I tried to cover some basic concepts of SQL Plan Management (SPM). This post will show you the main components of SPM and how they work in the different versions of Oracle.   

SQL plan management framework has three main components, plan capture, plan selection and plan evolution. These components allow administrators choose which plans should be executed by the database. Plan capture is the process of loading execution plans from different sources into the SQL management base. Plan selection is the process the database follows in order to choose the best plan available after considering many factors including SQL baselines. Plan evolution is the process of making baselines executable after verifying if any performance improvement is available.

Plan capture can be done in two forms, automatic and manual. Automatic plan capture takes place when the database initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE. Keep in mind that the default value for this parameter is FALSE. If automatic capture is enabled, the CBO will log the SQL signature of any SQL statement executed for the very first time in the database. When the SQL statement is executed for the second time it will recognize it as a repeatable statement in the database and SQL baselines are automatically created and marked as “ACCEPTED” for each repeatable SQL statement. Be careful with this feature because the baseline will be created using the execution plan of the second execution whether is optimal or not. If the CBO produces a better execution plan after the baseline is created, the plan will be stored in the SMB but not used until the plan is evolved.

Manual plan capture is the most common method to capture plans. It can be done in conjunction or instead of automatic capture for a single SQL statement or a group of statements. Plans can be manually loaded by using either DBMS_SPM package or Oracle Enterprise Manager (OEM) from four different sources. Manual loaded plans are automatically “ACCEPTED” and the CBO will continue to load plans automatically for this plans even if automatically plan capture is disabled. Plans automatically loaded after the manual load are marked as not “ACCEPTED”. You can load plans from four sources. From cursor cache, which are the active plans in memory. From SQL tuning sets, which require the SQL tuning pack or real application testing license. From stored outlines and from another Oracle database system using datapump. In order to transfer baselines from one database system to another, you must pack, load and unpack the baseline by following the below steps:

1. On the original system, create a staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure.

2. Pack the SQL plan baselines you want to export from the SQL management base into the staging table using the DBMS_SPM.PACK_STGTAB_BASELINE function.

3. Export the staging table into a flat file using the export command or Oracle Data Pump.

4. Transfer this flat file to the target system.

5. Import the staging table from the flat file using the import command or Oracle Data Pump.

6. Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.

The CBO calculates the execution plan every time the SQL statement is parsed (compiled) and then proceeds to execute the statement. If the OPTIMIZER_USE_SQL_PLAN_BASELINES is set to TRUE, the CBO will check if a SQL baseline exists before the plan is executed. If there is no SQL signature that matches the parsed SQL statement then the CBO will log this signature in the statement log if the automatic capture is enabled or just proceeds to execute the statement if disabled. If a signature matches the statement and a baseline doesn’t exists, it will proceed to create a plan for verification but if a baseline already exists then the baseline will be executed. If more than one “ACCEPTED” plan exists in the baseline, the CBO costs each plan for the given SQL statement and picks the one with the lowest cost. If a baseline is marked as “FIXED”, the CBO picks the lowest cost fixed plan unless all fixed plans are marked as non-reproducible.

Plan evolution, in simple terms is the process to “ACCEPT” or “REJECT” plans after verifying that performance is better or not compared with the current plan. Plan evolution as plan capture can be done automatic and manual. Automatic plan evolution is managed by the SQL tuning advisor task in 11g versions, while is managed by the SPM Evolve Advisor in 12c.

Adaptive SQL Plan Management  is one of the new features of Oracle 12c. Adaptive SQL Plan Management, is just the new automatic evolve task SYS_AUTO_SPM_EVOLVE_TASK that runs in the nightly maintenance window and is enabled by default. This task ranks all unaccepted plans and runs the evolve process for them. If the plan performs 1.5x times better than the current plan in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. This ratio defined by the hidden parameter _PLAN_VERIFY_IMPROVEMENT_MARGIN and can be modified. After the evolve task is complete, a persistent report is generated with details on how the non-accepted plan perform compared to the accepted plan performance. Administrators can go back and check what plans were evolved to any point in time.

      

Manual plan evolution is bit different in every version of the database. In 11g release 1, is being controlled using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function and by changing the attribute_name ‘ACCEPTED’ to ‘YES’. Is the administrator’s responsibility verify that the plan is performing better than the current plan. In 11g release 2, the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function helps the adminstrator verify if the plan performs better than the current plan and if it does, the plan is marked as “ACCEPTED”. A new SPM Evolve Advisor API is available in 12c version. Create task, execute task and report the evolve taks are part of the three step process of evolving plans. Unaccepted plans are not manually evolved when using the SPM Evolve Advisor, therfore the plan must be manually accepted using DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE function.   

In the next post I will show you how to use the different DBMS_SPM functions to capture and evolve plans.

Thanks,

Alfredo