Sql plan Management in Oracle

SQL Plan Management (SPM) in Oracle is a preventative mechanism that provides a level of control over the execution plans used by the Oracle optimizer. It ensures that regression due to changes in execution plans doesn’t occur, even as the database undergoes changes such as database upgrades, a gathering of new statistics, or changes to system settings.

SQL Plan Management has three main components:

Sql Plan Capture:

• This component is responsible for capturing SQL execution plans automatically as SQL statements are executed in the database. The capture process can also be performed manually.
• This creates the sql plan baseline which stores the accepted execution plans for all the relevant SQL statements
• Sql plan baselines are stored in the sql management base in the SYSAUX tablespace

SQL Plan Selection

Oracle Optimizer uses SQL Plan Baselines during the execution of SQL statements.
When a new execution plan is found for a SQL statement, if it’s not part of the SQL Plan Baseline, Oracle will use the existing baseline plan. The new plan is stored as an unaccepted plan.
The new plan is only used if it is accepted into the SQL Plan Baseline, and this occurs when Oracle determines that the new plan performs better during the evolve phase

SQL Plan evolution

This evaluates the performance of all the unacceptable plans for a given statement with only that shows a performance improvement becoming an accepted plan in the SQL Plan Baseline

Here are some key operations you can do with SPM:

• Creating SQL Plan Baselines: You can create a SQL plan baseline for one or more SQL statements using the DBMS_SPM package.
• Loading SQL Plans: You can load plans from the cursor cache, SQL Tuning Set, or from a staging table.
• Evolving SQL Plan Baselines: You can use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evaluate the performance of non-accepted plans and accept them if they perform better.
• Displaying SQL Plan Baselines: You can use the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function to display the contents of a SQL plan baseline.
• Dropping SQL Plan Baselines: You can drop one or all SQL plan baselines for a SQL statement using the DBMS_SPM.DROP_SQL_PLAN_BASELINE function.

Remember that SPM doesn’t replace good statistics or proper tuning, it merely provides a safety net for plan regressions. The real benefit is seen when a change occurs that would have caused a plan regression, but SPM prevents it. Also, note that using SPM has some overhead in terms of additional CPU and memory usage, so its use should be carefully considered based on the needs of the database.

Related Articles

SQL Tuning Set in Oracle
how to create sql baseline in Oracle 19c
how to create sql baseline from Sql tuning set in Oracle
How to drop the sql baseline in Oracle
how to create sql baseline from AWR repository without STS