Which two statements are true about the use of the DYNAMIC_SAMPLING hint in a query?
A.
It estimates selectivity better for the filters.
B.
It is always used for flashback queries that contain the AS OF clause.
C.
It cannot be used if there is a single-table predicate in the WHERE clause.
D.
It cannot be used for processing SQL statements in parallel.
E.
It can compensate for the lack of extended statistics to get accurate cardinality estimates for
—complex predicate expressions.
Explanation:
D: For parallel statements, the optimizer automatically decides whether to use dynamic sampling
and which level to use.
The decision depends on the size of the tables and the complexity of the predicates. The optimizer
expects parallel statements to be resource-intensive, so the additional overhead at compile time is
worth it to ensure the best plan. The database ignores the For serially processed SQL statements,
the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING
parameter and is not triggered automatically by the optimizer. Serial statements are typically shortrunning, so that any overhead at compile time could have a huge impact on their performance. the
value is honored.
Reference: Oracle Database Administrator’s Guide, About Oracle Database Resource Manager
Why A is false ?
D for me is not totally correct. Hint DYNAMIC SAMPLING can be used with parallel statement but will be ignore.
I think A,E are the best choices
About D: Hint will not be ignored, but sampling level is defined automatically
alter session set parallel_degree_policy=AUTO;
alter session set parallel_min_time_threshold=1;
SELECT /*+ DYNAMIC_SAMPLING(a 1) parallel(2) */ col1
FROM big_table a
where col2 like ‘%a%’;
Note
—–
– dynamic sampling used for this statement (level=2)
– Degree of Parallelism is 2 because of hint
SELECT /*+ parallel(2) */ col1
FROM big_table a
where col2 like ‘%a%’;
Note
—–
– Degree of Parallelism is 2 because of hint
C is correct
http://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#11792
So answers are C,E
A E correct
C – incorect
OPTIMIZER_DYNAMIC_SAMPLING – Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns.