1Z0-067 · Question #79
Examine the parameters for your database instance: NAMETYPEVALUE ----------------------------------------------------------------------- optimizer_adaptive_reporting_onlybooleanFALSE…
The correct answer is A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if 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. 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…
Question
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?
Options
- AThe optimizer automatically changes a plan during subsequent execution of a SQL statement if
- BThe optimizer can re optimize a query only once using cardinality feedback.
- CThe optimizer enables monitoring for cardinality feedback after the first execution of a query.
- DThe optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn
- EAfter the optimizer identifies a query as a re-optimization candidate, statistics collected by the
How the community answered
(29 responses)- A79% (23)
- B7% (2)
- E14% (4)
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. * OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the 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.
Topics
Community Discussion
No community discussion yet for this question.