Examine the parameters for your database instance: Which three statements are true about the process of automatic optimization by using cardinality feedback? (Choose three.)
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.
Suggested Answer:ACD🗳️
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.
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.
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.
D is correct: cardinality feedback is called Statistics feedback in 12c. "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."
Reference: https://blogs.oracle.com/optimizer/post/statistics-feedback-formerly-cardinality-feedback
I Think A C E.
CF(cardinality feedback) is helping Dynamic sampling. In order to disable CF. You need to set _optimizer_use_feedback" to false at a session or statement level.
So given answer D is wrong. E is correct.
upvoted 1 times
...
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
luizyto
2 years, 1 month agoluizyto
2 years, 1 month agoRay520
2 years, 10 months agoJMAN1
3 years agoJMAN1
3 years ago