You are administering a database that supports a mixed workload. You upgradeyour
databasefrom Oracle Database 11gto 12cand after the upgrade, users complain about
degraded performance of some queries. The SQL plan baselines imported from the previous
version are present for the queries and are loaded to the SQL Management Base as accepted
plans. On further investigation, you find that betterplansare generated but not used by the
optimizer. Examine the parameters set for the instance:
Which three tasks would you perform to improve the performance of these queries?(Choose
three.)
A.
Gather statistics for the objects used in the queries.
B.
Use theDBMS_SPM.EVOLVE_SQL_PLAN_BASELINEfunction to evolve new plans
and fix the plans for the statements.
C.
Create an SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate
recommendations.
D.
Create an STS and run it through the SQL Tuning Advisor to generate recommendations.
E.
Set theOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESparameter toTRUE.
F.
Use theDBMS_SPM.ALTER_SQL_PLAN_BASELINEfunction to alter the accepted
plans as fixed plans.
imo
B,D,E – right
B,D because these are the ways to evolve better plans
E because we have to capture better plans so as to automatically evolve them during maintenance window
B,D,E
I don’t agree with E. In the question it says:
“you find that better plans are generated but not used by the optimizer” so you don’t have to try to find out new, better plans, there are already there, you just have to implement them, so I would go for B,D and F
BDE
Not F because why should we fix to only new plans ??tohers one from the baeline may get good one day…