Which three statements are true about SQL plan directives?

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
• CThey are used to gather only missing statistics.
• DThey are created for a query expression where statistics are missing or the cardinality estimates
• EThey instruct the optimizer to create only column group statistics.
• FImprove plan accuracy by persisting both compilation and execution statistics in the SYSAUX

• A
  3% (1)
• B
  87% (27)
• C
  6% (2)
• F
  3% (1)

Explanation

During SQL execution, if a cardinality misestimate occurs, then the database creates SQL plan directives. During SQL compilation, the optimizer examines the query corresponding to the directive to determine whether missing extensions or histograms exist (D). The optimizer records any missing extensions. Subsequent DBMS_STATS calls collect statistics for the extensions. The optimizer uses dynamic sampling whenever it does not have sufficient statistics corresponding to the directive. (B, not C) E: Currently, the optimizer monitors only column groups. The optimizer does not create an extension on expressions. Not A: SQL plan directives are not tied to a specific SQL statement or SQL ID. * A SQL plan directive is additional information and instructions that the optimizer can use to generate a more optimal plan. For example, a SQL plan directive can instruct the optimizer to record a missing extension.

No community discussion yet for this question.