In which three situations can dynamic statistics be used?(Choose three.)
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
Explanation:
https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL341
A,B,C right
A,B,C
A,C,E
ACE
ABC
BCE
https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL348
Missing or insufficient statistics will cause dynamic statistics to be generated.
C
Extended statistics help the optimizer obtain accurate quality cardinality estimates for complex predicate expressions. The optimizer can use dynamic statistics to compensate for the lack of extended statistics or when it cannot use extended statistics, for example, for non-equality predicates.
Missing statistics
E
Insufficient Statistics
B
Hi all,
what about the following that I found in Oracle online documents (https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL344)
Really I confused
Dynamic statistics are beneficial in the following situations:
An execution plan is suboptimal because of complex predicates.
The sampling time is a small fraction of total execution time for the query.
The query executes many times so that the sampling time is amortized.
ACE
B says when “an execution plan is suboptimal because of complex predicates”.. dynamic sampling functionality does not take into account how optimal a plan is (they are used before actually coming up with an execution plan when parsing).. for this to be true it should mention that a sql plan directive is in use because of bad cardinalitiy estimates for previous runs and because of this dynamic sampling occurred ..
BCE
ABC
AB-https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL341
C-https://blogs.oracle.com/optimizer/dynamic-sampling-and-its-impact-on-the-optimizer