This post is a first part of two.
It's a copy and paste from several links that you find in the "Reference" section.
SQL Plan Management
SPM maintains, on disk, a plan history consisting of different execution plans generated for each managed SQL statement. 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).
The plan history enables the SPM aware optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. A brand new plan represents a plan change that has potential to cause performance regression. For this reason, the SPM aware optimizer does not choose a brand new best-cost plan. Instead, it chooses from a set of accepted plans. An accepted plan is one that has been either verified to not cause performance regression or designated to have good performance. A set of accepted plans is called a SQL plan baseline, which represents a subset of the plan history.
After you create a SQL plan baseline for a statement, subsequent executions of that statement will use the SQL plan baseline. From all the plans in the SQL plan baseline, the optimizer will select the one with the best cost in the current environment (including bind values, current statistics, parameters, etc.). The optimizer will also generate the best-cost plan that it would otherwise have used without a SQL plan baseline. However, this best-cost plan will not be used but instead added to the statement's plan history for later verification. In other words, the optimizer will use a known plan from the SQL plan baseline instead of a new and hitherto unknown plan.
It is possible for a SQL statement to have a stored outline as well as a SQL plan baseline. If a stored outline exists for a SQL statement and is enabled for use, then the optimizer will use it, ignoring the SQL plan baseline. In other words, the stored outline trumps a SQL plan baseline.
Adaptive cursor sharing (ACS) may generate multiple cursors for a given bind sensitive SQL statement if it is determined that a single plan is not optimal under all conditions. Each cursor is generated by forcing a hard parse of the statement. The optimizer will normally select the plan with the best cost upon each hard parse.
When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set.
Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive.
Remember that they are responsible for two different tasks. ACS controls whether or not a child cursor is shared on a particular execution. For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values. SPM controls which plans the optimizer may choose. If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM. But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer's choice of plans, without regard to whether this query is being optimized due to ACS.
My Note
SQL, with bind variable, run -> Parsing is done, peeking the content -> ACS is active, so a new execution plan is chosen -> Baseline is working, so the plan is compared with the one inside the "Plan Baseline" -> If the plan exists and is accepted, it will be selected and the original plan will not run, otherwise it will be added to the plan history and the original will run (not optimized. Well in this case I tell that the orignial plan was not optimized).
Queryng the dba_sql_plan_baselines (for example):
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
The 'NO' value for the accepted column implies that the new plan is in the plan history but is not available for use until it is verified to be a good plan. The optimizer will continue to use an accepted plan until new plans are verified and added to the SQL plan baseline. If there is more than one plan in the SQL plan baseline, the optimizer will use the one with the best cost under the then-current conditions (statistics, bind values, parameter settings and so on).
When you create a SQL plan baseline for a SQL statement, the SPM aware optimizer thus guarantees that no new plans will be used other than the ones in the SQL plan baseline. This prevents unexpected plan changes that sometimes lead to performance regressions.
A SQL statement can have both a SQL profile and a SQL plan baseline. In this case, the SPM aware optimizer will use both the SQL profile and the SQL plan baseline. The SQL profile contains additional information that helps the optimizer to accurately cost each accepted plan and select the best one. The SPM aware optimizer may choose a different accepted plan when a SQL profile is present than when it is not.
When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted plan to accepted status, thus evolving the SQL plan baseline to two plans. Note that the SQL Tuning Advisor may also find a completely new tuned plan, one that is not in the plan history. If you then accept the recommended SQL profile, the SQL Tuning Advisor will create a SQL profile and also add the tuned plan to the SQL plan baseline.
A SQL statement can also have both a SQL Patch and a SQL plan baseline.
SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline. If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright.
On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc.
Two parameters allow you to control SPM. The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.
My Note
SQL Patch and/or SQL Profile can exist with SQL Baseline. They are different objects used for different things.
SQL run -> SQL Patch and/or Profile exist, so it is applied -> New plan is generated -> Baseline is working, so the plan is compared with the one inside the "Plan Baseline" -> If the plan exists and is accepted, it will be selected and the original plan will not run, otherwise it will be added to the plan history and the original will run (not optimized. Well in this case I tell that the orignial plan was not optimized).
Please note that SQL Patch and SQL Profile could exist together. My guess is that SQL Patch is applied before of SQL Profile.
So the order should be something like this
SQL run -> SQL Patch exist, so it is applied -> Profile exist, so it is applied -> new plan is generated -> Baseline is working, so the plan is compared with the one inside the "Plan Baseline" -> If the plan exists and is accepted, it will be selected and the original plan will not run, otherwise it will be added to the plan history and the original will run (not optimized. Well in this case I tell that the orignial plan was not optimized).
This picture shows what I said
Reference
SQL Plan Management (Part 1 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 2 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 3 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 4 of 4) Creating SQL plan baselines (Maria Colgan)
How do adaptive cursor sharing and SQL Plan Management interact? (Unknown)
Why are there more cursors in 11g for my query containing bind variables? (Unknown)
Oracle Optimizer and SPM plan interaction (Mohamed Houri)
Stage and Fix SQL Statement (Unknown)
dbms_sqldiag (Jonathan Lewis)
SQL Patch II (Dominic Brooks)
SQL Patch III (Dominic Brooks)
SQL Patch IV (Dominic Brooks)
Adding and Disabling Hints Using SQL Patch (Nigel Bayliss)
Using SQL Patch to add hints to a packaged application (Unknown)
Adaptive Cursors and SQL Plan Management (Arup Nanda)
How a Cursor becomes Bind Aware? (Carlo Sierra)
Adaptive Cursor Sharing with SQL Plan Baselines (Dominic Brooks)
Nessun commento:
Posta un commento