Compare the execution plans in Oracle 19c

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;
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply