Examine the parameters for your database instance: 
Which three statements are true about the process of automatic optimization by using cardinality
feedback?
A.
The optimizer automatically changes a plan during subsequent execution of a SQL statement if
there is a huge difference in optimizer estimates and execution statistics.
B.
The optimizer can re optimize a query only once using cardinality feedback.
C.
The optimizer enables monitoring for cardinality feedback after the first execution of a query.
D.
The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn
statistics are enabled.
E.
After the optimizer identifies a query as a re-optimization candidate, statistics collected by the
collectors are submitted to the optimizer.
Explanation:
C: During the first execution of a SQL statement, an execution plan is generated as
usual.
D: if multi-column statistics are not present for the relevant combination of columns, the optimizer
can fall back on cardinality feedback.
(not B)* Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve plansfor repeated executions.
optimizer_dynamic_sampling
optimizer_features_enable
* dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate
selectivity of conjunctive predicates.
Note:
* OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the
optimizer.
Range of values. 0 to 10
* Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to
automatically improve plans for queries that are executed repeatedly, for which the optimizer does
not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a
variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the
reason for the misestimate, cardinality feedback may be able to help.
A,B,E is right, see course documentation.
C is wrong because During query execution, Optimizer estimates are compared with query execution statistics.
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.
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
D is true.
ABE are the correct answers. Course documentation speaks of option E clearly. A is correct of course. and B is also correct since cardinality feedback and not (reoptimization) feature since 11g will once check the sql once.
Hi Amar: Why E? please specify which document or give URL here. Thx
ADE since dynamic sampling is enabled cardinality feedback will not be used at all
From the 12c WHITE PAPER: “In Oracle Database 12c dynamic sampling has been enhanced to become dynamic statistics.
Dynamic statistics allow the optimizer to augment existing statistics to get more accurate cardinality estimates for not only single table accesses
but also joins and group-by predicates.”
So Cardinality feedback (statistic feedback) is used in conjunction with dynamic sampling and it has LEVEL 11.
I think the explaination is out of date.
However, the diagram has shown the sampling is NOT at LEVEL 11.
I will pick ACE.
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
D is correct.
C is correct
http://oracleinaction.com/12c-optimizer_dynamic_sampling-11/
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.
D is correct
http://oracleinaction.com/12c-optimizer_dynamic_sampling-11/
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.
C is Incorrect:
http://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL94984
The basic process of reoptimization using statistics feedback is as follows:
1. During the first execution of a SQL statement, the optimizer generates an execution plan.
2. After the first execution, the optimizer disables monitoring for statistics feedback.
3. If the query executes again, then the optimizer uses the corrected cardinality estimates instead of its usual estimates.
ADE
According to:
https://blogs.oracle.com/optimizer/cardinality-feedback
I will select ADE.
So much confusing here
B is wrong – “The cardinalities that can be observed during an execution depend on the shape of a plan. So it is possible that on the second execution of a query, after generating a new plan using cardinality feedback, there are still more cardinality estimates that are found to deviate significantly from the actual cardinalities. In this case, we can reoptimize yet again on the next execution. However, we have safeguards in place to guarantee that this will stabilize after a small number of executions. So you may see your plan changing in the first few executions, but then we eventually pick one and stick with it on all subsequent executions.”
C is right – During the first execution of a SQL statement, an execution plan is generated as usual. 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.
D is right : 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.
E is also somewhat right – 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.
And A is already right ….
I think I should go with ACD .
ACD