1Z0-062 · Question #158
Which three factors influence the optimizer's choice of an execution plan?
The correct answer is A. the optimizer_mode initialization parameter C. cardinality estimates D. object statistics in the data dictionary. The Oracle cost-based optimizer selects execution plans based on optimizer mode, cardinality estimates, and object statistics stored in the data dictionary.
Question
Which three factors influence the optimizer's choice of an execution plan?
Options
- Athe optimizer_mode initialization parameter
- Boperating system (OS) statistics
- Ccardinality estimates
- Dobject statistics in the data dictionary
- Efixed baselines
How the community answered
(57 responses)- A95% (54)
- B2% (1)
- E4% (2)
Why each option
The Oracle cost-based optimizer selects execution plans based on optimizer mode, cardinality estimates, and object statistics stored in the data dictionary.
The optimizer_mode initialization parameter (e.g., ALL_ROWS, FIRST_ROWS_n) sets the optimization goal, directly steering the optimizer toward throughput- or response-time-oriented plans.
The Oracle optimizer relies on database-level object statistics, not operating system statistics, when calculating and comparing execution plan costs.
Cardinality estimates - the predicted row counts at each plan step - are central inputs to the cost model; inaccurate cardinality leads to suboptimal join order and access-path selection.
Object statistics in the data dictionary (table row counts, column NDV, histograms, index clustering factor) are the primary data the optimizer uses to estimate operation costs and compare candidate plans.
SQL plan baselines constrain which plans the optimizer may promote for use, but they are not a factor the optimizer weighs during the cost estimation process itself.
Concept tested: Oracle cost-based optimizer execution plan selection factors
Source: https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm
Topics
Community Discussion
No community discussion yet for this question.