Oracle execution plans with DBMS_XPLAN

A query for the 10 ‘new’ orders among 100,000 can be planned for 33,333 rows, because the optimizer assumes the rows are spread evenly over the three status values. An execution planThe steps Oracle's optimizer chooses to run a SQL statement: which tables and indexes to read, in what order, and how to join them, with an estimate of the rows each step returns. shows that choice, and DBMS_XPLAN prints it, first as an estimate and then with the rows the query really read.

You will read both kinds of plan, measure the gap between them, fix an estimate with a histogram, and watch a bind value decide which plan a statement keeps.

EXPLAIN PLAN shows the optimizer’s plan before anything runs

Every script in this lesson builds its own orders_skew table: 100,000 orders, of which 98,990 are ‘shipped’, 1,000 ‘paid’ and 10 ‘new’, with an index on status. EXPLAIN PLANEXPLAIN PLAN: An Oracle SQL statement that asks the optimizer for a statement's plan and writes its steps to PLAN_TABLE without running the statement, so every number in the result is an estimate. asks for the plan of a query without running it:

The optimizer writes one row per step into PLAN_TABLE, and DBMS_XPLAN.DISPLAY formats those rows. TYPICAL, the default format, shows the operations, the estimated rows, bytes and cost, and the predicates; BASIC shows only the operations; ALL adds the projection and alias sections:

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- EXPLAIN PLAN asks the optimizer for its plan without running the query; DBMS_XPLAN.DISPLAY formats the rows
-- EXPLAIN PLAN wrote to PLAN_TABLE. The table is built here: 100,000 orders, of which 10 are 'new'.
SET FEEDBACK OFF
SET LINESIZE 150
SET PAGESIZE 200
DROP TABLE IF EXISTS orders_skew PURGE;

CREATE TABLE orders_skew AS
SELECT LEVEL AS order_id,
       CASE WHEN LEVEL <= 10 THEN 'new'
            WHEN LEVEL <= 1010 THEN 'paid'
            ELSE 'shipped' END AS status,
       MOD(LEVEL, 500) + 1 AS customer_id,
       ROUND(MOD(LEVEL * 37, 9000) + 99.5, 2) AS amount
FROM   dual
CONNECT BY LEVEL <= 100000;

CREATE INDEX orders_skew_status_ix ON orders_skew (status);

-- Table and column statistics, with no histogram on any column yet.
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS_SKEW', method_opt => 'FOR ALL COLUMNS SIZE 1')

SET FEEDBACK ON
-- 1. The plan for the 10 'new' orders, estimated only: nothing runs.
EXPLAIN PLAN FOR
SELECT order_id, customer_id, amount FROM orders_skew WHERE status = 'new';

-- 2. Expected: Rows 33333 for the status step (100,000 rows / 3 distinct values), and the predicate
--    "STATUS"='new' under Predicate Information. Whether it shows INDEX RANGE SCAN or TABLE ACCESS FULL is the
--    optimizer's choice for 33,333 rows; read it on your run.
SELECT * FROM DBMS_XPLAN.DISPLAY(format => 'TYPICAL');

-- 3. The same, with only the operations.
SELECT * FROM DBMS_XPLAN.DISPLAY(format => 'BASIC');

-- EXPLAIN PLAN is a DML statement that wrote rows to PLAN_TABLE and does not commit them: roll them back. Every
-- script of this lesson builds its own copy of orders_skew, so this one drops it.
ROLLBACK;
DROP TABLE orders_skew PURGE;

