Examine the parameters for your database instance:
NAMETYPEVALUE
—————————————————————————————————–
optimizer_adaptive_reporting_onlybooleanFALSE
optimizer_capture_sql_plan_baselinesbooleanFALSE
optimizer_dynamic_samplinginteger2
optimizer_features_enablestring12.1.0.1
Which three statements are true about the process of automatic optimization by using
statistics 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
plans for 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 C D?
ABD (although B may be wrong)
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
A: “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.”
B: Conflicting statements in document. B should be opposite of C, right?
“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.”
Not C: “The monitoring for cardinality feedback is disabled after the first execution.”
D: “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.”
Not E: This is a feature of Automatic Reoptimization.
Why does this question have answers that aren’t in the Oracle Docs or in the Student Guide (i.e. B & D)?
Steve, I think is A C D.
C: During the first execution of a SQL statement, an execution plan is generated as usual.
So it is activated after first execution.
Do you agree?
Statistics Feedback (Cardinality Feedback)
Cardinality feedback was introduced in Oracle Database 11g Release 2. When the optimizer generates an execution plan the presence of missing statistics, stale statistics, complex predicates or complex operators may trigger the optimizer to monitor the cardinality of operations in the plan. Once the execution is complete, if there is a significant difference between the estimated and actual cardinalities, the actual cardinalities are stored in the SGA for later use and the statement is marked as reoptimizable. On next execution the statement is reoptimized using the stored cardinalities, allowing a better plan to be determined. Cardinality feedback is statement specific and is lost if the instance is restarted or the statement is aged out of the shared pool. In Oracle Database 12c, cardinality feedback has been renamed to statistics feedback.
I have no idea on this. It’s complicated :d
ACD