Oracle Database 12c: Performance Management and Tuning — Question 3

In which three situations can dynamic statistics be used? (Choose three.)

Answer options

• A. when the sampling time is a small fraction of the total time for a query
• B. when an execution plan is suboptimal because of complex predicates
• C. when extended statistics are not available for SQL statements that use complex predicates
• D. when a query is on a partitioned table with a global index
• E. when index statistics are missing on a column that is used in SQL statements with highly selective filters

Correct answer: B, C, D

Explanation

Dynamic statistics are particularly useful in situations where standard statistics are insufficient, such as when execution plans are not optimal due to complex predicates (B), when extended statistics are not present for such queries (C), and when querying partitioned tables that utilize global indexes (D). Options A and E do not directly relate to the specific scenarios where dynamic statistics would be necessary.