SQL Plan Management

SQL Plan Management:

Purpose: 

Prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.

Factors affecting plan Management:

• New optimizer version
• Changes to optimizer statistics and optimizer parameters
• Changes to schema and metadata definitions
• Changes to system settings
• SQL profile creating

Steps:

1. Capture Plan Baseline: 2 ways

* Automatic Plan Capture:
Setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE

* Manual Plan Capture

• Loading Plans from SQL Tuning Sets and AWR Snapshots.
• Loading Plans from the Cursor Cache

2. Selecting SQL Baseline

OPTIMIZER_USE_SQL_PLAN_BASELINES

3. Evolving base line plans: 2 ways
* Manual Method: Plans loaded from SQL tuning set or cursor cache.

* Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

To review SQL Plan Baselines

select * from table( 

    dbms_xplan.display_sql_plan_baseline( 

        sql_handle=>'SYS_SQL_209d10fabbedc741', 

        format=>'basic'));

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

SQL Management Base:

Set supplied PL/SQL packages to facilitate the disk usage, retention time and movement of baselines 
across environments.

Tried out example

Test:: [Extended from the example above]
1. Dropped the SQL Plan baseline which was created for FTS.
2. Now i dropped the index SPM_TST_TAB_IDX from the table.
3. Now we have SQL Plan baseline created for the index range scan. But in the table the index is dropped.
4. Now when i ran the SQL statement, a new SQL Plan baseline is created and is used.
5. But the new baseline is not a accepted one.

Oracle Documentation
DBMS_SPM