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!
Martins Blog 