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

Question

Options

• AThey are tied to a specific statement or SQL ID.
• BThey instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate
• CThey are used to gather only missing statistics.
• DThey are created for a query expression where statistics are missing or the cardinality estimates by the
• EThey instruct the optimizer to create only column group statistics.
• FImprove plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.

Explanation

SQL plan directives are expression-level optimizer feedback objects that persist cardinality misestimate data in SYSAUX and can trigger both extended statistics collection and dynamic sampling to correct future plans.

Common mistakes.

• A. SQL plan directives are associated with query expressions (table-column predicate combinations), not specific SQL IDs or statement hashes, so any query using the same expression benefits from the directive regardless of its SQL ID.
• C. SQL plan directives are not limited to gathering missing statistics - they can also direct the optimizer to use dynamic sampling when statistics collection alone is insufficient to resolve the cardinality misestimate.
• E. SQL plan directives can instruct the optimizer to use dynamic sampling in addition to creating column group statistics; restricting their effect to only column group statistics is incorrect.

Concept tested. SQL plan directive creation, scope, and persistence

Reference. https://docs.oracle.com/database/121/TGSQL/tgsql_abt.htm

No community discussion yet for this question.