Which statement is true about an SQL plan baselines that are fixed?
A.
New plans are added automatically by the optimizer to the baseline and are automatically
evolved.
B.
New, better plans are added automatically as a fixed plan baseline.
C.
New plan can be manually loaded to the baseline from the cursor cache or a SQL tuning set.
D.
New plans can be added as fixed plans to the baseline by using the SQL Tuning Advisor to
generate a SQL profile and by accepting the SQL profile.
Explanation:
When a SQL statement with a fixed SQL plan baseline is tuned using the SQLTuning Advisor, a SQL profile recommendation has special meaning. When the SQL profile is
accepted, the tuned plan is added to the fixed SQL plan baseline as a non-fixed plan. However, as
described above, the optimizer will not use the tuned plan as long as a reproducible fixed plan is
present. Therefore, the benefit of SQL tuning may not be realized. To enable the use of the tuned
plan, manually alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.
Note:
It is also possible to influence the optimizer’s choice of plan when it is selecting a plan from a
SQL plan baseline. SQL plan baselines can be marked as fixed. 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.
Reference: Oracle Database Performance Tuning Guide 11g, Using Fixed SQL Plan Baselines
Reference: SQL Plan Management in Oracle Database 11g
C correct.
However, a fixed SQL plan baseline can be evolved by manually loading new plans into it from the cursor cache or a SQL tuning set.
http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BABICJBG
yes, C should be the correct answer