SQL Plan Comparison

 The plan comparison feature (hence forth referred to compare plans tool) takes as input a reference plan and an arbitrary list of test plans. It compares the reference plan with each of the test plans in the list and produces a difference report. Such a tool is useful in a variety of use cases:

 New plans: When a plan change occurs, users want to compare it with an old plan (for instance stored in AWR).

 SQL Performance Analyzer (SPA), SQL Tune: How does a plan generated based on a new SQL profile or plan generated through SPA differs from the original plan?

 SQL Plan Management: When an accepted baseline plan is not reproduced by SPM, how does the original plan that is stored in the baseline differs from the generated plan? What are the differences between the different plan baselines captured for the same query?

 Plan generated from a different SQL statement or based on the effect of hints/parameters: How does adding a particular hint or changing the value of a parameter affects the plan?

Plan comparison goes beyond a simple line-by-line comparison because SQL execution plans are execution trees. A more complex analysis is required and logical differences must be identified.

Consider two queries:

select count(*)

from products p, sales s

where p.prod_id = s.prod_id

and p.prod_min_price > 200;

select count(*)

from products p, sales s

where p.prod_id = s.prod_id

and s.quantity_sold = 43;

They can be compared as follows:

VARIABLE v_rep CLOB

BEGIN

:v_rep := DBMS_XPLAN.COMPARE_PLANS(

reference_plan => cursor_cache_object('0hxmvnfkasg6q', NULL),

compare_plan_list => plan_object_list(cursor_cache_object('10dqxjph6bwum', NULL)),

type => 'TEXT',

level => 'TYPICAL',

section => 'ALL');

END;

/

A report can be generated as follows:

SET PAGESIZE 50000

SET LONG 100000

SET LINESIZE 210

COLUMN report FORMAT a200

SELECT :v_rep REPORT FROM DUAL;

REPORT

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

COMPARE PLANS REPORT

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

Current user : SH

Total number of plans : 2

Number of findings : 1

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

COMPARISON DETAILS

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

Plan Number : 1 (Reference Plan)

Plan Found : Yes

Plan Source : Cursor Cache

SQL ID : 0hxmvnfkasg6q

Child Number : 0

Plan Database Version : 19.0.0.0

Parsing Schema : "SH"

SQL Text : select count(*) from products p, sales s where

p.prod_id = s.prod_id and s.quantity_sold = 43

Plan

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

Plan Hash Value : 3519235612

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

| Id | Operation | Name | Rows | Bytes | Cost | Time |

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

| 0 | SELECT STATEMENT | | | | 469 | |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

| 2 | PARTITION RANGE ALL | | 1 | 3 | 469 | 00:00:01 |

| * 3 | TABLE ACCESS FULL | SALES | 1 | 3 | 469 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 3 - filter("S"."QUANTITY_SOLD"=43)

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

Plan Number : 2

Plan Found : Yes

Plan Source : Cursor Cache

SQL ID : 10dqxjph6bwum

Child Number : 0

Plan Database Version : 19.0.0.0

Parsing Schema : "SH"

SQL Text : select count(*) from products p, sales s where

p.prod_id = s.prod_id and p.prod_min_price > 200

Plan

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

Plan Hash Value : 3037679890

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

| Id | Operation | Name | Rows | Bytes | Cost | Time |

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

| 0 | SELECT STATEMENT | | | | 34 | |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| * 2 | HASH JOIN | | 781685 | 10161905 | 34 | 00:00:01 |

| * 3 | TABLE ACCESS FULL | PRODUCTS | 61 | 549 | 2 | 00:00:01 |

28 WHITE PAPER / The Optimizer in Oracle Database 19c

| 4 | PARTITION RANGE ALL | | 918843 | 3675372 | 29 | 00:00:01 |

| 5 | BITMAP CONVERSION TO ROWIDS | | 918843 | 3675372 | 29 | 00:00:01 |

| 6 | BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX | | | | |

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

Predicate Information (identified by operation id):

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

* 2 - access("P"."PROD_ID"="S"."PROD_ID")

* 3 - filter("P"."PROD_MIN_PRICE">200)

Notes

-----

- This is an adaptive plan

Comparison Results (1):

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

1. Query block SEL$1: Transformation JOIN REMOVED FROM QUERY BLOCK occurred

only in the reference plan (result query block: SEL$A43D1678).