Search notes:

Oracle: DBMS_XPLAN.COMPARE_PLANS

Create a simple test table:

drop   table  tq84_tab;
create table tq84_tab (
    id  number,
    val varchar2(20),
    constraint tq84_tab_pk primary key(id)
);

Create a table to store the result of dbms_xplan.compare_plans to:

create table tq84_compared_plans(result clob);

Create two plans, one of which uses a hint that the primary key index is not used:

explain plan
   set statement_id = 'without hint'
for
   select * from tq84_tab where id = 5;
   

explain plan
   set statement_id = 'with hint'
for
   select /*+ no_index(t) */ * from tq84_tab t where id = 5;

Compare the two plans:

declare
   diff clob;
   
   plan_1 plan_table_object := plan_table_object('SYS', 'PLAN_TABLE$', 'without hint', null);
   plan_2 plan_table_object := plan_table_object('SYS', 'PLAN_TABLE$', 'with hint'   , null);
begin

   diff := dbms_xplan.compare_plans(
      plan_1,
      plan_object_list(plan_2)
   );
   
   delete from tq84_compared_plans;
   insert into tq84_compared_plans values(diff);

 --  dbms_output.put_line(diff);

end;
/

Get the result

select * from tq84_compared_plans;

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1761496867, '216.73.216.115', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/installed/packages/dbms/xplan/api/compare_plans/index(85): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

Oracle: DBMS_XPLAN.COMPARE_PLANS

See also