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 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 cardinalitiesfor 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.
SQL> ALTER DISKGROUP DATASLAVE ADD DISK ‘/dev/ASMDISK53’ NAME DATASLAVE_0023 SIZE 207157 M REBALANCE POWER 11;
Correct answer is A,C
Because Rebalance power can be set in add disk command
wrong page ?
A,C,D
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
ACD
This goes hand-in-hand with the “click through rate. A duplicable ACN marketing plan is the most important thing you need as you are trying to build a team of distributors. One of their most popular services, You – Tube, will apparently see a change in the manner in which advertisements are displayed to viewers of the site’s videos.
Compensation under this heading can be recovered for damaged clothing due to the injury, travel expenses, prescription charges, etc.
Temporarily blue skin seems a small price to pay for being able to walk again. They
make use of spinal adjustments not only for rehabilitation, but to
acquire a competitive edge.
What about ACE ?
E… is definitly TRUE
not D… Dynamic sampling turns off cardinality feedback , but what about tables, where statistics are not missing and dynamic sampling is not used.
ACD
I think that E is no correct because collectors( statistics collectors) are used to change a plan “on the fly” in adaptive plans. Statistics collectors are not mentioned in cardinality feedback (statistics feedback in 12c) section:
http://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL94982
ACE is correct answer
ACD
ACD because 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.
ACD is final answer!
ACE
d:
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
..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 C:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
The monitoring for cardinality feedback is disabled after the first execution.
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
says the same
A: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
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.
b: since monitoring is off, re optimization occurs once.
ABD
The database uses adaptive execution plans when
OPTIMIZER_FEATURES_ENABLE is set to 12.1.0.1 or
later, and OPTIMIZER_ADAPTIVE_REPORTING_ONLY
initialization parameter is set to the default of FALSE.
OPTIMIZER_ADAPTIVE_REPORTING_ONLY controls reporting-only mode for adaptive
optimizations. When set to TRUE, adaptive optimizations run in reporting-only mode where the information required for an adaptive optimization is gathered, but no action is taken to change the plan.
Cardinality feedback was introduced in Oracle Database 11g, Release 2. This feature does a very limited form of re-optimization, after the first execution is completed. It automatically improves plans for queries for which the optimizer estimate improper cardinalities in the plan. Hence, C is correct.
Cardinality monitoring feedback works as follows:
During query execution, optimizer estimates are compared to execution statistics. If they vary significantly, a new plan is chosen for subsequent executions. So, Choice A is correct.
Choice B is incorrect. Statements are continually monitored to see if statistics
fluctuate over different executions. A query may be re-optimized several times, each time creating a larger and more precise set of optimizer adjustments.
Statistics are gathered for the actual data volume and data type seen during execution. If the original optimizer estimates vary significantly, the statement is hard parsed during the next execution by using the execution statistics.
After the optimizer identifies a query as a re-optimization candidate, the database submits all collected statistics to the optimizer. Hence, E is correct.
Final Answers: A, C, E
ACD.
E is correct for “Automatic Re-optimization”, which is new for 12c.
“Cardinality feedback” exist in 11g R2.
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)?
First OPTIMIZER_DYNAMIC_SAMPLING should be 11 in the picture, because if it’s set to 2 it is only used with MISSING statistics…
And then:
ADE
from my exam preparation book “automatic reoptimization” under the topic “automatic reorganization” and “statistics feedback”:
A: “The optimizer considers automatic reoptimization, where optimizer changes a plan only for subsequent executions”
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.”
E: “For statements with bind variables, the automatic reoptimization works with adaptive cursor sharing”
ACE
D is incorrect in my opinion because Adaptive Statistics → known as cardinality feedback
includes Dynamic Statistics and One type of dynamic statistics is the information gathered by dynamic sampling
From the Documentation….
The following topics describe types of adaptive statistics:
Dynamic Statistics
Automatic Reoptimization
SQL Plan Directives
Dynamic Statistics
During the compilation of a SQL statement, the optimizer decides whether to use dynamic statistics by considering whether the available statistics are sufficient to generate an optimal execution plan. If the available statistics are insufficient, then the optimizer uses dynamic statistics to augment the statistics. One type of dynamic statistics is the information gathered by dynamic sampling. The optimizer can use dynamic statistics for table scans, index access, joins, and GROUP BY operations, thus improving the quality of optimizer decisions
ACE are correct
A – plan can change during subsequent execution as 12c optimizer keeps gather information and improves further
B – Cardinality feedback/Statistics feedback enabled after first execution of the query
E – Per my Oracle training documents it clearly says that DB submits statistics to optimizer.
Not sure why D is correct though many said. I don’t find any reference saying that optimizer doesn’t monitor cardinality feedback if dynamic sampling or multi column statistics are enabled.
Only reference I can find is following…Note that Statistics feedback = Cardinality feedback
Statistics feedback is “enabled” in the following cases: tables with no statistics, multiple conjunctive or disjunctive filter predicates on a table, and predicates containing complex operators for which the optimizer cannot accurately compute cardinality estimates.
ACE
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
Not C – “After the first execution, the optimizer disables monitoring for statistics feedback.”
Not B – “The optimizer can reoptimize a query several times, each time learning more and further improving the plan. Oracle Database 12c supports multiple forms of reoptimization”
Answer: ADE.
ADE, agree with Test5678..
I want to see an Oracle doc related to “D” though
ADE !!!
A – obvious – TRUE
B – this is true for 11g, but NOT in 12c anymore: https://support.oracle.com/epmos/faces/DocContentDisplay?id=1344937.1
“Oracle is able to repeatedly re-optimize a statement using Statistics Feedback. This may be necessary since cardinality differences may depend on the structure and shape of a plan. Therefore it is possible that on the second execution of a query, after generating a new plan using Statistics Feedback, there are still more cardinality estimates that are found to deviate significantly from the actual cardinalities. In this case, Oracle can re-optimize yet again on the next execution.”
Here we have optimizer_features_enable=12.1.0.1, so B is FALSE
C – optimizer enables monitoring for cardinality feedback BEFORE (during parsing) the first execution of a query, NOT after – so C is FALSE
https://support.oracle.com/epmos/faces/DocContentDisplay?id=1344937.1
“Even when statistics are calculated as accurately as possible, an estimated cardinality may be inaccurate. On the first execution of a SQL statement an execution plan is generated. During the plan optimization, certain types of estimates are noted and the cursor that is produced is monitored.”
D – TRUE
https://support.oracle.com/epmos/faces/DocContentDisplay?id=1344937.1
“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, Statistics Feedback is not enabled. However, if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on Statistics Feedback.”
E – TRUE. This sentence is taken from Oracle Study Guide.
D is wrong
Even with multi-column statistics enabled, if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on Statistics Feedback
ACE
So many different answers.
What’s the “correct” answer in exam ?
From http://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL94850
Reoptimization: Statistics Feedback
A form of reoptimization known as statistics feedback (formerly known as cardinality feedback) automatically improves plans for repeated queries that have cardinality misestimates.
The optimizer can estimate cardinalities incorrectly for many reasons, such as missing statistics, inaccurate statistics, or complex predicates. The basic process of reoptimization using statistics feedback is as follows:
During the first execution of a SQL statement, the optimizer generates an execution plan.
The optimizer may enable monitoring for statistics feedback for the shared SQL area in the following cases:
Tables with no statistics
Multiple conjunctive or disjunctive filter predicates on a table
Predicates containing complex operators for which the optimizer cannot accurately compute selectivity estimates
At the end of execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution. If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use. The optimizer also creates a SQL plan directive so that other SQL statements can benefit from the information obtained during this initial execution.
After the first execution, the optimizer disables monitoring for statistics feedback.
If the query executes again, then the optimizer uses the corrected cardinality estimates instead of its usual estimates.
So B is correct, C incorrect.
I’ll go with ABE.
https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL94850
MOS Doc ID 1344937.1
A. True. This is what cardinality feedback is about.
B. False. Cardinality feedback occur once but same query is optimized for second and subsequent execution
C. False. Cardinality feedback occur once on first execution but afterwards is disabled
D. True. Refer MOS Doc ID 1344937.1
E. True. Statistics collected are used for subsequent query
ADE
Notice how we all read/understand the same thing yet we choose different answers with different reason. These exam question sucks!!!