"""explain_basic.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("plans/explain_basic.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @plans/explain_basic.sql
Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3497657578

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 33333 |   716K|   105   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDERS_SKEW | 33333 |   716K|   105   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STATUS"='new')

13 rows selected.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3497657578

-----------------------------------------
| Id  | Operation         | Name        |
-----------------------------------------
|   0 | SELECT STATEMENT  |             |
|   1 |  TABLE ACCESS FULL| ORDERS_SKEW |
-----------------------------------------

8 rows selected.


Rollback complete.


Table dropped.

The Rows column of the status step reads 33,333: the statistics know the table has 100,000 rows and 3 distinct statuses, and nothing more. EXPLAIN PLAN is a DML statement that does not commit, so the script rolls its PLAN_TABLE rows back.

Actual plans put measured rows beside the estimates

After a query runs, its plan stays in the cursor cache, and DISPLAY_CURSORDISPLAY_CURSOR: A DBMS_XPLAN function that reads the plan a statement really used from the cursor cache; with the gather_plan_statistics hint it shows measured rows (A-Rows) beside the estimates (E-Rows). reads it from there. Run the query with the hint gather_plan_statistics and each step also records the rows it produced and the buffers it read:

With the format 'ALLSTATS LAST', E-Rows (the cardinality estimatecardinality estimate: The number of rows the optimizer expects an operator to return, based on statistics; a large gap with actual rows signals a plan problem. More in “Reading SQL Server execution plans”) stands next to A-Rows, the measured rows of the last execution, with a Buffers column of logical reads. The script needs read access to V$SQL, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL and V$SESSION; its header gives the four grants:

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- DBMS_XPLAN.DISPLAY_CURSOR reads the plan a query really used from the cursor cache. With the hint
-- gather_plan_statistics the query also records, per step, the rows it produced (A-Rows) and the buffers it read.
-- DISPLAY_CURSOR needs READ on four views the lab's LAB user does not hold; once, as SYS in FREEPDB1:
--   GRANT READ ON SYS.V_$SQL TO lab;                    GRANT READ ON SYS.V_$SQL_PLAN TO lab;
--   GRANT READ ON SYS.V_$SQL_PLAN_STATISTICS_ALL TO lab; GRANT READ ON SYS.V_$SESSION TO lab;
SET FEEDBACK OFF
SET LINESIZE 160
SET PAGESIZE 200
DROP TABLE IF EXISTS orders_skew PURGE;

CREATE TABLE orders_skew AS
SELECT LEVEL AS order_id,
       CASE WHEN LEVEL <= 10 THEN 'new'
            WHEN LEVEL <= 1010 THEN 'paid'
            ELSE 'shipped' END AS status,
       MOD(LEVEL, 500) + 1 AS customer_id,
       ROUND(MOD(LEVEL * 37, 9000) + 99.5, 2) AS amount
FROM   dual
CONNECT BY LEVEL <= 100000;

CREATE INDEX orders_skew_status_ix ON orders_skew (status);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS_SKEW', method_opt => 'FOR ALL COLUMNS SIZE 1')

-- With SERVEROUTPUT ON, SQL*Plus runs its own call after each statement, and DISPLAY_CURSOR with no SQL_ID would
-- show that call's plan instead of the query's.
SET SERVEROUTPUT OFF
SET FEEDBACK ON

-- 1. Run the query with row counting switched on. Expected: COUNT 10, SUM of the 10 'new' amounts.
SELECT /*+ gather_plan_statistics */ COUNT(*) AS orders, SUM(amount) AS total
FROM   orders_skew
WHERE  status = 'new';

-- 2. The plan of the last statement this session ran, with its last execution's statistics. Expected: E-Rows
--    33333 against A-Rows 10 on the step that filters status, and a Buffers column per step.
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST');

-- 3. The same for 'shipped'. Expected: E-Rows 33333 against A-Rows 98990.
SELECT /*+ gather_plan_statistics */ COUNT(*) AS orders, SUM(amount) AS total
FROM   orders_skew
WHERE  status = 'shipped';
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST');

DROP TABLE orders_skew PURGE;

"""display_cursor.sql in the lab as LAB, after the four grants its header names. The A-Time column, the time
each step took, changes from run to run and is shown as <time>."""

from oracle_session import Lab

with Lab(masks=[(r"\|\d\d:\d\d:\d\d\.\d\d \|", "|  <time>    |")]) as lab:
    lab.sysdba("""
