Which two are true about the Oracle Optimizer?

Question

Options

• AIt requires system statistics when generating SQL execution plans.
• BIt always generates an index access operation when a statement filters on an indexed column
• CIt ignores stale object statistics in the Data Dictionary.
• DIt can automatically re-optimize execution plans that were detected to be sub-optimal when
• EIt can re-write a statement internally in order to generate a more optimal plan.

• A
  16% (5)
• B
  3% (1)
• C
  6% (2)
• D
  75% (24)

Explanation

D and E are correct because Oracle's Cost-Based Optimizer (CBO) has two powerful capabilities often overlooked: adaptive query optimization (D) allows Oracle to detect sub-optimal plans at runtime and reoptimize on subsequent executions using mechanisms like cardinality feedback and statistics feedback; and query transformation (E) allows the optimizer to internally rewrite SQL - through techniques like subquery unnesting, view merging, and predicate pushing - before generating the execution plan.

A is wrong because system statistics (CPU speed, I/O speed, etc.) are optional. If not gathered, the optimizer falls back to default values and still produces a plan.

B is wrong because the optimizer never guarantees index usage - it performs a cost comparison between index access and full table scan and chooses whichever is cheaper based on selectivity, table size, and statistics.

C is wrong because the optimizer does not ignore stale statistics - it uses whatever is in the Data Dictionary, stale or not; you must manually re-gather or lock statistics to influence this behavior.

Memory tip: Remember "DR E" - the optimizer is a Doctor that Re-optimizes bad plans, and an Editor that rewrites your SQL. Everything else (index choice, stats requirements) is cost-based and optional, not guaranteed.

No community discussion yet for this question.