--  FILE:	xpln.sql
--
--  AUTHOR:     Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--              Copyright (C) 2004 AppsDBA Consulting
--  DATE:	Unknown
--
--  DESCRIPTION:
--		Script to output an explain plan for a given SQL statement.
--
--
--  REQUIREMENTS:
--              Requires access to a plan table
--              ($ORACLE_HOME/rdbms/admin/utlxplan.sql).
--
--
--  MODIFICATIONS:
--
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
SET LINESIZE 150;
--
DELETE FROM plan_table;
COMMIT;
--
explain plan
set statement_id = 'xpln'
for
-----------------------------------------------------------------------------
-- Place SQL here
-----------------------------------------------------------------------------


/
-----------------------------------------------------------------------------
-- End of SQL
-----------------------------------------------------------------------------
/* This script will display the results of Explain Plan in a nice format */
SET PAGESIZE 9999;
SET LINESIZE 120;
--
DEFINE STMT_ID = 'xpln';
--
select '| Operation                                     |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |'  
    as "Plan Table" from dual
union all
select '------------------------------------------------------------------------------------------'
 from dual
union all
select * 
  from 
(select /*+ no_merge */
       rpad('| '||substr(lpad(' ',1*(level-1))||operation||
            decode(options, null,'',' '||options), 1, 47), 48, ' ')||'|'||
       rpad(substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
                   ||' ',1, 19), 20, ' ')||'|'||
       lpad(decode(cardinality,null,'  ',
                decode(sign(cardinality-1000), -1, cardinality||' ', 
                decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', 
                decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', 
                       trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
       lpad(decode(bytes,null,' ',
                decode(sign(bytes-1024), -1, bytes||' ', 
                decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', 
                decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', 
                       trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
       lpad(decode(cost,null,' ',
                decode(sign(cost-10000000), -1, cost||' ', 
                decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', 
                       trunc(cost/1000000000)||'G'))), 8, ' ') || '|' ||
       lpad(decode(partition_start, 'ROW LOCATION', 'ROWID', 
            decode(partition_start, 'KEY', 'KEY', decode(partition_start, 
            'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6), 
            'NUMBER', substr(substr(partition_start, 8, 10), 1, 
            length(substr(partition_start, 8, 10))-1), 
            decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' ||
       lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L', 
          decode(partition_stop, 'KEY', 'KEY', decode(partition_stop, 
          'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6), 
          'NUMBER', substr(substr(partition_stop, 8, 10), 1, 
          length(substr(partition_stop, 8, 10))-1), 
          decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|' as "Explain plan"
from plan_table
start with id=0 and timestamp = (select max(timestamp) from plan_table 
                                 where id=0 and nvl(statement_id, ' ') not like 'SYS_LE%')
                and nvl(statement_id, ' ') not like 'SYS_LE%'
connect by (prior id = parent_id 
        and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
        and prior timestamp <= timestamp)
        or 
       (prior nvl(object_name, ' ') like 'SYS_LE%' and prior nvl(object_name, ' ') = nvl(statement_id, ' ')
        and id = 0 and prior timestamp <= timestamp)
order 
--siblings
 by id, position)
union all
select '------------------------------------------------------------------------------------------'
  from dual;
--
UNDEFINE STMT_ID;