Home » Oracle » 1z0-117 » Which two types of SQL statements will benefit from dynamic sampling?
Which two types of SQL statements will benefit from dynamic sampling?
A. SQL statements that are executed parallel
B. SQL statement that use a complex predicate expression when extended statistics are not available.
C. SQL statements that are resource-intensive and have the current statistics
D. SQL statements with highly selective filters on column that has missing index statistics
E. Short-running SQL statements
Correct Answer: BD
Explanation/Reference:
B:Onescenario 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: DSIt is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.
Note:
*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 thestatistics gathered using the DBMS_STATS package. This trade off is made to limit the impact on the compile time of the statement.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
