DOAG 2021 gems: DBMS_XPLAN.COMPARE_PLANS

The most excellent #DOAG2021 conference ended last week. I have attended quite a few presentations and took lots of notes. I particularly enjoyed Conner McDonald‘s presentation about 25 years of tips and techniques. One of these tips prompted this blog post ;)

Turns out I have only seen a change to DBMS_XPLAN in passing. Its functionality has been extended in 19c, allowing you to compare execution plans. So needless to say I wanted to try DBMS_XPLAN.COMPARE_PLANS in my lab. The 19c Packages and Types documentation defines the call as follows:

DBMS_XPLAN.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;

The meaning of the first two parameters isn’t immediately obvious, so back to the documentation again. A generic plan object contains a single “SQL plan” whereas a plan object list is an array of these. Different options to reference the “SQL Plan” exist. You can grab a “SQL plan” from the plan table, cursor cache and many others.

I’m sure there are license considerations to be taken into account here so be careful which option you choose! Actually this applies to this entire blog (apologies if I have said it before, but), if you want to follow along please ensure you are license compliant.

The idea as I see it is for you to pick a reference plan from a supported source and compare it with 1 or many other plans. OK I think I have enough to get started.

Running Queries

As always I’m using Swingbench and its Order Entry schema as an example. I specifically went for it as it has enough complexity to create some larger execution plans but not too complex to make it impossible to follow the example. I’m running Oracle 19c (19.12.0) Enterprise Edition on Oracle Linux 8.4 by the way.

I managed to come up with what I think is a suitable compromise for this post:

SQL> !cat query.sql
set timing on echo on

SELECT /*+ gather_plan_statistics */
    o.order_id,
    SUM(oi.unit_price * oi.quantity) AS revenue,
    p.category_id,
    o.order_date
FROM
         orders o
    JOIN order_items oi ON ( o.order_id = oi.order_id )
    JOIN products    p ON ( p.product_id = oi.product_id )
WHERE
    o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00' AND TIMESTAMP '2007-01-01 13:30:00'
GROUP BY
    o.order_id,
    p.category_id,
    o.order_date
ORDER BY
    o.order_id;
    
set timing off echo off

I’m joining PRODUCTS (a view), ORDERS and ORDER_ITEMS before applying a filter predicate. This requires the use of a timestamp as ORDERS.ORDER_DATE is a TIMESTAMP(6) WITH LOCAL TIME ZONE

Let’s execute the query:

@query

...

  45740079       8082           3 01-JAN-07 01.00.00.000000 PM
  45740079       5960          83 01-JAN-07 01.00.00.000000 PM

3552 rows selected.

Elapsed: 00:00:23.37
SQL> 
SQL> set timing off echo off

I was a little surprised about the elapsed time as it seems a little long. Both tables are partitioned using the hash partitioning scheme offered by oewizard. Here are some other stats worth knowing:

  • ORDERS: 45,924,841 rows occupying 5632 MB of disk space (without indices)
  • ORDER_ITEMS: 232,170,100 rows for 16128 MB of disk space (again without indices)

So let’s try and work out why the query took quite some time to complete. I’m using Tanel Poder’s excellent tpt-oracle scripts for this. Immediately after the statement finishes executing I use x.sql:

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gtr9hy6x492p7, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */     o.order_id,
SUM(oi.unit_price * oi.quantity) AS revenue,     p.category_id,
o.order_date FROM          orders o     JOIN order_items oi ON (
o.order_id = oi.order_id )     JOIN products    p ON ( p.product_id =
oi.product_id ) WHERE     o.order_date BETWEEN TIMESTAMP '2007-01-01
13:00:00' AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY     o.order_id,
   p.category_id,     o.order_date ORDER BY     o.order_id

