Examine the parameter for your database instance:
You generated the execution plan for the following query in the plan table and noticed that the
nested loop join was done. After actual execution of the query, you notice that the hash join was
done in the execution plan:
Identify the reason why the optimizer chose different execution plans.
A.
The optimizer used a dynamic plan for the query.
B.
The optimizer chose different plans because automatic dynamic sampling was enabled.
C.
The optimizer used re-optimization cardinality feedback for the query.
D.
The optimizer chose different plan because extended statistics were created for the columns
used.
Explanation:
* optimizer_dynamic_sampling
OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics,
and the size of the sample that the optimizer uses to gather the statistics.
Range of values0 to 11
B
B
A
A.
Not B because dynamic sampling is used when query is explained too:
https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer
Possibly C. The example is almost identical to question:
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
There was not any execution before it, but explain plan was generated. Re-optimalization occurs after the first execution. I think that A is correct option.
It is not said if there was any execution before, would be possible: first execution + explain + second execution. The question is not clear, could be answer A or C, but the example in blog is sql identical to the question, and the result (nested join -> hash join) too.
It mentions “In some cases, there are other techniques available to improve estimation; for instance, dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. In cases where these techniques apply, cardinality feedback is not enabled.” In this question, it enables dynamic sampling, so maybe not C?
Adaptive Plans help decide between multiple plans. This is the feature being used here… The statistics collector will buffer some rows in order to estimate the cardinality. This will allow the optimizer to make the decision between a NL or HASH JOIN.
On the other hand, there is also the reoptimization feature. Reoptimization is intended to make changes that Adaptive Plans cannot. For example, Join Orders…
In contrast to adaptive plans, automatic reoptimization changes a plan on subsequent executions after the initial execution.
Domingo – RIGHT
C – OK
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
Not C.
Cardinality feedback monitoring may be enabled in the following cases: tables with no statistics, multiple conjunctive or disjunctive filter predicates on a table, and predicates containing complex operators that the optimizer cannot accurately compute selectivity estimates for. In some cases, there are other techniques available to improve estimation; for instance, dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. In cases where these techniques apply, cardinality feedback is not enabled. However, if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback.
B
C
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
Cardinality Feedback
During the first execution of a SQL statement, an execution plan is generated as usual. During optimization, certain types of estimates that are known to be of low quality (for example, estimates for tables which lack statistics or tables with complex predicates) are noted, and monitoring is enabled for the cursor that is produced. If cardinality feedback monitoring is enabled for a cursor, then at the end of execution, some of the cardinality estimates in the plan are compared to the actual cardinalities seen during execution. If some of these estimates are found to differ significantly from the actual cardinalities, the correct estimates are stored for later use. The next time the query is executed, it will be optimized again, and this time the optimizer uses the corrected estimates in place of its usual estimates.
The example give here in the article, is exactly what is asked in the question. Hence, I would go with the choice: C
C
In some cases, there are other techniques available to improve estimation; for instance, dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. In cases where these techniques apply, cardinality feedback is not enabled.
Sorry B In cases where these techniques apply, cardinality feedback is not enabled.
B is incorrect, because the optimizer will not use automatic dynamic sampling if OPTIMIZER_DYNAMIC_SAMPLING=2, at least it should be 4.
Not B, explain also uses dynamic sampling,
A or C
I’ll pick A
Automatic dynamic sampling is enabled when the OPTIMIZER_DYNAMIC_SAMPLING =11. (not B)
Adaptive Execution plan (Dynamic plans, Reoptimization) is enabled when OPTIMIZER_FEATURES_ENABLED = 12.1.0.1 and OPTIMIZER_ADAPTIVE_REPORTING_ONLY = FALSE.
Answer: A.
My choice – C
The answer is ‘C’. ‘B’ is wrong as dynamic_sampling would affect the execution plan before execution the sql not during the execution.
A – From my exam preparation book: The optimizer can automatically adapt poor execution plans at run time,… the database WILL use the adaptive plan mechanism when both of the following are true:
– the initialization parameter optimizer_features_enable is set to 12.1.0.1 or later
– the optimizer_adaptive_reporting_only parameter is set to false (default value for this parameter)
A
C
B- is not correct . 12c introduced dynamic_sampling level 11. When set , it uses the Dynamic sampling.
See very nice document.
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
automatic reoptimization changes a plan on subsequent executions after the initial execution.
so c may not correct.
There is no previous execution, only EXPLAIN PLAN. So, there is no Cardinality Feedback in place. As Bill said, automatic dynamic sampling would be enabled with 11. So, B and C are OUT.
There is no evidence of some extended statistics could have gathered. But, if yes, it could be the cause. So, D is feasible.
But, for me the answer is A. Dynamic plan appears to be more feasible for this situation.
A
https://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm
13.5.7 Estimating Statistics with Dynamic Sampling
The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans.
B is the correct answer.
This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter. For dynamic sampling to automatically gather the necessary statistics, this parameter should be set to a value of 2 or higher. The default value is 2. See “Dynamic Sampling Levels” to learn about the sampling levels that can be set.
the adaptive query optimization capability changes plans based on statistics collected during statement execution. All adaptive mechanisms can execute a final plan for a statement that differs from the default plan. Adaptive optimization uses either dynamic plans, which choose among subplans during statement execution, or reoptimization, which changes a plan on executions after the current execution.
A
http://oracleelogs.blogspot.com.es/2013_09_01_archive.html
A dynamic plan.
it can choose between hash join and nested loop join based on number of returning rows.
A
A is correct.
Refer : http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
There are two distinct aspects in Adaptive Query Optimization, adaptive plans, which focuses on improving the initial execution of a query and adaptive statistics, which provide additional information to improve subsequent executions.
Not B .Dynamic sampling comes in subsequent executions. Question focus on initial execution.
Of course A – dynamic plan
according to the document
https://oracle-base.com/articles/12c/adaptive-plans-12cr1
A is more close.
Answer should be A, as it is dynamic plan which changes join method in the very first execution itself.