GRANT READ ON SYS.V_$SQL TO lab;
GRANT READ ON SYS.V_$SQL_PLAN TO lab;
GRANT READ ON SYS.V_$SQL_PLAN_STATISTICS_ALL TO lab;
GRANT READ ON SYS.V_$SESSION TO lab;
""", note="once, as SYS in FREEPDB1")
    lab.sqlplus("plans/display_cursor.sql")

-- once, as SYS in FREEPDB1
$ sqlplus -s / as sysdba <<'SQL'
ALTER SESSION SET CONTAINER = FREEPDB1;
GRANT READ ON SYS.V_$SQL TO lab;
GRANT READ ON SYS.V_$SQL_PLAN TO lab;
GRANT READ ON SYS.V_$SQL_PLAN_STATISTICS_ALL TO lab;
GRANT READ ON SYS.V_$SESSION TO lab;
SQL
Session altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

$ sqlplus -s lab@//localhost/FREEPDB1 @plans/display_cursor.sql
    ORDERS      TOTAL
---------- ----------
        10       3030

1 row selected.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a6ku484a2pcv1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) AS orders, SUM(amount) AS
total FROM   orders_skew WHERE  status = 'new'

Plan hash value: 1102967995

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |      1 |        |      1 |  <time>    |     373 |
|   1 |  SORT AGGREGATE    |             |      1 |      1 |      1 |  <time>    |     373 |
|*  2 |   TABLE ACCESS FULL| ORDERS_SKEW |      1 |  33333 |     10 |  <time>    |     373 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"='new')


20 rows selected.


    ORDERS      TOTAL
---------- ----------
     98990  455287970

1 row selected.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1ajv1v0vfd729, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) AS orders, SUM(amount) AS
total FROM   orders_skew WHERE  status = 'shipped'

Plan hash value: 1102967995

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |      1 |        |      1 |  <time>    |     373 |
|   1 |  SORT AGGREGATE    |             |      1 |      1 |      1 |  <time>    |     373 |
|*  2 |   TABLE ACCESS FULL| ORDERS_SKEW |      1 |  33333 |  98990 |  <time>    |     373 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"='shipped')


20 rows selected.


Table dropped.

For ‘new’, 33,333 rows were expected and 10 found; for ‘shipped’, 33,333 expected and 98,990 found. A plan costed for a third of the table can be the wrong plan for either.

Which plan tools every edition has, and which need a pack

DBMS_XPLAN’s DISPLAY and DISPLAY_CURSOR read the plan table and the cursor cache, and the Licensing Information lists neither under a management pack. The workload history and the tuning advisers are different: on Enterprise Edition they belong to the Diagnostics and Tuning Packs, which are extra-cost options there.

Oracle AI Database Free includes both packs, and Standard Edition 2 offers neither. DISPLAY_AWR, which reads AWR’s DBA_HIST_ views, is deprecated in 26ai in favour of DISPLAY_WORKLOAD_REPOSITORY.

Histograms: the counts behind a row estimate Pro: opens with a Pro, Premium or Ultimate pass

Bind peeking and adaptive cursor sharing, measured Pro: opens with a Pro, Premium or Ultimate pass

Practise

The questions use orders_skew and the plans of the scripts above:

Interview questions

How do DBMS_XPLAN.DISPLAY and DISPLAY_CURSOR differ, and when do you use each? (warm-up; fresher, 2–5 years)

DISPLAY formats the rows that EXPLAIN PLAN wrote to PLAN_TABLE: the plan the optimizer would choose, worked out without running the query, so every number is an estimate. It suits a statement you cannot or should not run, such as a large UPDATE. DISPLAY_CURSOR reads the plan a statement really used from the cursor cache; run the statement with gather_plan_statistics and the format ‘ALLSTATS LAST’ shows E-Rows beside A-Rows and buffers per step, which is how you see where the estimate went wrong. Neither needs a management pack.

Two longer questions follow, on a batch job that slowed down and on one statement that is fast for one value and slow for another:

Key takeaways

• EXPLAIN PLAN writes the optimizer’s plan to PLAN_TABLE without running the query, and DBMS_XPLAN.DISPLAY formats it; every number in it is an estimate.
• DISPLAY_CURSOR reads the plan a query used from the cursor cache, and with gather_plan_statistics and ALLSTATS LAST it shows E-Rows beside A-Rows.
• DISPLAY and DISPLAY_CURSOR need no pack, while AWR, ASH and the tuning advisers need the Diagnostics and Tuning Packs on Enterprise Edition.