Plan hash value: 1832779287

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                     |      1 |        |       |   198K(100)|       |       |   3552 |00:00:23.30 |     717K|    714K|       |       |          |
|   1 |  SORT GROUP BY                          |                     |      1 |   2328 |   104K|   198K  (1)|       |       |   3552 |00:00:23.30 |     717K|    714K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                |                     |      1 |        |       |            |       |       |   3648 |00:00:13.75 |     717K|    714K|       |       |          |
|*  3 |    HASH JOIN RIGHT OUTER                |                     |      1 |   2328 |   104K|   198K  (1)|       |       |   3648 |00:00:13.75 |     717K|    714K|  2546K|  2546K| 1622K (0)|
|   4 |     INDEX FAST FULL SCAN                | PRD_DESC_PK         |      1 |   1000 |  4000 |     3   (0)|       |       |   1000 |00:00:00.01 |      17 |      2 |       |       |          |
|*  5 |     HASH JOIN                           |                     |      1 |   2328 | 97776 |   198K  (1)|       |       |   3648 |00:00:11.13 |     717K|    714K|  1995K|  1995K| 1674K (0)|
|   6 |      TABLE ACCESS FULL                  | PRODUCT_INFORMATION |      1 |   1000 |  8000 |     9   (0)|       |       |   1000 |00:00:00.01 |      30 |     10 |       |       |          |
|   7 |      NESTED LOOPS                       |                     |      1 |   2328 | 79152 |   198K  (1)|       |       |   3648 |00:00:11.13 |     717K|    714K|       |       |          |
|   8 |       NESTED LOOPS                      |                     |      1 |   2328 | 79152 |   198K  (1)|       |       |   3648 |00:00:13.73 |     717K|    714K|       |       |          |
|   9 |        PARTITION HASH ALL               |                     |      1 |    463 |  7871 |   196K  (1)|     1 |    32 |    768 |00:00:19.29 |     714K|    714K|       |       |          |
|* 10 |         TABLE ACCESS FULL               | ORDERS              |     32 |    463 |  7871 |   196K  (1)|     1 |    32 |    768 |00:00:19.82 |     714K|    714K|       |       |          |
|* 11 |        INDEX RANGE SCAN                 | ITEM_ORDER_IX       |    768 |      5 |       |     3   (0)|       |       |   3648 |00:00:00.04 |    2322 |     42 |       |       |          |
|  12 |       TABLE ACCESS BY GLOBAL INDEX ROWID| ORDER_ITEMS         |   3648 |      5 |    85 |     5   (0)| ROWID | ROWID |   3648 |00:00:00.03 |     801 |     30 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
   3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
   5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
  10 - filter(("O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000' AND "O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000'))
  11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


39 rows selected.

Hmmm, that’s a bit strange: instead of a full scan on ORDERS (line 10) I would have expected the use of an index. I know there is one ;) And here is proof:

SQL> @ind order_date
Display indexes where table or index name matches %order_date%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SOE                  ORDERS                         ORD_ORDER_DATE_IX                 1 ORDER_DATE


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SOE                  ORDERS                         ORD_ORDER_DATE_IX              NORMAL/REV NO   VALID    NO   N     4     148859        632130   46338862   46298397 2021-11-23 07:06:01 1      VISIBLE
SQL> 

Oh hang on a sec: ORDER_DATE does have an index, but it’s a reverse key index. This does have a few implications as explained by Richard Foote, let’s try and see if a “regular” index makes a difference.

Note that changing the index type might very well cause issues unrelated to this particular query. There is almost certainly a reason why the index was created as a reverse key index so by “fixing” this issue you can end up introducing another. Or multiple others.

– Lesson learned the hard way after stuff broke

By the way, further executions of the query didn’t change the elapsed time, they occasionally resulted in the creation an additional child cursor thanks to statistics feedback.

Let’s recreate the index as a non-reverse index:

SQL> alter index ORD_ORDER_DATE_IX rebuild noreverse parallel 8;

Index altered.

SQL> alter index ORD_ORDER_DATE_IX noparallel;

Index altered.

Running the query again gives me a different result:

SQL> @query

...

  45623157       5700         141 01-JAN-07 01.00.00.000000 PM
  45623157       5455         192 01-JAN-07 01.00.00.000000 PM
  45740079       8082           3 01-JAN-07 01.00.00.000000 PM
  45740079       5960          83 01-JAN-07 01.00.00.000000 PM

3552 rows selected.

Elapsed: 00:00:00.06
SQL> 

Right, so there is a difference in elapsed time :) Let’s check if the index was used:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gtr9hy6x492p7, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */     o.order_id,
SUM(oi.unit_price * oi.quantity) AS revenue,     p.category_id,
o.order_date FROM          orders o     JOIN order_items oi ON (
o.order_id = oi.order_id )     JOIN products    p ON ( p.product_id =
oi.product_id ) WHERE     o.order_date BETWEEN TIMESTAMP '2007-01-01
13:00:00' AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY     o.order_id,
   p.category_id,     o.order_date ORDER BY     o.order_id

