nerdexam
Oracle

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.

New Features for Performance

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)
  • A
    9% (3)
  • B
    83% (29)
  • C
    3% (1)
  • E
    6% (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.

AThey are tied to a specific statement or SQL ID.

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.

BThey instruct the maintenance job to collect missing statistics or perform dynamic sampling to generateCorrect

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.

CThey are used to gather only missing statistics.

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.

DThey are created for a query expression where statistics are missing or the cardinality estimates by theCorrect

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.

EThey instruct the optimizer to create only column group statistics.

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.

FImprove plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.Correct

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

#SQL plan directives#adaptive query optimization#dynamic sampling#cardinality estimates

Community Discussion

No community discussion yet for this question.

Full 1Z0-060 Practice