SQL PLAN Management

It ensures that the performance doesn’t degrade due to change of execution plan. To guarantee this, only accepted execution plans will be used; any plan evolution will be tracked and evaluated at a later point in time and only be accepted as verified if new plan causes no runtime changes or an improvement of the runtime.

1. Capture

2. Selection

3. Evolution

Plan Capture
Create SQL plan baseline that represents accepted execution plans stored in SQL Management Base in SYSAUX tablespace



Automatic plan capture –
All repeatable sql staments will be automatically captured if OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE is set to true.

A SQL Plan history will be created for the statement, which will include the information used by the optimizer to reproduce the execution plan, such as SQL text, outline, bind variable and compilation environment.
Only accepted plans used in future in case a new plan is found for the same SQL` text it is added to the plan history and is marked for verification. It will be accepted only if its performance is better than the plan from the current SQL plan baseline.
Bulk Loading
We can capture the plan into a SQL Tuning Set, and then load these plans into SQL Management Base as SQL Plan Baseline using the PL/SQL procedure DBMS_SPM.LOAD_PLAN_FROM_SQLSET or through OEM.
To load plans from a SQL Tuning Set, use the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package:

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    sqlset_name => 'tset1');

END;

/

To load plans from the cursor cache, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package:

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

    sql_id => '99twu5t2dn5xd');

END;

/

Selecting SQL Plan Baseline
IF u set the OPTIMIZER_USE_SQL_BASELINE to true, each statement that is compiled the optimizer first uses a cost based search method to build a best cost plan then tried to find the matching plan in the SQL plan baseline. It evaluates the cost of each accepted plan in SQL plan baseline and selects the plan with lowest cost.
The plan generated during the execution of query also store in sql plan baseline but is accepted only if it doesnot causes any performance degradation.
Fixed SQL plan baselines indicate

to the optimizer that they are preferred. If the optimizer is costing SQL plan baselines and one of

the plans is fixed, the optimizer will only cost the fixed plan and go with that if it is reproducible.

If the fixed plan(s) are not reproducible the optimizer will go back and cost the remaining SQL

plan baselines and select the one with the lowest cost. Note that costing a plan is nowhere near

as expensive as a hard parse. The optimizer is not looking at all possible access methods but at

one specific access path.

Evolving SQL Plan Baseline.

When the optimizer finds a new plan for a SQL statement, the plan is added to the plan history

as a non-accepted plan that needs to be verified before it can become an accepted plan. It is

possible to evolve a SQL statement’s execution plan using Oracle Enterprise Manager or by

running the command-line function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. Using either of

these methods you have three choices:

1. Accept the plan only if it performs better than the existing SQL plan baseline

2. Accept the plan without doing performance verification

3. Run the performance comparison and generate a report without evolving the new plan.

If you choose option 1, it will trigger the new plan to be evaluated to see if it performs better

than a selected plan baseline. If it does, then the new plan will be added to the SQL plan baseline,

as an accepted plan. If not the new plan will remain in the plan history as a non-accepted plan

but its LAST_VERIFIED attribute will be updated with the current timestamp. A formatted text

report is returned by the function, which contains the actions performed by the function as well

as side-by-side display of performance statistics of the new plan and the original plan.

If you choose option 2, the new plan will be added to the SQL plan baseline as an accepted plan

without verifying its performance. The report will also be generated.

If you choose option 3 the new plan will be evaluated to see if it performs better than a selected

plan baseline but it will not be accepted automatically if it does. After the evaluation only the
report will be generated.