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.

  Most plan changes result in either improvement or no performance change. However, some plan changes may cause performance regressions. 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.

• Deployment of new application modules introduces new SQL statements into the database.

  The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines over time to produce better performance.

Both SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans.

Both profiles and baselines are internally implemented using hints. However, these mechanisms have significant differences, including the following:

• In general, SQL plan baselines are proactive, whereas SQL profiles are reactive.

  Typically, you create SQL plan baselines before significant performance problems occur. SQL plan baselines prevent the optimizer from using suboptimal plans in the future.

  The database creates SQL profiles when you invoke SQL Tuning Advisor, which you do typically only after a SQL statement has shown high-load symptoms. SQL profiles are primarily useful by providing the ongoing resolution of optimizer mistakes that have led to suboptimal plans. Because the SQL profile mechanism is reactive, it cannot guarantee stable performance as drastic database changes occur.

  Figure 27-1 SQL Plan Baselines and SQL Profiles

  
  Description of "Figure 27-1 SQL Plan Baselines and SQL Profiles"

• SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.

  A SQL plan baseline is a set of accepted plans. Each plan is implemented using a set of outline hints that fully specify a particular plan. SQL profiles are also implemented using hints, but these hints do not specify any specific plan. Rather, the hints correct miscalculations in the optimizer estimates that lead to suboptimal plans. For example, a hint may correct the cardinality estimate of a table.

  Because a profile does not constrain the optimizer to any one plan, a SQL profile is more flexible than a SQL plan baseline. For example, changes in initialization parameters and optimizer statistics enable the optimizer to choose a better plan.

Oracle recommends that you use SQL Tuning Advisor. In this way, you follow the recommendations made by the advisor for SQL profiles and plan baselines rather than trying to determine which mechanism is best for each SQL statement.

When enabled, the database checks whether executed SQL statements are eligible for automatic capture.

You 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.

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

Use Cloud Control or 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.

Figure 27-2 Loading Plans into a SQL Plan Baseline

Description of "Figure 27-2 Loading Plans into a SQL Plan 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:

  1. Creates a plan history and plan baseline for the statement

  2. Marks the initial plan for the statement as accepted

  3. Adds the plan to the new baseline

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

  1. Marks the loaded plan as accepted

  2. 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. You can load plans without enabling them by setting the enabled parameter to NO in the DBMS_SPM.LOAD_PLANS_FROM_% functions.

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.

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.

Use the evolve procedures on demand, or configure the subprograms to run automatically. The automatic maintenance task SYS_AUTO_SPM_EVOLVE_TASK executes daily in the scheduled maintenance window. The task perform the following actions automatically:

1. Selects and ranks unaccepted plans for verification

2. Accepts each plan if it satisfies the performance threshold

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 stores information that the optimizer can use to maintain or improve SQL performance.

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 does not use SQL plan management and SQL tuning features when this tablespace is unavailable.

Figure 27-5 SMB Architecture

Description of "Figure 27-5 SMB Architecture"

SMB data related to a PDB is stored in the PDB, and is included if the PDB is unplugged. A common user whose current container is the CDB root can view SMB data for PDBs. A user whose current container is a PDB can view the SMB data for the PDB only.

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.

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

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$;
 
 SIGNATURE     BATCH#
---------- ----------
1.8096E+19          1

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT * FROM SQLLOG$;
 
 SIGNATURE     BATCH#
---------- ----------
1.7971E+19          1
1.8096E+19          1

SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
no rows selected

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
SQL_HANDLE           SQL_TEXT
-------------------- --------------------
SQL_f9676a330f972dd5 SELECT job_title FRO
                     M hr.jobs WHERE job_
                     id='PR_REP'

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. For plans created before Oracle Database 12c, the function must compile the SQL statement and generate the plan because the SMB does not store it.