SPM - SQL Plan Management

How are we evolved to 11g SQL plan management (SPM) ?

Outlines -> Stored Outlines are very difficult to manage and also very difficult to swap the execution plans with plan stability.
10g SQL Profiles -> Starting from 10g we used the SQL profile concept ie SQL tuning sets and also we can change the execution plans.
11g SQL Plan management (SPM) -> It is an easy to use tool to lock the best execution plans.

The major difference between Outlines and SPM is that Outlines are fixed and we cant override execution plan, But SPM uses baselines that can be evaluated for better plans and activated in place of original plans.

The difference between SQL Profiles and SPM is that the query plan changes based on the predicate used, But with SQL Plan Baselines,  the plan would be the same regardless of the value in the predicate.
For example queries with "Where cust_id= ", "where book_id= " will have different execution plans in SQL profiles.

What is SQL Baseline ?
Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. Baselines stores the plan_hash_value, so they know if they are reproducing the correct plan or not.
-- Baselines will be used by default in 11gR1, if they exist. There is a parameter to control whether they are used or not (OPTIMIZER_USE_SQL_PLAN_BASELINE). It is set to TRUE by default.
-- Baselines will not be created by default in 11gR1. So, much like with the older Outlines or SQL Profiles, you must do something to create them.
-- There is a view called DBA_SQL_PLAN_BASELINES that exposes the Baselines that have been created.
-- Just like Outlines and SQL Profiles, Baselines apply to all instances in a RAC environment (they are not localized to a specific instance).

How to create Base lines ?
-- Baselines are created automatically by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
-- From the cursor cache using dbms_spm.load_plans_from_cursor_cache
-- From the tuning sets using dbms_spm.load_plans_from_sqlset

These created baselines are applied to any SQL statement where the normalized text matches ( means same execution plan ) ie the baseline created for one statement can be attached to a different statement. You dont need to do extra , but call the procedure directly and it’s done. Of course the optimizer will have to verify that the plan will work for the statement you attach it to. If it fails this validation step, then the optimizer will ignore it and go on about it’s normal business.

Well ... we discussed about baselines and how they can be created  .....But how are these baselines related to SQL Plan Management (SPM). What is the relationship between them ? how can they choose the best execution plan ?
When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer tries to find a matching plan in the SQL plan baseline that is flagged as accepted. If a matching plan is found, the optimizer will uses the plan. If the SQL plan baseline doesn't contain a matching plan, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, the optimizer will use the original plan with the lowest cost and mark this as accepted.
The final conclusion steps are  load , evolve , test and accept.

Are the baselines fully controlled by oracle internally or can we do some maintenance tasks with SQL Plan Baselines ?
1) We can alter the attributes of SQL Plan Baselines with ALTER_SQL_PLAN_BASELINE function
--enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
--fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
--autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
--plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
--description : Used to amend the SQL plan description, up to a maximum of 30 character.
2) Diplay the baseline information using
DBA_SQL_PLAN_BASELINES
DBMS_XPLAN.display_sql_plan_baseline
3) Transferring SQL Plan Baselines - DBMS_SPM.pack_stgtab_baseline
4) Dropping SQL Plan Baselines

References :
SQL Plan Management(SPM) - Theory