Oracle Database 12c: Advanced Administration — Question 154

Examine the parameters for your database instance:

NAMETYPEVALUE -
------------------------------------------------
optimizer_adaptive_reporting_onlybooleanFALSE
optimizer_capture_sql_plan_baselinesbooleanFALSE
optimizer_dynamic_samplinginteger2
optimizer_features_enablestring12.1.0.1
Which three statements are true about the process of automatic optimization by using statistics feedback?

Answer options

• A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
• B. The optimizer can re optimize a query only once using cardinality feedback.
• C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
• D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
• E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.

Correct answer: A, C, D

Explanation

Option A is correct because the optimizer does adjust plans based on discrepancies between estimates and actual execution. Option C is true as monitoring for cardinality feedback begins after the first query execution. Option D is accurate since the presence of dynamic sampling and multicolumn statistics prevents the optimizer from monitoring cardinality feedback. Options B and E are incorrect as they misrepresent the capabilities of the optimizer regarding re-optimization and the handling of statistics.