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;