DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, March 17, 2016

Script to generate Execution Plan output in a detailed manner

Please change the below script as per your need.

set lines 150
set echo on
set pages 500

delete from  plan_table where statement_id='dba';
commit;

spool eplan11g
explain plan set statement_id='dba' for
Please give your select query here


select * from table(dbms_xplan.display());
spool off




For example:
------------------

set lines 150
set echo on
set pages 500

delete from  plan_table where statement_id='oracle';
commit;

spool eplan11g
explain plan set statement_id='oracle' for
SELECT p.table_name "Parent Table", c.table_name "Child Table",
       p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
       FROM user_constraints p
       JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
       AND c.constraint_type = 'R'
       AND p.table_name = UPPER('&table_name') and p.owner='ABC';


select * from table(dbms_xplan.display());
spool off

No comments: