In Oracle 19c, a new sub-program called COMPARE_PLANS has been added to DBMS_XPLAN.
With this new functionality, we can take a reference plan and compare it with some other execution plan – it could belong to a SQL statement already executed where we obtain the plan from the Cursor Cache or from the AWR history or an existing SQL Tuning Set – or could be a case where we want to test an how an existing plan will behave if we make some change like adding a hint or maybe creating an index.
We can use DBMS_XPLAN.COMPARE_PLANS to generate a report in text, XML, or HTML format.
Some use cases for this could be:
- We want to compare the current plan of a query whose performance is regressing with an old plan captured in AWR
- We want to determine how adding a hint, changing a parameter, or creating an index will affect a plan
Let us have a look at this feature with an example.
We have a table called MYOBJECTS_19C and we force the execution plan in this case to use an Index via the INDEX hint.
SQL> select /*+ INDEX (MYOBJECTS_19C,MYOBJECTS_IND) */ distinct object_type
2 from myobjects_19c where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
SYNONYM
SCHEDULE
EDITION
JAVA CLASS
PACKAGE
DIRECTORY
PROCEDURE
EVALUATION CONTEXT
TABLE SUBPARTITION
CONSUMER GROUP
DESTINATION
FUNCTION
INDEX PARTITION
JOB CLASS
PROGRAM
SEQUENCE
TYPE
OPERATOR
WINDOW
SCHEDULER GROUP
TABLE
VIEW
JAVA RESOURCE
26 rows selected.
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 22qxvfx8h6u7d, child number 0
-------------------------------------
select /*+ INDEX (MYOBJECTS_19C,MYOBJECTS_IND) */ distinct object_type
from myobjects_19c where owner='SYS'
Plan hash value: 3518837258
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1019 (100)| |
| 1 | HASH UNIQUE | | 26 | 2054 | 1019 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MYOBJECTS_19C | 95948 | 7402K| 1016 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MYOBJECTS_IND | 95948 | | 101 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SYS')
Note
-----
- dynamic statistics used: statistics for conventional DML
We now execute the same query as above – but this time without an INDEX hint. So the execution plan has now changed.
SQL> select distinct object_type
2 from myobjects_19c where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
SYNONYM
SCHEDULE
EDITION
JAVA CLASS
PACKAGE
DIRECTORY
PROCEDURE
EVALUATION CONTEXT
TABLE SUBPARTITION
CONSUMER GROUP
DESTINATION
FUNCTION
INDEX PARTITION
JOB CLASS
PROGRAM
SEQUENCE
TYPE
OPERATOR
WINDOW
SCHEDULER GROUP
TABLE
VIEW
JAVA RESOURCE
26 rows selected.
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 413acgxab86s3, child number 0
-------------------------------------
select distinct object_type from myobjects_19c where owner='SYS'
Plan hash value: 1625058500
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 624 (100)| |
| 1 | HASH UNIQUE | | 26 | 2054 | 624 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| MYOBJECTS_19C | 95948 | 7402K| 621 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Note
-----
- dynamic statistics used: statistics for conventional DML
23 rows selected.
We will now use DBMS_XPLAN.COMPARE_PLANS to generate a TEXT report of the differences which may be found in the source ‘referenced’ execution plan (Plan Number 1) and the ‘new’ execution plan (Plan Number 2) which we want to compare against.
Note the comparison results displayed.
SQL>
SQL> VARIABLE v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('22qxvfx8h6u7d', NULL),
compare_plan_list => plan_object_list(cursor_cache_object('413acgxab86s3', NULL)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/SQL> SQL>
PL/SQL procedure successfully completed.
SQL>SET PAGESIZE 50000
SQL>SET LONG 100000
SQL>SET LINESIZE 210
SQL>COLUMN report FORMAT a200
SQL>SELECT :v_rep REPORT FROM DUAL;
REPORT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : DEMO
Total number of plans : 2
Number of findings : 2
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 22qxvfx8h6u7d
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "DEMO"
SQL Text : select /*+ INDEX (MYOBJECTS_19C,MYOBJECTS_IND) */
distinct object_type from myobjects_19c where
owner='SYS'
Plan
-----------------------------
Plan Hash Value : 3518837258
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1019 | |
| 1 | HASH UNIQUE | | 26 | 2054 | 1019 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | MYOBJECTS_19C | 95948 | 7579892 | 1016 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | MYOBJECTS_IND | 95948 | | 101 | 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("OWNER"='SYS')
Notes
-----
- optimizer_use_stats_on_conventional_dml = yes
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 413acgxab86s3
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "DEMO"
SQL Text : select distinct object_type from myobjects_19c where
owner='SYS'
Plan
-----------------------------
Plan Hash Value : 1625058500
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 624 | |
| 1 | HASH UNIQUE | | 26 | 2054 | 624 | 00:00:01 |
| * 2 | TABLE ACCESS FULL | MYOBJECTS_19C | 95948 | 7579892 | 621 | 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("OWNER"='SYS')
Notes
-----
- optimizer_use_stats_on_conventional_dml = yes
Comparison Results (2):
-----------------------------
1. Query block SEL$1, Alias "MYOBJECTS_19C"@"SEL$1": Some lines (id: 3) in the
reference plan are missing in the current plan.
2. Query block SEL$1, Alias "MYOBJECTS_19C"@"SEL$1": Some columns (OPTIONS) do
not match between the reference plan (id: 2) and the current plan (id: 2).
---------------------------------------------------------------------------------------------
SQL>
