Oracle DBA

Understanding how two different execution plans differ is a critical skill for performance tuning. In Oracle 19c and 21c, the DBMS_XPLAN package has evolved to include built-in functions that let you compare plans directly: COMPARE_PLANS, COMPARE_CURSOR, and COMPARE_EXPLAIN.

In this post, we will:

1. Set up a testing environment
2. Show example queries with different plans
3. Use COMPARE_PLANS, COMPARE_CURSOR, and COMPARE_EXPLAIN
4. Interpret the results
5. Note caveats & tips

---

Environment Setup

First, create a test user and a simple table to run against:

-- as SYS or a privileged user
DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1 
  QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO testuser1;
GRANT SELECT_CATALOG_ROLE TO testuser1;

Then switch to this user and create a table EMP:

CONNECT testuser1/testuser1@...;

CREATE TABLE emp (
  empno     NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename     VARCHAR2(10),
  job       VARCHAR2(9),
  mgr       NUMBER(4),
  hiredate  DATE,
  sal       NUMBER(7,2),
  comm      NUMBER(7,2),
  deptno    NUMBER(2)
);

INSERT INTO emp VALUES (7369, 'SMITH',   'CLERK',   7902, TO_DATE('17-12-1980','DD-MM-YYYY'), 800,   NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN',   'SALESMAN',7698, TO_DATE('20-02-1981','DD-MM-YYYY'),1600, 300, 30);
-- … (other sample rows) …
COMMIT;

Also, if you’re using SQL*Plus or SQLcl, set your display attributes:

SET LINESIZE 120
SET PAGESIZE 1000
SET LONG 1000000

This ensures the plan output is readable.

---

Example Queries & Plans

Let’s run two logically equivalent queries that produce different plans.

1. Default plan (uses index)

SELECT * FROM emp WHERE empno = 7369;

Immediately after, fetch its execution plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

You might see something like:

SQL_ID ... child number 0
SELECT * FROM emp WHERE empno = 7369

Plan hash value: 2949544139
-------------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------
| 0  | SELECT STATEMENT              |          |      |       |     1|          |
| 1  | TABLE ACCESS BY INDEX ROWID   | EMP      |    1 |    87 |     1| 00:00:01 |
|*2  | INDEX UNIQUE SCAN              | PK_EMP   |    1 |       |     1| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information:
  2 - access("EMPNO" = 7369)

This shows the optimizer picks an index scan path based on the primary key.

2. Force a full table scan via hint

SELECT /*+ FULL(emp) */ * FROM emp WHERE empno = 7369;

Again, show the plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

You’ll now see:

SQL_ID ... child number 0
SELECT /*+ FULL(emp) */ * FROM emp WHERE empno = 7369

Plan hash value: 3956160932
-------------------------------------------------------------------------------
| Id | Operation                | Name | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------
| 0  | SELECT STATEMENT         |      |      |       |     2|          |
|*1  | TABLE ACCESS FULL        | EMP  |    1 |    87 |     2| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information:
  1 - filter("EMPNO" = 7369)

Here, the plan is full scan plus a filter — different path than the index-based plan.

---

Comparing Plans with COMPARE_PLANS

Oracle 19c introduced DBMS_XPLAN.COMPARE_PLANS, which allows comparing two plan objects (generic plan objects). Use it to highlight differences between a “reference plan” and other plans.

The signature is:

FUNCTION compare_plans(
  reference_plan    IN generic_plan_object,
  compare_plan_list IN plan_object_list,
  type              IN VARCHAR2 := 'TEXT',
  level             IN VARCHAR2 := 'TYPICAL',
  section           IN VARCHAR2 := 'ALL'
) RETURN CLOB;

You can call it like this:

VAR l_report CLOB;

BEGIN
  :l_report := DBMS_XPLAN.compare_plans(
    cursor_cache_object('3vksfnydr3639', 0),
    plan_object_list(cursor_cache_object('8s0n2z0trbub4', 0))
  );
END;
/
PRINT l_report;

Or in verbose form:

BEGIN
  :l_report := DBMS_XPLAN.compare_plans(
    reference_plan    => cursor_cache_object('3vksfnydr3639', 0),
    compare_plan_list => plan_object_list(cursor_cache_object('8s0n2z0trbub4', 0)),
    type              => 'TEXT',
    level             => 'TYPICAL',
    section           => 'ALL'
  );
END;
/
PRINT l_report;

In the result output, you’ll see a Compare Plans Report that shows:

• Number of plans compared
• Differences (for example, “Access path is different — INDEX vs FULL”)
• The two plan trees side by side with annotated differences

This is very helpful for quickly spotting key differences in execution strategies.

---

COMPARE_CURSOR in Oracle 21c

With Oracle 21c, a new (though undocumented) function COMPARE_CURSOR was introduced. It simplifies comparison by using just SQL IDs (and optional child numbers).

Signature:

FUNCTION compare_cursor(
  sql_id1 IN VARCHAR2 DEFAULT NULL,
  sql_id2 IN VARCHAR2 DEFAULT NULL,
  childnum1 IN INTEGER DEFAULT NULL,
  childnum2 IN INTEGER DEFAULT NULL,
  type IN VARCHAR2 := 'TEXT',
  level IN VARCHAR2 := 'TYPICAL',
  section IN VARCHAR2 := 'ALL'
) RETURN CLOB;

Example calls:

VAR l_report CLOB;

-- Simple form
BEGIN
  :l_report := DBMS_XPLAN.compare_cursor('3vksfnydr3639', '8s0n2z0trbub4');
END;
/
PRINT l_report;

-- Verbose form
BEGIN
  :l_report := DBMS_XPLAN.compare_cursor(
    sql_id1 => '3vksfnydr3639',
    sql_id2 => '8s0n2z0trbub4',
    childnum1 => 0,
    childnum2 => 0,
    type => 'TEXT',
    level => 'TYPICAL',
    section => 'ALL'
  );
END;
/
PRINT l_report;

The output has the same style as COMPARE_PLANS, showing both plans and differences.

---

COMPARE_EXPLAIN in Oracle 21c

Another new (but also undocumented) function introduced in 21c is COMPARE_EXPLAIN. Instead of relying on existing plans in cursor cache or plan tables, it compares plans derived from EXPLAIN PLAN.

Signature:

FUNCTION compare_explain(
  statement_id1 IN VARCHAR2 DEFAULT NULL,
  statement_id2 IN VARCHAR2 DEFAULT NULL,
  plan_id1 IN NUMBER DEFAULT NULL,
  plan_id2 IN NUMBER DEFAULT NULL,
  type IN VARCHAR2 := 'TEXT',
  level IN VARCHAR2 := 'TYPICAL',
  section IN VARCHAR2 := 'ALL'
) RETURN CLOB;

How to use:

1. Explain both statements first: EXPLAIN PLAN SET statement_id = 'emp1' FOR SELECT * FROM emp WHERE empno = 7369; EXPLAIN PLAN SET statement_id = 'emp2' FOR SELECT /*+ FULL(emp) */ * FROM emp WHERE empno = 7369;
2. Call COMPARE_EXPLAIN: VAR l_report CLOB; BEGIN :l_report := DBMS_XPLAN.compare_explain('emp1', 'emp2'); END; / PRINT l_report;

Or in expanded form:

BEGIN
  :l_report := DBMS_XPLAN.compare_explain(
    statement_id1 => 'emp1',
    statement_id2 => 'emp2',
    plan_id1 => NULL,
    plan_id2 => NULL,
    type => 'TEXT',
    level => 'TYPICAL',
    section => 'ALL'
  );
END;
/
PRINT l_report;

This is useful when you don’t have actual execution plans available (e.g. no cursor in cache), but you can run EXPLAIN PLAN.

---

Interpreting the Output

The comparison reports typically include:

• A header showing user, number of plans, number of differences
• A “Comparison Details” section
• Each plan in a side-by-side or sequential layout
• Differences annotated (for example, “Access path is different — INDEX_RS_ASC vs FULL”)
• Optionally, sections like SUMMARY, FINDINGS, INFORMATION, etc., controlled via the TYPE, LEVEL, SECTION parameters (defaults are TEXT, TYPICAL, ALL).

The default output shows all sections, but you can tailor it to show only the differences or a compact summary.

---

Tips, Caveats, and Best Use Cases

• Because COMPARE_CURSOR and COMPARE_EXPLAIN are undocumented in 21c (at least at the time of writing), their behavior or support might change. Use caution in production.
• COMPARE_PLANS works in 19c and is the more “official” supported method.
• If a SQL isn’t in the cursor cache, or its plan isn’t retained, COMPARE_CURSOR won’t find it. In such cases, COMPARE_EXPLAIN is a fallback, if you can run EXPLAIN PLAN.
• Choose appropriate LEVEL (e.g. BASIC, TYPICAL, ALL) and SECTION (e.g. SUMMARY, FINDINGS) to reduce noise in the comparison output.
• Use this when you’re tuning SQL, comparing before/after hints or optimizer changes, or diagnosing plan regressions.

---

Summary

• Oracle 19c introduced DBMS_XPLAN.COMPARE_PLANS for comparing execution plans from cursor cache / plan objects.
• Oracle 21c extends capabilities with COMPARE_CURSOR (compare by SQL_ID) and COMPARE_EXPLAIN (compare plans from EXPLAIN PLAN).
• These utilities make it much easier to spot differences in access paths, join methods, cost estimations, and more, rather than manually diffing plan text.
• Use them wisely, choose appropriate levels/sections, and be aware of their current documentation status.