8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- DBMS_XPLAN : Compare Execution Plans in Oracle Database 19c and 21c
- DBMS_XPLAN : Display Execution Plans
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.
- 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.
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:
- DBMS_XPLAN (documentation)
- DBMS_XPLAN : Compare Execution Plans in Oracle Database 19c and 21c
- DBMS_XPLAN : Display Execution Plans
Hope this helps. Regards Tim...