Compare execution plan of two SQL statements in Oracle 19c

Compare execution plan of two SQL statements in Oracle

New feature of Oracle 19c (19.1) for compare the execution plan of two SQL statements.

Example to compare two sql statement execution plan
1. Execute the following statements for getting the SQL IDs

--Query 1
select count(*) from hr.employees a, hr.departments b where a.department_id = b.department_id and a.department_id > 100;
--Query 2
select count(*) from hr.employees a, hr.departments b where a.department_id = b.department_id and a.department_id = 100;

2. Get the SQL ID from this statements.

SET LINESIZE 120
COL SQL_ID FORMAT a20
COL SQL_TEXT FORMAT a60

SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE '%department%'
AND SQL_TEXT NOT LIKE '%SQL_TEXT%'
ORDER BY SQL_ID;

SQL_ID SQL_TEXT
-------------------- ------------------------------------------------------------
4gtfvmsmq822s select count(*) from hr.employees a, hr.departments b where
a.department_id = b.department_id and a.department_id = 100

gg66gxr1mukuh select count(*) from hr.employees a, hr.departments b where
a.department_id = b.department_id and a.department_id > 100

3. Login as hr user.

4. Get the SQL IDs from upper queries and put them below SQL Code and Run the code.

VARIABLE v_rep CLOB

BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('4gtfvmsmq822s', NULL),
compare_plan_list => plan_object_list(cursor_cache_object('gg66gxr1mukuh', NULL)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/

5. Check the execution plan and get the compare report.

SET PAGESIZE 50000
SET LONG 100000
SET LINESIZE 210
COLUMN report FORMAT a200

SELECT :v_rep REPORT FROM DUAL;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.