SQL Plan management and Baselines

Oracle SQL plans have evolved over the past several decades, from simple stored outlines to complex SQL plan management:

Optimizer plan stability (a.k.a. stored outlines) - Stored outlines were cumbersome to manage, and it was very difficult to "swap" execution plans with plan stability.

10g SQL Profiles - Starting in Oracle 10g, we see the SQL Profile approach, whereby a SQL tuning Set (STS) could be tested as a workload, and Oracle would allow the DBA to implement changes to execution plans.

11g SQL Plan management - Starting in 11g we finally see an easy-to-use approach to locking-down SQL execution plans.
The Oracle 11g view dba_sql_plan_baselines allows us to see all of the plans for any given SQL statement:


SQL Plan management - Starting in 11g, there is an even easier to use approach to locking-down SQL execution plans.
The 10g SQL Profile approach is deprecated, and uses only two parameters,
optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines.

select
   sql_handle,
   plan_name,
   enabled,
   accepted,
   fixed
from
   dba_sql_plan_baselines

 WHERE LOWER(sql_text) LIKE '%hr.employees%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a4397d478871  YES YES NO
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439c0e983c6  YES YES YES
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439cf314e9e  YES NO  NO

select
   s.sql_text,
   b.plan_name,
   b.origin,
   b.accepted
from
   dba_sql_plan_baselines b,
   v$sql s
where
   s.exact_matching_signature = b.signature
and
   s.sql_plan_baseline = b.plan_name;