Oracle Database 12c: Installation and Administration — Question 218

Which three statements are true about SQL plan directives? (Choose three.)

Answer options

• A. They are tied to a specific statement or SQL ID.
• B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.
• C. They are used to gather only missing statistics.
• D. They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.
• E. They instruct the optimizer to create only column group statistics.
• F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.

Correct answer: B, D, F

Explanation

The correct answers are B, D, and F because they accurately reflect the functions of SQL plan directives in optimizing query performance. Option A is incorrect as it lacks the broader context of their function, C is wrong because they do more than just gather missing statistics, and E is incorrect as they do not solely focus on column group statistics.