Sql Plan Management (SPM)

Tags

,

Its an inbuilt database feature introduced in 11.2. It’s helpful to maintain stabile sql plans, or we can say control plan changes.  As per the documentation, it helps with maintain sql plan with database changes. It also improves the sql performance with the database changes, because it’s adaptable in nature.

We can use this feature automatically or manually, to use it automatic set these two parameters:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

An enhanced version of the Oracle optimizer, called SPM aware optimizer, accesses, uses, and manages this information which is stored in a repository called the SQL Management Base (SMB).

 

Process flow of SPM:

— Create base lines. (STS or CACHE)
— SPM Aware Optimizer. (Select best plan in the current DB environment).
— Evolving SQL plan baselines. (evolve sql baselines which were not accepted).
— User Interfaces and other features.(OEM, DBMS_SPM).

 

Queries that help to retrieve plan baselines:

select sql_text, sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines;
select *  from table(dbms_xplan.display_sql_plan_baseline(
sql_handle => ‘SYS_SQL_6b0spf85d0ge21’, format => ‘basic’));