plan_order.sql
Explain planの結果を実行順に表示する
code:SQL
-- Display the execution plan tree in the order of execution.
-- called from "showplan.sql"
set autot off
set lines 400
set pages 1000
col Operation for a70
col Name for a35
col QBNAME heading 'Query Block Name / Object Alias' for a40
col COST1 heading "Cost" for a11
col ROWS1 heading "Rows" for a11
col BYTES1 heading "Bytes" for a10
col PRED for a4
col Pstart for a20
col Pstop for a20
col PRED_INFO heading "Predicate Information" for a104
col ID for 9999
col SEQ for a7 heading "Seq-ID"
--set sqlformat ansiconsole
select
trim(to_char(rownum,'900'))||'-'||trim(to_char(ID,'900')) SEQ
--,ID
--,POSITION
,PRED
,"Operation"
,"Name"
,QBNAME
,ROWS1
,BYTES1
,COST1
,"Srch Cols"
,"Pstart"
,"Pstop"
,PRED_INFO
from (
select
ID
,PRED
,POSITION
,"Operation"
,"Name"
,QBNAME
,ROWS1
,BYTES1
,COST1
,"Srch Cols"
,"Pstart"
,"Pstop"
,PRED_INFO
from (
select distinct
rownum NO
,ID
,POSITION
,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"
,CARDINALITY "Rows"
,case when CARDINALITY < 100000 then to_char(floor(CARDINALITY),'99999')
when CARDINALITY < 100000000 then to_char(floor(CARDINALITY/1000),'99999')||'K'
when CARDINALITY < 100000000000 then to_char(floor(CARDINALITY/1000000),'99999')||'M'
when CARDINALITY >= 100000000000 then to_char(floor(CARDINALITY/1000000000),'99999999')||'G'
else null
end ROWS1
,case when QBLOCK_NAME is not null and OBJECT_ALIAS is not null then substr(rpad(QBLOCK_NAME,14,' ') ||'/ '|| replace(OBJECT_ALIAS,'"'),1,40)
when QBLOCK_NAME is not null and OBJECT_ALIAS is null then QBLOCK_NAME
else null
end QBNAME
,BYTES "Bytes"
,case when BYTES < 10000 then to_char(floor(BYTES),'9999')
when BYTES < 10000000 then to_char(floor(BYTES/1000),'9999')||'K'
when BYTES < 10000000000 then to_char(floor(BYTES/1000000),'9999')||'M'
when BYTES >= 10000000000 then to_char(floor(BYTES/1000000000),'9999999')||'G'
else null
end BYTES1
,COST "Cost"
,case when COST < 10000 then to_char(floor(COST),'9999')
when COST < 10000000 then to_char(floor(COST/1000),'9999')||'K'
when COST < 10000000000 then to_char(floor(COST/1000000),'9999')||'M'
when COST >= 10000000000 then to_char(floor(COST/1000000000),'9999999')||'G'
else null
end COST1
,SEARCH_COLUMNS "Srch Cols"
,PARTITION_START "Pstart"
,PARTITION_STOP "Pstop"
,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 * from plan_table
where TIMESTAMP = (select max(TIMESTAMP) from plan_table)
)
start with PARENT_ID is null
connect by prior ID = PARENT_ID
order siblings by ID desc
)
order by NO desc
)
;
--set sqlformat default