Understanding Oracle SQL Plan Management SPM – Part 1

This time I want to talk about SQL Plan Management, explain how this feature works and how to make the best use of it. As is a very extensive topic I will split it in three posts.

Introduction

The Oracle Cost Based Optimizer (CBO) introduced in Oracle version 7. It determines the most efficient way to execute SQL statements after considering several factors such as database initialization parameters, optimizer statistics and many others like bind peeking, cardinality feedback, etc.

The CBO’s goal is to produce an optimal execution plan for the SQL statements executed in the database system. This is the reason why we want the CBO to be flexible enough to produce the best execution plans for the SQL statements. We must not forget that DB systems are dynamic enough to have data distribution changes, new Indexes may be added and initialization parameters could be modified, leading the CBO to produce multiple execution plans for the same SQL statement. This is an expected behavior of the CBO and we should be glad that is smart enough to produce optimal plans most of the times during the hard parsing, however sometimes it may also produce sub-optimal plans under special circumstances.

Here’s where plan stability tools come on the scene. Tools like hints, outlines, SQL Plan Management and custom SQL Profiles help the DBA and developers to allow only optimal plans be executed, hence avoiding severe performance problems within the database.

SQL Plan Management (SPM) is a new feature of Oracle 11g. SPM provides a framework for plan stability and control by ensuring that only selected plans are executed. If new plans are created by the CBO, they will not be executed until they are verified by the database or by the administrator and marked as accepted.

SPM controls the execution plans by using three control flags. The “ENABLED” flag that accepts two values YES & NO controls if the plan is available or not for the CBO to be considered. If the “ACCEPTED” flag is set to YES and “ENABLED” is set to YES, then the CBO will execute the plan. If “ACCEPTED” is set to NO, the plan should be verified by the database system or the administrator to check if it has comparable or better performance than the current plan. This process of making not “ACCEPTED” plans into “ACCEPTED” is called plan evolution. The last control flag name is “FIXED” and can only be set to YES if the plan is “ENABLED” and “ACCEPTED”, if set to YES plans have priority over “ACCEPTED” plans just because they are not expected to change.

SPM uses a mechanism called SQL plan baseline (baseline). Baseline is a group or set of “ACCEPTED” plans the CBO is allowed to use for a particular SQL statement. These baselines and the not “ACCEPTED” plans are stored into the SQL plan history. All baselines and the plan history are logically stored into the SQL management base (SMB) in the data dictionary. The SQL management base stores the statement log, plan histories, SQL plan baselines, and SQL profiles. Oracle provides information about SQL baselines in the DBA_SQL_PLAN_BASELINES and v$SQL system views. Database administrators can query information about what SQL baselines exists, their status and origin.

More information about DBA_SQL_PLAN_BASELINES can be found here https://docs.oracle.com/database/121/REFRN/refrn23714.htm#REFRN23714.

Oracle matches SQL statements with SQL baselines by using signatures. SQL signature is a unique identifier created from the normalized SQL text, uncased and whitespaces removed. SQL signatures ensure that the same SQL statement is always having the same SQL signature independent of the upper/lower case or spaces. SQL signatures are stored into the SQL management log in the SMB. If a SQL statement has its signature stored in the SMB, means to be a repeatable statement and a baseline will be created during the next execution.

SQL baselines also have three status flags. The first flag “REPRODUCED” is automatically set to YES when the CBO is able to reproduce the plan for the given SQL statement and NO when not possible, like when an Index is dropped. The second flag “AUTOPURGE” is user modifiable, if set to YES the plan will be purged when not used and reaches the SPM plan retention limit parameter. The last status flag “REJECTED” is set to YES when “ACCEPTED” is set to NO and the plan was verified (has LAST_VERIFIED), or “ENABLED” is set to no in 11gR2 and 12c versions.

SQL plan management is controlled by two initialization parameters. When the optimizer_use_sql_plan_baselines parameter is set to TRUE (default), the CBO will make use of the baselines created and stored in the SMB. The second parameter optimizer_capture_sql_plan_baselines is set to FALSE by default and controls if the database will automatically capture the SQL signature in the SQL management log and automatically create a SQL baseline on the second execution of the SQL statement. As a best practice and personal recommendation, don’t set optimizer_capture_sql_plan_baselines to TRUE. If set to TRUE, any baseline that is automatically created is also set to “ACCEPTED”, this means that the second execution of a SQL statement will always be used by the CBO even if there’s a better execution plan available. 

In the next post I’m going to cover how to capture and evolve SQL baselines in 11g and 12c version, stay tuned!

Thanks,

Alfredo