1Z0-060 Question #224: Real Exam Question with Answer & Explanation

The correct answer is C: It chose different plans because automatic dynamic sampling was enabled.. Automatic dynamic sampling can cause the optimizer to select a different join method at execution time than what EXPLAIN PLAN predicted, because real-time statistics collected during hard parse were not available during static plan generation.

Question

Examine the parameters for your database instance: You generate an execution plan for this query and the plan table shows a nested loop join in the plan/ After query execution, you see that a hash join was performed: What explain this optimizer behavior?

Exhibits

Options

AIt used SQL plan baselines for the query.
BIt chose different plans because extended statistics were created on the filter columns.
CIt chose different plans because automatic dynamic sampling was enabled.
DIt used an adaptive execution plan.

Explanation

Automatic dynamic sampling can cause the optimizer to select a different join method at execution time than what EXPLAIN PLAN predicted, because real-time statistics collected during hard parse were not available during static plan generation.

Common mistakes.

A. SQL plan baselines cause the optimizer to reproduce a previously captured and accepted plan, which would enforce consistent plan selection rather than explain a divergence between EXPLAIN PLAN output and actual execution.
B. Extended statistics improve cardinality estimates for correlated or expression-based predicates during plan compilation, but they are applied equally to both EXPLAIN PLAN and actual execution and do not cause plan divergence between the two.
D. Adaptive execution plans allow runtime switching between nested loops and hash joins based on row count feedback, but the parameter settings shown indicate adaptive plans are not enabled, making automatic dynamic sampling the correct explanation for the observed behavior.

Concept tested. Automatic dynamic sampling causing plan divergence at runtime

Reference. https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/influencing-the-optimizer.html

Community Discussion

No community discussion yet for this question.

Full 1Z0-060 Practice