Get the Execution Plan from/using SQL management base (SQL plan baselines)

It is good thing, if you know what is SMB/SPB/SPM before you actually jump into execution plan extraction.  Some pointers ......

The SQL Management Base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement log, plan histories, and SQL plan baselines, as well as SQL profiles.

SQL plan baselines are the key objects that SQL Plan Management uses to prevent unverified change to SQL execution plans. When SQL Plan Management is active, there will not be drastic changes in performance even as the statistics change or as the database version changes. Until a new plan is verified to produce better performance than the current plan, it will not be considered by the optimizer.

SQL Outlines have been used in past versions. They are still available for backward compatibility, but Outlines are deprecated in favor of SQL Plan Management.

A SQL plan change can occur due to a variety of reasons such as optimizer version, optimizer statistics, optimizer parameters, schema definitions, system settings, and SQL profile creation. Various plan control techniques are available in the Oracle Database to address performance regressions due to plan changes. The oldest is the use of hints in the SQL code to force a specific access path. Stored outlines allowed the hints to be stored separate from the code and modified.

SQL Plan Management automatically controls SQL plan evolution by maintaining what is
called “SQL plan baselines.” With this feature enabled, a newly generated SQL plan can join a SQL plan baseline only if it has been proven that doing so will not result in performance regression. So, during the execution of a SQL statement, only a plan that is part of the SQL plan baseline can be used.

For SQL statements that are executed more than once, the optimizer maintains a history of plans for individual SQL statements. The optimizer recognizes a repeatable SQL statement by maintaining a statement log. A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged. After a SQL statement is recognized as repeatable, various plans generated by the optimizer are maintained as a plan history containing relevant information (such as SQL text, outline, bind variables, and compilation environment) that is used by the optimizer to reproduce an execution plan.

The main benefit of the SQL Plan Management feature is performance stability of the system by avoiding plan regressions.

A plan history contains different plans generated by the optimizer for a SQL statement over time. However, only some of the plans in the plan history may be accepted for use. For example, a new plan generated by the optimizer is not normally used until it has been verified not to cause a performance regression. Plan verification is done by default as part of the Automatic SQL Tuning task running as an automated task in a maintenance window.

An Automatic SQL Tuning task targets only high-load SQL statements.For those statements, it automatically implements actions such as making a successfully verified plan an accepted plan. A set of acceptable plans constitutes a SQL plan baseline. The very first plan generated for a SQL statement is obviously acceptable for use; therefore, it forms the original plan baseline. Any new plans subsequently found by the optimizer are part of the plan history but not part of the plan baseline initially.

The DISPLAY_SQL_PLAN_BASELINE table function displays formatted information about a specific plan, or all plans in the SQL plan baseline in one of three formats (BASIC, TYPICAL or ALL). The following example displays the default format (TYPICAL) report for a specific plan. Or you can query DBA_SQL_PLAN_BASELINES view.

SET LONG 10000

SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_d90440b9ed3324'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

SQL handle: SYS_SQL_7b76323ad90440b9
SQL text: SELECT description FROM   spm_test_tab WHERE  id = 99
--------------------------------------------------------------------------------

Plan name: SQL_PLAN_d90440b9ed3324
Enabled: YES     Fixed: YES     Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3121206333

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   2 - access("ID"=99)
25 rows selected.

SQL>

-Asifkhan P.