SQL plan management

SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.

Benefits of SQL Plan Management

SQL plan management can improve or preserve SQL performance in database upgrades and system and data changes.

Specifically, benefits include:

•    A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements.
SQL plan baselines significantly minimize potential regressions resulting from an upgrade.
When you upgrade, the database only uses plans from the plan baseline.
The database puts new plans that are not in the current baseline into a holding area, and later evaluates them to determine whether they
use fewer resources than the current plan in the baseline.
If the plans perform better, then the database promotes them into the baseline; otherwise, the database does not promote them.

•    Ongoing system and data changes can affect plans for some SQL statements, potentially causing performance regressions.
SQL plan baselines help minimize performance regressions and stabilize SQL performance.

SQL Plan Baselines

SQL plan management uses a mechanism called a SQL plan baseline, which is a set of accepted plans that the optimizer is allowed to use for a SQL statement.
In this context, a plan includes all plan-related information (for example, SQL plan identifier, set of hints, bind values, and optimizer environment)
that the optimizer needs to reproduce an execution plan.
The baseline is implemented as a set of plan rows and the outlines required to reproduce the plan. An outline is a set of optimizer hints used to force a specific plan.

Component Of SQL PLAN Managements

The main components of SQL plan management are as follows:

•    Plan capture
•    Plan selection
•    Plan evolution



1=> Plan Capture

SQL plan capture refers to techniques for capturing and storing relevant information about plans in the SQL Management Base for a set of SQL statements.
Capturing a plan means making SQL plan management aware of this plan.
You can configure initial plan capture to occur automatically by setting an initialization parameter, or you can capture plans manually by using the DBMS_SPM package.

A=> Automatic Initial Plan Capture

When enabled, the database checks whether executed SQL statements are eligible for automatic capture.
We can enable automatic initial plan capture by setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true (the default is false).
Note that the initialization parameter OPTIMIZER_USE_SQL_PLAN_BASELINES is independent. 
For example, if OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is true,
then the database creates initial plan baselines regardless of whether OPTIMIZER_USE_SQL_PLAN_BASELINES is true or false.

Eligibility for Automatic Initial Plan Capture
The first check for eligibility is repeated execution. 

Plan Matching for Automatic Initial Plan Capture (What IF plan already PART and NOT PART of BASELINE)

If the database executes a repeatable SQL statement, and if this statement passes through the DBMS_SPM.CONFIGURE filters,
then the database attempts to match a plan in the SQL plan baseline.

For automatic initial plan capture, the plan matching algorithm is as follows:

=> If a SQL plan baseline does not exist, then the optimizer creates a plan history and SQL plan baseline for the statement,
       marking the initial plan for the statement as accepted and adding it to the SQL plan baseline.
=> If a SQL plan baseline exists, then the optimizer behavior depends on the cost-based plan derived at parse time:
       -> If this plan does not match a plan in the SQL plan baseline, then the optimizer marks the new plan as unaccepted and adds it to the SQL plan baseline.
       -> If this plan does match a plan in the SQL plan baseline, then nothing is added to the SQL plan baseline.



B=> Manual Initial Plan Capture

In SQL plan management, manual plan capture refers to the user-initiated bulk load of existing plans into a SQL plan baseline.

Use PL/SQL to load the execution plans for SQL statements from AWR, a SQL tuning set (STS), the shared SQL area, a staging table, or a stored outline.



Plan Matching for Manual Initial Plan Capture (What IF plan already PART and NOT PART of BASELINE)

The loading behavior varies depending on whether a SQL plan baseline exists for each statement represented in the bulk load:

=> If a baseline for the statement does not exist, then the database does the following:

Creates a plan history and plan baseline for the statement
Marks the initial plan for the statement as accepted
Adds the plan to the new baseline

=> If a baseline for the statement exists, then the database does the following:

Marks the loaded plan as accepted
Adds the plan to the plan baseline for the statement without verifying the plan's performance

Manually loaded plans are always marked accepted because the optimizer assumes that any plan loaded manually by the administrator has acceptable performance.


2=> Plan Selection

SQL plan selection is the optimizer ability to detect plan changes based on stored plan history,
and the use of SQL plan baselines to select plans to avoid potential performance regressions.

When the database performs a hard parse of a SQL statement, the optimizer generates a best-cost plan.
By default, the optimizer then attempts to find a matching plan in the SQL plan baseline for the statement.

If no plan baseline exists, then the database runs the statement with the best-cost plan.

If a plan baseline exists, then the optimizer behavior depends on whether the newly generated plan is in the plan baseline:

    If the new plan is in the baseline, then the database executes the statement using the found plan.
    If the new plan is not in the baseline, then the optimizer marks the newly generated plan as unaccepted and adds it to the plan history. 

Optimizer behavior depends on the contents of the plan baseline:

If fixed plans exist in the plan baseline, then the optimizer uses the fixed plan with the lowest cost.
If no fixed plans exist in the plan baseline, then the optimizer uses the baseline plan with the lowest cost.
If no reproducible plans exist in the plan baseline, which could happen if every plan in the baseline referred to a dropped index,
then the optimizer uses the newly generated cost-based plan.



3=> Plan Evolution

In general, SQL plan evolution is the process by which the optimizer verifies new plans and adds them to an existing SQL plan baseline.

Purpose of Plan Evolution

Typically, a SQL plan baseline for a statement starts with one accepted plan.

However, some SQL statements perform well when executed with different plans under different conditions.
For example, a SQL statement with bind variables whose values result in different selectivities may have several optimal plans.
Creating a materialized view or an index or repartitioning a table may make current plans more expensive than other plans.

