8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

DBMS_XPLAN : Compare Execution Plans in Oracle Database 19c and 21c (COMPARE_PLANS, COMPARE_CURSORS and COMPARE_EXPLAIN)

This article demonstrates how to compare execution plans using the DBMS_XPLAN package in Oracle Database 19c and 21c.

Related articles.

Setup

We create a test user.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;
grant select_catalog_role to testuser1;

We connect to the test user and create the EMP table.

conn testuser1/testuser1@//localhost:1521/pdb1

create table emp (
  empno    number(4) constraint pk_emp primary key,
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2) );

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

If you are using SQL*Plus or SQLcl, these setting may be useful.

set linesize 120 pagesize 1000 long 1000000

Example Queries

We query the EMP table for a specific value of the EMPNO column. Since the EMPNO column is the primary key column, we see the primary key index is used to return the data.

select * from emp where empno = 7369;


select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  3vksfnydr3639, child number 0
-------------------------------------
select * from emp where empno = 7369

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
   2 - access("EMPNO"=7369)


19 rows selected.

SQL>

We repeat the query, but this time force a full table scan using the FULL optimizer hint.

select /*+ full(emp) */ * from emp where empno = 7369;


select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  8s0n2z0trbub4, child number 0
-------------------------------------
select /*+ full(emp) */ * from emp where empno = 7369

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7369)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------


18 rows selected.

SQL>

COMPARE_PLANS

The COMPARE_PLANS function was introduced in Oracle 19c.

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;

We use the CURSOR_CACHE_OBJECT object to return GENERIC_PLAN_OBJECT types for use as parameters. The constructor method for this object accepts a SQL_ID and CHILD_NUMBER attributes.

FINAL CONSTRUCTOR FUNCTION CURSOR_CACHE_OBJECT RETURNS SELF AS RESULT
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID                         VARCHAR2                IN
CHILD_NUMBER                   NUMBER                  IN     DEFAULT

So using the SQL_IDs from the two queries we ran before, and assuming CHILD_NUMBER of "0" for each, we can compare the plans as follows. We've included the simple and verbose version of the syntax, which result in the same report.

var l_report clob;

-- Simple
begin
  :l_report := dbms_xplan.compare_plans(
                 cursor_cache_object('3vksfnydr3639', 0),
                 plan_object_list(cursor_cache_object('8s0n2z0trbub4', 0))
               ); 
end;
/

-- Verbose
begin
  :l_report := dbms_xplan.compare_plans(
                 reference_plan    => cursor_cache_object('3vksfnydr3639', 0),
                 compare_plan_list => plan_object_list(cursor_cache_object('8s0n2z0trbub4', 0)),
                 type              => 'TEXT',
                 level             => 'TYPICAL',
                 section           => 'ALL'
               ); 
end;
/

print l_report

COMPARE_CURSOR

The COMPARE_CURSOR function was introduced in Oracle 21c, but is undocumented at present.

dbms_xplan.compare_cursor(
    sql_id1   in varchar2 default null,
    sql_id2   in varchar2 default null,
    childnum1 in integer  default null,
    childnum2 in integer  default null,
    type      in varchar2 := 'TEXT',
    level     in varchar2 := 'TYPICAL',
    section   in varchar2 := 'ALL')
  return clob;

In the following examples use COMPARE_CURSOR to compare the two plans using the SQL_IDs. We've included the simple and verbose version of the syntax, which result in the same report.

var l_report clob;

-- Simple
begin
  :l_report := dbms_xplan.compare_cursor('3vksfnydr3639','8s0n2z0trbub4');
end;
/

-- Verbose
begin
  :l_report := dbms_xplan.compare_cursor(sql_id1   => '3vksfnydr3639',
                                         sql_id2   => '8s0n2z0trbub4',
                                         childnum1 => 0,
                                         childnum2 => 0,
                                         type      => 'TEXT',
                                         level     => 'TYPICAL',
                                         section   => 'ALL');
end;
/

print l_report

COMPARE_EXPLAIN

The COMPARE_EXPLAIN function was introduced in Oracle 21c, but is undocumented at present.

dbms_xplan.compare_explain(
    statement_id1 in varchar2 default null,
    statement_id2 in varchar2 default null,
    plan_id1      in number   default null,
    plan_id2      in number   default null,
    type          in varchar2 := 'TEXT',
    level         in varchar2 := 'TYPICAL',
    section       in varchar2 := 'ALL')
  return clob;

To use this function we must first run EXPLAIN PLAN for each statement.

explain plan set statement_id = 'emp1' for
select * from emp where empno = 7369;

explain plan set statement_id = 'emp2' for
select /*+ full(emp) */ * from emp where empno = 7369;

In the following examples use COMPARE_EXPLAIN to compare the two plans using the STATEMENT_IDs. We've included the simple and verbose version of the syntax, which result in the same report.

var l_report clob;

-- Simple
begin
  :l_report := dbms_xplan.compare_explain('emp1','emp2');
end;
/

-- Verbose
begin
  :l_report := dbms_xplan.compare_explain(statement_id1 => 'emp1',
                                          statement_id2 => 'emp2',
                                          plan_id1      => null,
                                          plan_id2      => null,
                                          type          => 'TEXT',
                                          level         => 'TYPICAL',
                                          section       => 'ALL');
end;
/

print l_report

Output

The TYPE, LEVEL and SECTION parameters allow the output to be tailored to your needs. The allowable values are shown below.

The default output for all the calls in the above examples look like this. It includes the details of the statements, including the execution plans, and a comparison report which identifies the differences.

L_REPORT
--------------------------------------------------------------------------------

COMPARE PLANS REPORT
--------------------------------------------------------------------------------
  Current user           : TESTUSER1
  Total number of plans  : 2
  Number of findings     : 1
--------------------------------------------------------------------------------

COMPARISON DETAILS
--------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : TESTUSER1
 Plan Table Name        : PLAN_TABLE
 Statement ID           : emp1
 Plan ID                : 1
 Plan Database Version  : 21.0.0.0
 Parsing Schema         : "TESTUSER1"
 SQL Text               : No SQL Text

Plan
-----------------------------
 Plan Hash Value  : 2949544139

---------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    1 |    87 |    1 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID | EMP    |    1 |    87 |    1 | 00:00:01 |
| * 2 |    INDEX UNIQUE SCAN          | PK_EMP |    1 |       |    1 | 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("EMPNO"=7369)

--------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : TESTUSER1
 Plan Table Name        : PLAN_TABLE
 Statement ID           : emp2
 Plan ID                : 2
 Plan Database Version  : 21.0.0.0
 Parsing Schema         : "TESTUSER1"
 SQL Text               : No SQL Text

Plan
-----------------------------
 Plan Hash Value  : 3956160932

---------------------------------------------------------------------
| Id  | Operation           | Name | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    1 |    87 |    2 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | EMP  |    1 |    87 |    2 | 00:00:01 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("EMPNO"=7369)

Comparison Results (1):
-----------------------------
 1. Query block SEL$1, Alias "EMP"@"SEL$1": Access path is different -
    reference plan: INDEX_RS_ASC (lines: 1, 2), current plan: FULL (line: 1).

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

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.