Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 38 ยท Performance and Administration

Oracle Execution Plans and Optimizer Statistics

Read Oracle execution plans with EXPLAIN PLAN and DBMS_XPLAN, understand full scans, index scans and join methods, and keep optimizer statistics fresh with DBMS_STATS.

SQL says what you want, not how to get it. Between your statement and the result, Oracle's cost-based optimizer chooses an execution plan: which indexes to use, in which order to join tables, and with which join method. Most slow queries are slow because the plan is poor, so the first skill of tuning is reading plans. The second is knowing what information the optimizer based its choice on: the statistics.

Oracle cannot run on this site. Oracle commands are read-only, and plan output is shown only as an illustration. The runnable examples use SQLite's EXPLAIN QUERY PLAN, a much simpler relative of Oracle's plan, as a stand-in to show how an index changes the access path.

Getting a plan

The classic way is EXPLAIN PLAN, which records the plan without running the statement, followed by DBMS_XPLAN.DISPLAY:

SQL
EXPLAIN PLAN FOR
  SELECT e.first_name, d.department_name
    FROM employees e
    JOIN departments d ON d.department_id = e.department_id
   WHERE e.salary > 8000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

A plan is a tree of operations. The output lists them with an Id, the Operation, the Name of the object, the estimated Rows (cardinality), Bytes, and the Cost. Indentation shows parent and child; Oracle executes the most deeply indented operation first, and when siblings are at the same level, it goes top to bottom.

To see the plan that really ran, with actual row counts, run the statement with the hint /*+ GATHER_PLAN_STATISTICS */ and then:

SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

Compare the estimated rows (E-Rows) with the actual rows (A-Rows). A large mismatch is the number one clue that the optimizer was misled, usually by stale or missing statistics. In SQL*Plus, SET AUTOTRACE TRACEONLY EXPLAIN gives a quick plan, and SQL Developer has an Explain Plan button (F10).

Common operations

Operation Meaning
TABLE ACCESS FULL Reads every block of the table. Fine for small tables or when most rows are needed.
INDEX UNIQUE SCAN One row found through a unique index (primary key lookup).
INDEX RANGE SCAN A range of index entries, such as WHERE salary > 8000 or an equality on a non-unique column.
INDEX FAST FULL SCAN Reads the entire index like a thin table when it holds all needed columns.
TABLE ACCESS BY INDEX ROWID Fetches the table row an index entry points to.
NESTED LOOPS For each row of the outer input, probe the inner one. Good for few outer rows plus an indexed inner.
HASH JOIN Build a hash table from the smaller input and probe with the larger. Good for big joins without useful indexes.
MERGE JOIN Sort both inputs and merge them. Used when inputs are sorted already or for inequality joins.
SORT ORDER BY, HASH GROUP BY Sorting and aggregation steps.

A full scan is not automatically bad. If a query needs 40 percent of a table, scanning it in large sequential reads beats 40 percent worth of random index lookups. The goal is a plan that matches the amount of data, not an index everywhere.

How an index changes the plan

SQLite expresses the same idea in a smaller form. Run both statements in turn and compare the detail column: the first scans the table, the second searches an index.

SQL Full scan, then index search (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

A covering index (one that contains every column the query needs) can answer a query without touching the table at all, the Oracle INDEX RANGE SCAN with no TABLE ACCESS BY INDEX ROWID step:

SQL A covering index (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Optimizer statistics

The optimizer predicts costs from statistics: the number of rows and blocks in a table, the number of distinct values in each column, the data distribution (histograms) and index details. Wrong statistics produce wrong plans.

SQL
-- gather statistics for one table (and its indexes)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'EMPLOYEES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade          => TRUE);
END;
/

-- see what Oracle knows
SELECT num_rows, blocks, last_analyzed FROM user_tables WHERE table_name = 'EMPLOYEES';
SELECT column_name, num_distinct, histogram FROM user_tab_col_statistics WHERE table_name = 'EMPLOYEES';

Oracle gathers statistics itself in a nightly automatic maintenance window, so normally you do not need to do anything. Gather manually after a large data load, before testing a query on a freshly populated table, or when E-Rows and A-Rows disagree. Always use DBMS_STATS; the older ANALYZE command is not for the optimizer. Use GATHER_SCHEMA_STATS for a whole schema, and lock statistics (LOCK_TABLE_STATS) on tables where a stable plan matters more than freshness.

Two related features: histograms capture skewed data (for example, 90 percent of orders have status CLOSED) so the optimizer picks a scan for the common value and an index for the rare one, and bind variable peeking lets the optimizer look at the first bind value used when it parses. Adaptive cursor sharing then produces different plans for different bind values when it detects skew.

A simple mental model

The Python stand-in below prices two access paths with a toy cost model, to show why the optimizer flips from an index to a full scan as the share of matching rows grows:

Python Why the optimizer switches plans (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A tuning workflow

  1. Find the slow statement (AWR report, V$SQL, or the application log).
  2. Get its actual plan with GATHER_PLAN_STATISTICS.
  3. Look for the step where estimated and actual rows diverge, or where most time goes.
  4. Fix the cause: refresh statistics, add or change an index (indexes), rewrite the query, or as a last resort add a hint (tuning and hints).
  5. Re-run and compare. Change one thing at a time.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement shows the plan of a query without executing it?

    EXPLAIN PLAN records the estimated plan, and DBMS_XPLAN.DISPLAY prints it. The statement itself is not run.
  2. 2. In an execution plan, which operation does Oracle run first?

    Child operations feed their parent, so the deepest one executes first.
  3. 3. A large gap between E-Rows and A-Rows usually points to...

    The optimizer's estimate came from statistics. When they are wrong, plans are wrong.
  4. 4. Which package should you use to gather optimizer statistics?

    DBMS_STATS is the supported interface; ANALYZE is not meant for optimizer statistics.

Summary

  • The optimizer picks an execution plan from statistics; read it with EXPLAIN PLAN plus DBMS_XPLAN, or the real one with DISPLAY_CURSOR and GATHER_PLAN_STATISTICS.
  • Know the main operations: full scan, index unique and range scans, nested loops, hash join, merge join.
  • Compare estimated and actual rows; mismatches point to bad statistics.
  • Gather statistics with DBMS_STATS, mostly automatic, manually after big loads.

Next: Indexes in Oracle