SQL> SQL> SQL> SQL> / Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 2 | 270 | 2384 | | | | TABLE ACCESS BY INDEX ROW|STR_ITEM_ | 2 | 270 | 2384 | | | | INDEX RANGE SCAN |STR_ITEM_ | 2 | | 23 | | | -------------------------------------------------------------------------------- 6 rows selected. SQL> @E:\oracle\ora81\RDBMS\ADMIN\utlxpls.sql SQL> Rem SQL> Rem $Header: utlxpls.sql 05-jan-2000.22:08:44 bdagevil Exp $ SQL> Rem SQL> Rem utlxpls.sql SQL> Rem SQL> Rem Copyright (c) Oracle Corporation 1998, 1999, 2000. All Rights Reserved. SQL> Rem SQL> Rem NAME SQL> Rem utlxpls.sql - UTiLity eXPLain Serial plans SQL> Rem SQL> Rem DESCRIPTION SQL> Rem script utility to display the explain plan of the last explain plan SQL> Rem command. Do not display information related to Parallel Query SQL> Rem SQL> Rem NOTES SQL> Rem Assume that the PLAN_TABLE table has been created. The script SQL> Rem utlxplan.sql should be used to create that table SQL> Rem SQL> Rem To avoid lines from truncating or wrapping around: SQL> Rem 'set charwidth 80' in svrmgrl SQL> Rem 'set linesize 80' in SQL*Plus SQL> Rem SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem bdagevil 01/05/00 - add order-by to make it deterministic SQL> Rem kquinn 06/28/99 - 901272: Add missing semicolon SQL> Rem bdagevil 05/07/98 - Explain plan script for serial plans SQL> Rem bdagevil 05/07/98 - Created SQL> Rem SQL> SQL> SQL> Rem SQL> Rem Display last explain plan SQL> Rem SQL> select '| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |' as "Plan Table" from dual 2 union all 3 select '--------------------------------------------------------------------------------' from dual 4 union all 5 select * from 6 (select /*+ no_merge */ 7 rpad('| '||substr(lpad(' ',1*(level-1))||operation|| 8 decode(options, null,'',' '||options), 1, 27), 28, ' ')||'|'|| 9 rpad(substr(object_name||' ',1, 9), 10, ' ')||'|'|| 10 lpad(decode(cardinality,null,' ', 11 decode(sign(cardinality-1000), -1, cardinality||' ', 12 decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', 13 decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', 14 trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' || 15 lpad(decode(bytes,null,' ', 16 decode(sign(bytes-1024), -1, bytes||' ', 17 decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', 18 decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', 19 trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' || 20 lpad(decode(cost,null,' ', 21 decode(sign(cost-10000000), -1, cost||' ', 22 decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', 23 trunc(cost/1000000000)||'G'))), 8, ' ') || '|' || 24 lpad(decode(partition_start, 'ROW LOCATION', 'ROWID', 25 decode(partition_start, 'KEY', 'KEY', decode(partition_start, 26 'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6), 27 'NUMBER', substr(substr(partition_start, 8, 10), 1, 28 length(substr(partition_start, 8, 10))-1), 29 decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' || 30 lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L', 31 decode(partition_stop, 'KEY', 'KEY', decode(partition_stop, 32 'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6), 33 'NUMBER', substr(substr(partition_stop, 8, 10), 1, 34 length(substr(partition_stop, 8, 10))-1), 35 decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|' as "Explain plan" 36 from plan_table 37 start with id=0 and timestamp = (select max(timestamp) from plan_table 38 where id=0) 39 connect by prior id = parent_id 40 and prior nvl(statement_id, ' ') = nvl(statement_id, ' ') 41 and prior timestamp <= timestamp 42 order by id, position) 43 union all 44 select '--------------------------------------------------------------------------------' from dual; Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 2 | 270 | 2384 | | | | TABLE ACCESS BY INDEX ROW|STR_ITEM_ | 2 | 270 | 2384 | | | | INDEX RANGE SCAN |STR_ITEM_ | 2 | | 23 | | | -------------------------------------------------------------------------------- 6 rows selected. SQL> SPOOL OFF