📋Steps in this guide1/6

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.

oracle 21cconfigurationintermediate

by OracleDba

13 views

Setup

We create a test user. We connect to the test user and create the table. If you are using SQL*Plus or SQLcl, these setting may be useful.

Code/Command (click line numbers to comment):

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;

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;

set linesize 120 pagesize 1000 long 1000000

Example Queries

We query the table for a specific value of the column. Since the column is the primary key column, we see the primary key index is used to return the data. We repeat the query, but this time force a full table scan using the optimizer hint.

Code/Command (click line numbers to comment):

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>

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 function was introduced in Oracle 19c. We use the object to return types for use as parameters. The constructor method for this object accepts a and attributes. So using the s from the two queries we ran before, and assuming 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.

Code/Command (click line numbers to comment):

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;

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

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 function was introduced in Oracle 21c, but is undocumented at present. In the following examples use to compare the two plans using the s. We've included the simple and verbose version of the syntax, which result in the same report.

Code/Command (click line numbers to comment):

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;

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 function was introduced in Oracle 21c, but is undocumented at present. To use this function we must first run for each statement. In the following examples use to compare the two plans using the s. We've included the simple and verbose version of the syntax, which result in the same report.

Code/Command (click line numbers to comment):

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;

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;

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 , and parameters allow the output to be tailored to your needs. The allowable values are shown below. - TYPE : TEXT, HTML, XML - LEVEL : BASIC, TYPICAL, ALL - SECTION : SUMMARY, FINDINGS, PLANS, INFORMATION, ERRORS, ALL 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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!