8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 26ai | 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.