Which two types of SQL statements will benefit from dynamic sampling?

Question

Options

• ASQL statements that are executed parallel
• BSQL statement that use a complex predicate expression when extended statistics are not available.
• CSQL statements that are resource-intensive and have the current statistics
• DSQL statements with highly selective filters on column that has missing index statistics
• EShort-running SQL statements

• A
  7% (3)
• B
  83% (34)
• C
  7% (3)
• E
  2% (1)

Explanation

B: One scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions. D: DS It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. * Dynamic sampling (DS) was introduced in Oracle Database 9i Release 2 to improve the optimizer's ability to generate good execution plans. * During the compilation of a SQL statement, the optimizer decides whether to use DS or not by considering whether the available statistics are sufficient to generate a good execution plan. If the available statistics are not enough, dynamic sampling will be used. It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. For the case where one or more of the tables in the query does not have statistics, DS is used by the optimizer to gather basic statistics on these tables before optimizing the statement. The statistics gathered in this case are not as high a quality or as complete as the statistics gathered using the DBMS_STATS package. This trade off is made to limit the impact on the compile time of the statement.

No community discussion yet for this question.