The query optimizer normally uses information like
object and system statistics, compilation environment, bind values and so on to
determine the best plan for a SQL statement. In some cases, defects in either
these inputs or the optimizer can lead to a sub-optimal plan. A SQL
profile contains auxiliary information that mitigates these problems.
When used together with its regular inputs, a SQL profile helps the optimizer minimize
mistakes and thus more likely to select the best plan.
A SQL plan baseline for a SQL statement consists of
a set of accepted plans. When the statement is parsed, the optimizer will only
select the best plan from among this set. If a different plan is found using
the normal cost-based selection process, the optimizer will add it to the plan
history but this plan will not be used until it is verified to perform better
than the existing accepted plan and is evolved.So, SQL profiles provide additional information to
the optimizer to help select the best plan; they don't constrain the optimizer
to any specific plan, which is why they can be shared. SQL plan baselines, on
the other hand, constrain the optimizer to only select from a set of accepted
plans. The cost-based approach is still used to choose a plan, but only within
this set of plans. SQL plan baselines are a more conservative plan selection
strategy than SQL profiles. So when should you use SQL profiles versus SQL plan
baselines?
You should use a SQL profiles if you just want to
help the optimizer a little in its costing process without constraining it to
any specific plan. This approach can be extremely useful when you want the
system to adapt immediately to changes like new object statistics. You should
use SQL plan baselines if you are more conservative and want to control which
plans are used. If you are using SQL plan baselines and find that the optimizer
sometimes does not select the best plan from the accepted list or does not find
a best-cost plan to add to the plan history, then you can always use a SQL
profile as well. What happens if a SQL statement has both a SQL Profile and a
SQL plan Baseline?
If you recall, SQL Plan Management (SPM) has three
component, plan capture, plan selection, and plan evolution. The presence of a
SQL profile affects all three components of SPM and we will describe each of
those interactions below.
SPM plan capture and SQL profiles
When the statement is executed it will be hard
parsed and a cost based plan will be generated. That plan will be influenced by
the SQL Profile. Once the cost based plan is determined it will be compared to
the plans that exist in the SQL plan baseline. If the plan matches one of the
accepted plans in the SQL plan baseline, we will go ahead and use it. However,
if the cost based plan doesn't match any of the accepted plans in the SQL plan
baseline it will be added to the plan baseline as an unaccepted plan.
SPM plan selection and SQL profiles
When a SQL statement with a SQL plan baseline is
parsed, the accepted plan with the best cost will be chosen. This process
uses the regular optimizer. The presence of a SQL profile will affect the
estimated cost of each of these plans and thus potentially the plan that is
finally selected.
SPM plan evolution and SQL profiles
The third sub-component of SPM is verification or
evolution of non-accepted plans. The evolution process test-executes the
non-accepted plan against the best of the accepted plans. The best
accepted plan is selected based on cost. Again, if a SQL profile exists,
it will influence the estimated cost and thus the accepted plan chosen for
comparison against the non-accepted plan.
Hopefully this information gives you a clear
picture of how SQL profile and SQL plan baselines differ and how they interact
with one another!
Comments
Post a Comment