Home » Oracle » 1z0-117 » Which statement is true about an SQL plan baselines that are fixed?
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.
Correct Answer: D
Explanation/Reference:
When a SQL statement with a fixed SQL plan baseline is tuned using the SQL Tuning 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: OracleDatabase Performance Tuning Guide11g,Using Fixed SQL Plan Baselines
Reference:SQL Plan Management in Oracle Database 11g
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
