News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 19c New Feature Compare Execution Plans with DBMS_XPLAN.COMPARE_PLANS

  • Posted by Gavin Soorma
  • On August 12, 2019
  • 0 Comments
  • 18c, 19c, 19c new features, compare_plans, dbms_xplan, Performance Tuning

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> 

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *