Oracle Notebook
Performance & Tuning · 4 min read

Understanding Oracle's Cost-Based Optimizer

How Oracle's CBO chooses execution plans, why it sometimes gets it wrong, and the practical levers — statistics, histograms, hints, baselines — for fixing bad plans.

Every SQL query in Oracle goes through the Cost-Based Optimizer (CBO) before it runs. The CBO decides which indexes to use, how tables get joined, and what order the operations happen in. When queries are slow, the optimizer’s decisions are usually the first thing worth understanding.

This post is a developer-focused introduction to how the CBO works, what it relies on, and what you can do when it goes wrong.

What the CBO actually does

For any SQL statement, there are usually many ways to execute it. Different join orders, different access paths (full scan vs index range scan vs unique scan), different join methods (nested loops vs hash vs sort merge). The CBO enumerates plausible plans, estimates the cost of each one, and picks the cheapest.

“Cost” is an abstract number that combines estimates of I/O, CPU, and (sometimes) network. Lower is preferred. The CBO doesn’t actually run the alternatives — it estimates them based on statistics.

Statistics are the foundation

The CBO’s decisions are only as good as its statistics. The ones that matter most:

  • Table statistics — row count, average row length, block count
  • Column statistics — number of distinct values, nulls, high/low values, histograms
  • Index statistics — depth, leaf blocks, clustering factor
  • System statistics — I/O and CPU characteristics of the database server

When statistics are stale or missing, the CBO makes poor decisions. The classic symptom: a query that ran fast for months suddenly runs slowly after a bulk load that wasn’t followed by stats gathering.

Refresh statistics with:

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');

Or for the whole schema:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);

In modern Oracle, automatic stats gathering runs nightly. But ad-hoc loads and unusual data patterns still benefit from manual gathering right after.

Cardinality estimates and the truth

The CBO estimates how many rows each operation will produce. These estimates drive the cost calculation. When estimates are wrong, plans are wrong.

The most common reasons for bad estimates:

  • Skew without a histogram. If a column has highly skewed values (most rows have one value, a few have many others), basic stats average across the skew. A histogram captures the distribution.
  • Correlated predicates. Oracle assumes predicates are independent. If you filter on country = 'US' AND zip_code LIKE '9%', the CBO multiplies the selectivities. In reality the predicates are correlated. Extended statistics help.
  • Function-wrapped columns. WHERE UPPER(name) = 'ACME' hides the column from index use and stats. Use function-based indexes or store normalized data.

Reading execution plans

EXPLAIN PLAN FOR
  SELECT * FROM orders WHERE customer_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

The output shows the chosen plan: which indexes used, join order, estimated rows per step.

For a query that’s already run, DBMS_XPLAN.DISPLAY_CURSOR shows the actual plan with actual rows per step. Comparing E-Rows (estimated) with A-Rows (actual) reveals where estimates go wrong:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

If E-Rows and A-Rows differ by orders of magnitude, that’s where to focus your tuning effort.

When the CBO gets it wrong

Three common cases:

  1. Stats are stale or missing. Gather them.
  2. Distribution is skewed and no histogram exists. Add a histogram with METHOD_OPT => 'FOR COLUMNS column_name SIZE AUTO'.
  3. Correlation isn’t captured. Create extended statistics on related columns.

When those don’t fix the plan:

  • Hints force the CBO’s hand: /*+ INDEX(orders idx_customer) */. Use sparingly — they age badly as data changes.
  • SQL Plan Baselines lock a known-good plan. Better than hints for long-term stability.
  • Adaptive features in 12c+ let the CBO change plans at runtime based on actual cardinality observed during execution.

A few good habits

  • Gather stats after large data loads. Don’t wait for the nightly job.
  • Use bind variables, not literals — repeated parsing is expensive, and bind peeking gives the CBO real selectivity.
  • Avoid functions on indexed columns in WHERE clauses. If you need them, build function-based indexes.
  • Watch A-Rows vs E-Rows when tuning. Cardinality drift is the single most common reason for bad plans.
  • Read execution plans before applying hints. Hints without understanding usually make things worse.

The honest summary

The CBO is not magic. Most performance problems in real codebases come down to bad stats, missing indexes, or queries written in a way that hides information from the optimizer. Understanding the CBO well enough to diagnose those is the highest-leverage skill in Oracle performance work.

If you only learn one debugging technique, learn to read DBMS_XPLAN.DISPLAY_CURSOR output with ALLSTATS LAST. Most of the rest follows from there.