aplans.sql
実行統計付実行計画を実行順に表示する
code:SQL
-- Display the execution plan tree in the order of execution.
-- called from "aplan.sql"
set lines 1000
col ID for 9999
col PRED for a4
col Operation for a57
col Name for a35
col Pstart for a13
col Pstop for a13
col A-Time for 9,990.00
col A-Rows for 9,999,999,990
col E-Rows for 9,999,999,990
col Starts for 9,999,999,990
col ROWS1 heading "A-Rows" for a9
col BUFFERS1 heading "Buffers" for a8
col READS1 heading "Reads" for a8
col WRITES1 heading "Writes" for a8
col QBLOCK_NAME heading "QB_NAME" for a13
col PRED_INFO heading "Predicate Information" for a104
col QBNAME heading 'Query Block Name / Object Alias' for a34
col SEQ for a7 heading "Seq-ID"
--set sqlformat ansiconsole
-- 実行順実行統計出力
select
trim(to_char(rownum,'900'))||'-'||trim(to_char(ID,'900')) SEQ
,PRED
,"Operation"
,"Name"
--,QBLOCK_NAME
,substr(QBNAME,1,33) QBNAME --折り返しが発生しないように指定文字数で切る
,"Starts"
--,"E-Rows"
,ROWS1 "A-Rows"
,"A-Time"
,BUFFERS1 "Buffers"
,READS1 "Reads"
--,WRITES1 "Writes"
,"Srch Cols"
,"Pstart"
,"Pstop"
,"PartID"
,PRED_INFO
from
(
select
ID
,PRED
,"Operation"
,"Name"
--,QBLOCK_NAME
,QBNAME
,"Starts"
--,"E-Rows"
,ROWS1
,"A-Time"
,BUFFERS1
,READS1
,WRITES1
,"Srch Cols"
,"Pstart"
,"Pstop"
,"PartID"
,PRED_INFO
from
(
select
rownum NO
,ID
,case when ACCESS_PREDICATES is not null and FILTER_PREDICATES is not null then 'ACFL'
when ACCESS_PREDICATES is not null and FILTER_PREDICATES is null then 'ACCS'
when ACCESS_PREDICATES is null and FILTER_PREDICATES is not null then 'FLTR'
else null
end PRED
,lpad(' ',DEPTH) || OPERATION ||' '|| OPTIONS "Operation"
,OBJECT_NAME "Name"
,QBLOCK_NAME
,case when QBLOCK_NAME is not null and OBJECT_ALIAS is not null then rpad(QBLOCK_NAME,14,' ') ||'/ '|| replace(OBJECT_ALIAS,'"')
when QBLOCK_NAME is not null and OBJECT_ALIAS is null then QBLOCK_NAME
else null
end QBNAME
,LAST_STARTS "Starts"
,nvl(CARDINALITY,1) * LAST_STARTS "E-Rows"
,case when LAST_OUTPUT_ROWS < 100000 then to_char(floor(LAST_OUTPUT_ROWS),'99999')
when LAST_OUTPUT_ROWS < 100000000 then to_char(floor(LAST_OUTPUT_ROWS/1000),'99999')||'K'
when LAST_OUTPUT_ROWS < 100000000000 then to_char(floor(LAST_OUTPUT_ROWS/1000000),'99999')||'M'
when LAST_OUTPUT_ROWS >= 100000000000 then to_char(floor(LAST_OUTPUT_ROWS/1000000000),'99999999')||'G'
else null
end ROWS1
,LAST_ELAPSED_TIME/1000000 "A-Time"
,case when LAST_CR_BUFFER_GETS < 10000 then to_char(floor(LAST_CR_BUFFER_GETS),'9999')
when LAST_CR_BUFFER_GETS < 10000000 then to_char(floor(LAST_CR_BUFFER_GETS/1000),'9999')||'K'
when LAST_CR_BUFFER_GETS < 10000000000 then to_char(floor(LAST_CR_BUFFER_GETS/1000000),'9999')||'M'
when LAST_CR_BUFFER_GETS >= 10000000000 then to_char(floor(LAST_CR_BUFFER_GETS/1000000000),'9999999')||'G'
else null
end BUFFERS1
,case when LAST_DISK_READS < 10000 then to_char(floor(LAST_DISK_READS),'9999')
when LAST_DISK_READS < 10000000 then to_char(floor(LAST_DISK_READS/1000),'9999')||'K'
when LAST_DISK_READS < 10000000000 then to_char(floor(LAST_DISK_READS/1000000),'9999')||'M'
when LAST_DISK_READS >= 10000000000 then to_char(floor(LAST_DISK_READS/1000000000),'9999999')||'G'
else null
end READS1
,case when LAST_DISK_WRITES < 10000 then to_char(floor(LAST_DISK_WRITES),'9999')
when LAST_DISK_WRITES < 10000000 then to_char(floor(LAST_DISK_WRITES/1000),'9999')||'K'
when LAST_DISK_WRITES < 10000000000 then to_char(floor(LAST_DISK_WRITES/1000000),'9999')||'M'
when LAST_DISK_WRITES >= 10000000000 then to_char(floor(LAST_DISK_WRITES/1000000000),'9999999')||'G'
else null
end WRITES1
,SEARCH_COLUMNS "Srch Cols"
--,COST
,PARTITION_START "Pstart"
,PARTITION_STOP "Pstop"
,PARTITION_ID "PartID"
,case when ACCESS_PREDICATES is not null and FILTER_PREDICATES is not null then 'AC- ' || substr(substr(replace(ACCESS_PREDICATES,'"'),1,80) || ' ; ' || 'FL- ' || substr(replace(FILTER_PREDICATES,'"'),1,100),1,100)
when ACCESS_PREDICATES is not null and FILTER_PREDICATES is null then 'AC- ' || substr(replace(ACCESS_PREDICATES,'"'),1,100)
when ACCESS_PREDICATES is null and FILTER_PREDICATES is not null then 'FL- ' || substr(replace(FILTER_PREDICATES,'"'),1,100)
else null
end PRED_INFO
from
(
select a.* from
V$SQL_PLAN_STATISTICS_ALL a
where SQL_ID = '&1'
and a.TIMESTAMP = (select max(TIMESTAMP) from V$SQL_PLAN_STATISTICS_ALL b where b.SQL_ID = a.SQL_ID)
and a.CHILD_NUMBER = '&2'
)
start with PARENT_ID is null
connect by prior ID = PARENT_ID
order siblings by ID desc
)
order by NO desc
)
;
--set sqlformat default