nerdexam
Oracle

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.

Managing Performance

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)
  • A
    95% (54)
  • B
    2% (1)
  • E
    4% (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.

Athe optimizer_mode initialization parameterCorrect

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.

Boperating system (OS) statistics

The Oracle optimizer relies on database-level object statistics, not operating system statistics, when calculating and comparing execution plan costs.

Ccardinality estimatesCorrect

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.

Dobject statistics in the data dictionaryCorrect

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.

Efixed baselines

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

#optimizer#execution plan#cardinality#object statistics

Community Discussion

No community discussion yet for this question.

Full 1Z0-062 Practice