If new plans were never added to SQL plan baselines, then the performance of some SQL statements might degrade.
Thus, it is sometimes necessary to evolve newly accepted plans into SQL plan baselines.
Plan evolution prevents performance regressions by verifying the performance of a new plan before including it in a SQL plan baseline.

How Plan Evolution Works

Specifically, plan evolution consists of the following distinct steps:

=> Verifying
The optimizer ensures that unaccepted plans perform at least as well as accepted plans in a SQL plan baseline (known as plan verification).

=> Adding
After the database has proved that unaccepted plans perform as well as accepted plans, the database adds the plans to the baseline.

So that a new plan is not usable by SQL plan management until the optimizer verifies plan performance relative to the SQL plan baseline.

PL/SQL Subprograms for Plan Evolution

The DBMS_SPM package provides procedures and functions for plan evolution.

=> Evolving SQL PLANS Manually

The DBMS_SPM package provides procedures and functions for plan evolution.
These subprograms use the task infrastructure. For example, CREATE_EVOLVE_TASK creates an evolution task, whereas EXECUTE_EVOLVE_TASK executes it.
All task evolution subprograms have the string EVOLVE_TASK in the name.

=> Evolving SQL PLANS Automatically

The automatic maintenance task SYS_AUTO_SPM_EVOLVE_TASK executes daily in the scheduled maintenance window. 
The task perform the following actions automatically:
      -> Selects and ranks unaccepted plans for verification
      -> Accepts each plan if it satisfies the performance threshold

Storage Architecture for SQL Plan Management

The SQL plan management infrastructure records the signatures of parsed statements, and both accepted and unaccepted plans.

SQL Management Base

The SQL management base (SMB) is a logical repository in the data dictionary.
The SMB contains the following:

    SQL statement log, which contains only SQL IDs
    SQL plan history, which includes the SQL plan baselines
    SQL profiles
    SQL patches

The SMB resides in the SYSAUX tablespace and uses automatic segment-space management. Because the SMB is located entirely within the SYSAUX tablespace, 
the database can not use SQL plan management and SQL tuning features when this tablespace is unavailable.



What is SQL Statement Log (To identify Repeatable statement)

When automatic SQL plan capture is enabled, the SQL statement log contains the signature of statements that the optimizer has evaluated over time.
A SQL signature is a numeric hash value computed using a SQL statement text that has been normalized for case insensitivity and white space. 
When the optimizer parses a statement, it creates signature.
During automatic capture, the database matches this signature against the SQL statement log (SQLLOG$) to determine whether the signature has been observed before. 
If it has not, then the database adds the signature to the log. 
If the signature is already in the log, then the database has confirmation that the statement is a repeatable SQL statement.

What is SQL Plan History

The SQL plan history is the set of captured SQL execution plans. The history contains both SQL plan baselines and unaccepted plans.

In SQL plan management, the database detects new SQL execution plans for existing SQL plan baselines and records the new plan in the history 
so that they can be evolved (verified). Evolution is initiated automatically by the database or manually by the DBA.

Starting in Oracle Database 12c, the SMB stores the execution plans for all SQL statements in the SQL plan history. 
The DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function fetches and displays the plan from the SMB. 

=> Enabled Plans

An enabled plan is a plan that is eligible for use by the optimizer.

When plans are loaded with the enabled parameter set to YES (default), the database automatically marks the resulting SQL plan baselines as enabled, 
even if they are unaccepted. You can manually change an enabled plan to a disabled plan, which means the optimizer can no longer use the plan even if it is accepted.

=> Accepted Plans

An accepted plan is a plan that is in a SQL plan baseline for a SQL statement and thus available for use by the optimizer. 
An accepted plan contains a set of hints, a plan hash value, and other plan-related information.

The SQL plan history for a statement contains all plans, both accepted and unaccepted. After the optimizer generates the first accepted plan in a plan baseline, 
every subsequent unaccepted plan is added to the plan history, awaiting verification, but is not in the SQL plan baseline.

=>  Fixed Plans

A fixed plan is an accepted plan that is marked as preferred, so that the optimizer considers only the fixed plans in the baseline. 
Fixed plans influence the plan selection process of the optimizer.

Assume that three plans exist in the SQL plan baseline for a statement. You want the optimizer to give preferential treatment to only two of the plans. 

SQL QUERY REPETABLITY

During automatic capture, the database matches this signature against the SQL statement log (SQLLOG$) to determine whether the signature has been observed before.
If it has not, then the database adds the signature to the log.
If the signature is already in the log, then the database has confirmation that the statement is a repeatable SQL statement.

SQL> SELECT * FROM SQLLOG$;

no rows selected

SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';

JOB_TITLE
-----------------------------------
President

SQL> SELECT * FROM SQLLOG$;

no rows selected

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

Session altered.

SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';

JOB_TITLE
-----------------------------------
President

SQL> SELECT * FROM SQLLOG$;

 SIGNATURE     BATCH#
---------- ----------
9.8456E+17        1
1.8096E+19        1

SQL> SELECT SQL_HANDLE, SQL_TEXT FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT job_title%';

no rows selected

SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';

JOB_TITLE
-----------------------------------
President

SQL> SELECT SQL_HANDLE, SQL_TEXT FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT job_title%';

SQL_HANDLE                 SQL_TEXT
--------------------------------------------------------------------------------
SQL_fb21d94e54d9612c           SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES'

Views and Packages & Parameters =>

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
OPTIMIZER_USE_SQL_PLAN_BASELINES
DBA_SQL_MANAGEMENT_CONFIG
SQLLOG$
DBA_SQL_PLAN_BASELINES
DBMS_SPM