1Z0-060 · Question #119
Which three statements are true about SQL plan directives?
The correct answer is B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate D. They are created for a query expression where statistics are missing or the cardinality estimates by the F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace. 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.
Question
Which three statements are true about SQL plan directives?
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.
How the community answered
(35 responses)- A9% (3)
- B83% (29)
- C3% (1)
- E6% (2)
Why each option
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.
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.
SQL plan directives instruct the automatic maintenance job (via DBMS_STATS) to collect missing extended statistics such as column groups, and can also trigger dynamic sampling at an elevated level, providing two complementary mechanisms to address cardinality misestimates.
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.
A SQL plan directive is created when the optimizer detects a significant gap between estimated and actual cardinality for a specific query expression (a combination of table and column predicates), signaling that current statistics are insufficient for accurate plan generation.
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.
SQL plan directives are stored persistently in the SYSAUX tablespace and capture both compile-time estimated cardinality and actual execution cardinality, allowing the optimizer to leverage real runtime feedback to improve plan accuracy in subsequent executions.
Concept tested: SQL plan directive creation, scope, and persistence
Source: https://docs.oracle.com/database/121/TGSQL/tgsql_abt.htm
Topics
Community Discussion
No community discussion yet for this question.