How to compare two execution plans in Oracle
In Oracle 19c, Oracle introduced a new feature in the package DBMS_XPLAN that has a Compare_plans procedure which will provide the comparison display report with EXPLAIN PLAN
Script to compare two SQL ID execution plans
-- Declare the SQL Variable
VARIABLE v_rep CLOB
-- Compare the two sql id compare plan
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('13wegds7v4d6a', NULL),
compare_plan_list => plan_object_list(cursor_cache_object('890bdewey45s9', NULL)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
-- generate the report from sql
SET LINE 999 PAGE 999
SET LONG 100000
COLUMN report FORMAT a200
SELECT :v_rep as REPORT FROM DUAL;