Plan hash value: 4255998723

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                     |      1 |        |       |  2801 (100)|       |       |   3552 |00:00:00.03 |    3945 |    772 |       |       |          |
|   1 |  SORT GROUP BY                                   |                     |      1 |   2328 |   104K|  2801   (1)|       |       |   3552 |00:00:00.03 |    3945 |    772 |   302K|   302K|  268K (0)|
|*  2 |   FILTER                                         |                     |      1 |        |       |            |       |       |   3648 |00:00:00.01 |    3945 |    772 |       |       |          |
|*  3 |    HASH JOIN RIGHT OUTER                         |                     |      1 |   2328 |   104K|  2800   (1)|       |       |   3648 |00:00:00.01 |    3945 |    772 |  2546K|  2546K|  353K (0)|
|   4 |     INDEX FAST FULL SCAN                         | PRD_DESC_PK         |      1 |   1000 |  4000 |     3   (0)|       |       |   1000 |00:00:00.01 |      17 |      0 |       |       |          |
|*  5 |     HASH JOIN                                    |                     |      1 |   2328 | 97776 |  2797   (1)|       |       |   3648 |00:00:00.01 |    3928 |    772 |  1995K|  1995K|  353K (0)|
|   6 |      TABLE ACCESS FULL                           | PRODUCT_INFORMATION |      1 |   1000 |  8000 |     9   (0)|       |       |   1000 |00:00:00.01 |      30 |      0 |       |       |          |
|   7 |      NESTED LOOPS                                |                     |      1 |   2328 | 79152 |  2788   (1)|       |       |   3648 |00:00:00.01 |    3898 |    772 |       |       |          |
|   8 |       NESTED LOOPS                               |                     |      1 |   2328 | 79152 |  2788   (1)|       |       |   3648 |00:00:00.01 |    3097 |    772 |       |       |          |
|   9 |        TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS              |      1 |    463 |  7871 |   472   (0)| ROWID | ROWID |    768 |00:00:00.41 |     775 |    772 |       |       |          |
|* 10 |         INDEX RANGE SCAN                         | ORD_ORDER_DATE_IX   |      1 |    467 |       |     5   (0)|       |       |    768 |00:00:00.01 |       8 |      5 |       |       |          |
|* 11 |        INDEX RANGE SCAN                          | ITEM_ORDER_IX       |    768 |      5 |       |     3   (0)|       |       |   3648 |00:00:00.01 |    2322 |      0 |       |       |          |
|  12 |       TABLE ACCESS BY GLOBAL INDEX ROWID         | ORDER_ITEMS         |   3648 |      5 |    85 |     5   (0)| ROWID | ROWID |   3648 |00:00:00.01 |     801 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
   3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
   5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
  10 - access("O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000' AND "O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000')
  11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


39 rows selected.

It very much looks like the change from reverse to non-reverse index provided plenty of benefit for this query. If regression testing showed no problems with the other workloads on this system there is a strong argument to put this into production.

SQL Plan Analysis

At this point in time V$SQL shows 3 entries for SQL_ID gtr9hy6x492p7:

  • Plan Hash Value 1832779287: original execution using reverse key index (child cursor number 0)
  • Plan Hash Value 4255998723: using the index rebuilt as non-reverse key (child cursor number 1 and 2)

Child cursor 3 uses the same Plan Hash Value despite having used statistics feedback. In this demo run a second execution against the reverse-key index didn’t trigger statistics feedback although I have seen it during earlier tests.

While it’s fairly obvious to the human eye where the differences are between child cursor 0 and the others, let’s see what Oracle comes up with.

With the information provided by x.sql I can compare plans from the cursor cache. I rewrote the code example from the documentation a little, the end result however is the same.

var report clob

BEGIN
    :report := dbms_xplan.compare_plans(
        reference_plan => cursor_cache_object('gtr9hy6x492p7', 0), 
        compare_plan_list => plan_object_list(
            cursor_cache_object('gtr9hy6x492p7', 1), 
            cursor_cache_object('gtr9hy6x492p7', 2)
        ), 
        type => 'TEXT');
END;
/

print report

And here is the report. Tanel’s login.sql does a great job formatting the output by the way:

REPORT
-----------------------------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SOE
  Total number of plans  : 3
  Number of findings     : 6
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gtr9hy6x492p7
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SOE"
 SQL Text               : SELECT /*+ gather_plan_statistics */ o.order_id,
                        SUM(oi.unit_price * oi.quantity) AS revenue,
                        p.category_id, o.order_date FROM orders o JOIN
                        order_items oi ON ( o.order_id = oi.order_id ) JOIN
                        products p ON ( p.product_id = oi.product_id ) WHERE
                        o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00'
                        AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY
                        o.order_id, p.category_id, o.order_date ORDER BY
                        o.order_id

Plan
-----------------------------

 Plan Hash Value  : 1832779287

--------------------------------------------------------------------------------------------------------------
| Id   | Operation                                 | Name                | Rows | Bytes  | Cost   | Time     |
--------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                          |                     |      |        | 198882 |          |
|    1 |   SORT GROUP BY                           |                     | 2328 | 107088 | 198882 | 00:00:08 |
|  * 2 |    FILTER                                 |                     |      |        |        |          |
|  * 3 |     HASH JOIN RIGHT OUTER                 |                     | 2328 | 107088 | 198881 | 00:00:08 |
|    4 |      INDEX FAST FULL SCAN                 | PRD_DESC_PK         | 1000 |   4000 |      3 | 00:00:01 |
|  * 5 |      HASH JOIN                            |                     | 2328 |  97776 | 198877 | 00:00:08 |
|    6 |       TABLE ACCESS FULL                   | PRODUCT_INFORMATION | 1000 |   8000 |      9 | 00:00:01 |
|    7 |       NESTED LOOPS                        |                     | 2328 |  79152 | 198868 | 00:00:08 |
|    8 |        NESTED LOOPS                       |                     | 2328 |  79152 | 198868 | 00:00:08 |
|    9 |         PARTITION HASH ALL                |                     |  463 |   7871 | 196553 | 00:00:08 |
| * 10 |          TABLE ACCESS FULL                | ORDERS              |  463 |   7871 | 196553 | 00:00:08 |
| * 11 |         INDEX RANGE SCAN                  | ITEM_ORDER_IX       |    5 |        |      3 | 00:00:01 |
|   12 |        TABLE ACCESS BY GLOBAL INDEX ROWID | ORDER_ITEMS         |    5 |     85 |      5 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
* 3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
* 5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
* 10 - filter(("O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000' AND "O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000'))
* 11 - access("O"."ORDER_ID"="OI"."ORDER_ID")

---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gtr9hy6x492p7
 Child Number           : 1
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SOE"
 SQL Text               : SELECT /*+ gather_plan_statistics */ o.order_id,
                        SUM(oi.unit_price * oi.quantity) AS revenue,
                        p.category_id, o.order_date FROM orders o JOIN
                        order_items oi ON ( o.order_id = oi.order_id ) JOIN
                        products p ON ( p.product_id = oi.product_id ) WHERE
                        o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00'
                        AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY
                        o.order_id, p.category_id, o.order_date ORDER BY
                        o.order_id

Plan
-----------------------------

 Plan Hash Value  : 4255998723

---------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                          | Name                | Rows | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                                   |                     |      |        | 2801 |          |
|    1 |   SORT GROUP BY                                    |                     | 2328 | 107088 | 2801 | 00:00:01 |
|  * 2 |    FILTER                                          |                     |      |        |      |          |
|  * 3 |     HASH JOIN RIGHT OUTER                          |                     | 2328 | 107088 | 2800 | 00:00:01 |
|    4 |      INDEX FAST FULL SCAN                          | PRD_DESC_PK         | 1000 |   4000 |    3 | 00:00:01 |
|  * 5 |      HASH JOIN                                     |                     | 2328 |  97776 | 2797 | 00:00:01 |
|    6 |       TABLE ACCESS FULL                            | PRODUCT_INFORMATION | 1000 |   8000 |    9 | 00:00:01 |
|    7 |       NESTED LOOPS                                 |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    8 |        NESTED LOOPS                                |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    9 |         TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | ORDERS              |  463 |   7871 |  472 | 00:00:01 |
| * 10 |          INDEX RANGE SCAN                          | ORD_ORDER_DATE_IX   |  467 |        |    5 | 00:00:01 |
| * 11 |         INDEX RANGE SCAN                           | ITEM_ORDER_IX       |    5 |        |    3 | 00:00:01 |
|   12 |        TABLE ACCESS BY GLOBAL INDEX ROWID          | ORDER_ITEMS         |    5 |     85 |    5 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
* 3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
* 5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
* 10 - access("O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000' AND "O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000')
* 11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


Comparison Results (3):
-----------------------------
 1. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 9) in the
    reference plan are missing in the current plan.
 2. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 10) in the
    current plan are missing in the reference plan.
 3. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some columns (OPTIONS, ID,
    PARENT_ID, DEPTH, PARTITION_START, PARTITION_STOP) do not match between the
    reference plan (id: 10) and the current plan (id: 9).


---------------------------------------------------------------------------------------------
 Plan Number            : 3
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gtr9hy6x492p7
 Child Number           : 2
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SOE"
 SQL Text               : SELECT /*+ gather_plan_statistics */ o.order_id,
                        SUM(oi.unit_price * oi.quantity) AS revenue,
                        p.category_id, o.order_date FROM orders o JOIN
                        order_items oi ON ( o.order_id = oi.order_id ) JOIN
                        products p ON ( p.product_id = oi.product_id ) WHERE
                        o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00'
                        AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY
                        o.order_id, p.category_id, o.order_date ORDER BY
                        o.order_id

Plan
-----------------------------

 Plan Hash Value  : 4255998723

---------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                          | Name                | Rows | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                                   |                     |      |        | 2801 |          |
|    1 |   SORT GROUP BY                                    |                     | 2328 | 107088 | 2801 | 00:00:01 |
|  * 2 |    FILTER                                          |                     |      |        |      |          |
|  * 3 |     HASH JOIN RIGHT OUTER                          |                     | 2328 | 107088 | 2800 | 00:00:01 |
|    4 |      INDEX FAST FULL SCAN                          | PRD_DESC_PK         | 1000 |   4000 |    3 | 00:00:01 |
|  * 5 |      HASH JOIN                                     |                     | 2328 |  97776 | 2797 | 00:00:01 |
|    6 |       TABLE ACCESS FULL                            | PRODUCT_INFORMATION | 1000 |   8000 |    9 | 00:00:01 |
|    7 |       NESTED LOOPS                                 |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    8 |        NESTED LOOPS                                |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    9 |         TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | ORDERS              |  463 |   7871 |  472 | 00:00:01 |
| * 10 |          INDEX RANGE SCAN                          | ORD_ORDER_DATE_IX   |  467 |        |    5 | 00:00:01 |
| * 11 |         INDEX RANGE SCAN                           | ITEM_ORDER_IX       |    5 |        |    3 | 00:00:01 |
|   12 |        TABLE ACCESS BY GLOBAL INDEX ROWID          | ORDER_ITEMS         |    5 |     85 |    5 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
* 3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
* 5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
* 10 - access("O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000' AND "O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000')
* 11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


Notes
-----
- cardinality_feedback = yes


Comparison Results (3):
-----------------------------
 1. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 9) in the
    reference plan are missing in the current plan.
 2. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 10) in the
    current plan are missing in the reference plan.
 3. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some columns (OPTIONS, ID,
    PARENT_ID, DEPTH, PARTITION_START, PARTITION_STOP) do not match between the
    reference plan (id: 10) and the current plan (id: 9).


---------------------------------------------------------------------------------------------

How very nice! So Oracle reviews the plans with regards to the reference and point out what’s different.

Summary

DBMS_XPLAN.COMPARE_PLANS looks like a great addition to the package and it helps discovering differences between SQL execution plans. The report-which looks pretty in HTML by the way-points out differences in columns as well as rows: additional/missing lines are pointed out as well as different access paths as you can see in the report above.

I should point out again that I haven’t studied the license guide, as always please ensure you are appropriately licensed for all technology you use.

Happy troubleshooting!