What is Adaptive SQL Plan Management?

SQL Plan Management is a mechanism that enables the optimizer to automatically manage execution plans. SQL plan management works to prevent performance regressions caused by plan changes by ensuring that the Oracle database uses only known or verified plans. Adaptive SQL Plan Management overtime adapts to changes, such as new optimizer statistics or indexes, by analyzing and verifying only the plan changes that improve performance.

The Three Main Components of Adaptive SQL Plan Management

SQL Plan Baseline Capture

During the SQL plan baseline capture phase, the database detects plan changes and records the new plan so that it can be evolved (verified) by the database administrator. To recognize repeatable SQL statements, the database maintains a statement log that contains the SQL ID. An accepted plan in the SQL Plan Baseline will contain the following:
Set of hints
Plan hash value
Plan-related information
You can display a plan using the following query: DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package. The displayed SQL handle will look like this:

———————————————————————————-
Plan name: SYS_SQL_PLAN_bbedc741f554c408
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
———————————————————————————-
Plan hash value: 4115973128

———————————————————————————-
| Id | Operation | Name |
———————————————————————————-
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS |
| 4 | BITMAP CONVERSION TO ROWIDS | |
| 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |
| 6 | PARTITION RANGE | |
| 7 | BITMAP CONVERSION TO ROWIDS | |
| 8 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES |
———————————————————————————-

These accepted execution plans are then stored in a plan history and therefore become the SQL Plan baseline. SQL Plan Baseline Capture can be configured for automatic capture of plan history and SQL plan baselines for repeatable SQL statements. As an alternative, you can also manually load plans as SQL plan baselines. In previous releases, auto-capture of SQL plan baselines was disabled by default, but evolution of existing baselines is now automated after the release of Oracle Database 12c.

 

SQL Plan Baseline Selection

The purpose of the SQL Plan Baseline Selection is to ensure that only accepted execution plans are used for statements with a SQL plan baseline and to track all new execution plans – accepted or not – in the plan history for a statement. Each time the Oracle database compiles a SQL statement, the optimizer does the following:

• Finds the most cost-effective plan based on the search method
• Tries to find a similar plan in the SQL plan baseline
• Does either of the following depending on whether a match is found:
  • If found, the optimizer proceeds using the similar plan
  • If not found, the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the most cost-efficient plan
• 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 new plans perform better, then the database promotes them into the baseline; otherwise, the database does not promote them

 

SQL Plan Baseline Evolution

During the SQL plan baseline evolution phase, the database evaluates all unverified execution plans for a given statement in the plan history to become either accepted or rejected. Then, the database adds plans to the baseline that have been verified to not cause performance regressions. These enabled, accepted plans then become part of the SQL Plan Baseline.

NOTE: The new plan can only be executed during the maintenance window, or the SQL plan baseline must be manually evolved with a new evolve task.

 

What’s the difference between SQL Server Profiler and Adaptive SQL Server Plan Management?

SQL Server profiler is another tool that helps ensure the optimizer is only using effective plans. A SQL profile is a set of auxiliary information specific to a SQL statement. In general, the goal of the SQL plan management mechanism is to preserve the performance of corresponding SQL statements, regardless of changes in the database. SQL profiles, on the other hand, were introduced in Oracle Database 10g and were supposed to guide the Optimizer to a better plan. They do not guarantee the same plan each time the statement is parsed.

 

Benefits of Adaptive SQL Plan Management

SQL Plan Management can affect SQL Server performance when upgrading databases, as well as system and data changes. Most plan changes result in either improvement or no performance change. SQL plan baselines significantly minimize potential regressions resulting from an upgrade, while preventing plan changes may cause performance regressions

For more information on how to enhance your database performance, visit our blog on the best practices to prevent SQL Server deadlocks, tips on cost effective ways to save storage costs in PAAS Azure SQL, or read our various other blogs on our resources page.