You run the SQL Tuning Advisor (STA) to tune a SQL statement that is part of a fixed SQL
plan baseline. The STA generates a SQL profile for the SQL statement, which recommends
that you accept the profile.
Which statement is true when you accept the suggested SQL profile?
A.
The tuned plan is not added to the SQL plan baseline.
B.
The tuned plan is added to the fixed SQL plan baseline as a fixed plan.
C.
The tuned plan is added to the fixed SQL plan baseline as a nonfixed plan.
D.
The tuned plan is added to a new nonfixed SQL plan baseline as a nonfixed plan.
Explanation:
15.4 Using Fixed SQL Plan Baselines (Refer to here)
When you tune a SQL statement with a fixed SQL plan baseline using SQL Tuning Advisor,
a SQL profile recommendation has special meaning. When the SQL profile is accepted, the
database adds the tuned plan to the fixed SQL plan baseline as a non-fixed plan. However,
as described above, the optimizer does not use the tuned plan when 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.
C is correct:
http://docs.oracle.com/cd/E25178_01/server.1111/e16638/optplanmgmt.htm
Decide whether the stored outlines migrated to SQL plan baselines should use fixed plans or nonfixed plans:
Fixed plans
A fixed plan is frozen. If a fixed plan is reproducible using the hints stored in plan baseline, then the optimizer always chooses the lowest-cost fixed plan baseline over plan baselines that are not fixed. Essentially, a fixed plan baseline acts as a stored outline with valid hints.
A fixed plan is reproducible when the database can parse the statement based on the hints stored in the plan baseline and create a plan with the same plan hash value as the one in the plan baseline. If one of more of the hints become invalid, then the database may not be able to create a plan with the same plan hash value. In this case, the plan is nonreproducible.
If a fixed plan cannot be reproduced when parsed using its hints, then the optimizer chooses a different plan, which can be either of the following:
Another plan for the SQL plan baseline
The current cost-based plan created by the optimizer
In some cases, a performance regression occurs because of the different plan, requiring SQL tuning.
Nonfixed plans
If a plan baseline does not contain fixed plans, then SQL Plan Management considers the plans equally when picking a plan for a SQL statement.