showspplan.sql
PLAN HASH VALUEを指定して実行計画を表示する
code:SQL
define PHV=&1
set autot off
set lines 400
set pages 1000
col Operation for a55
col Name for a25
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"
select
PLAN_HASH_VALUE
,SNAP_ID
,ID
,lpad(' ',DEPTH) || OPERATION ||' '|| OPTIONS "Operation"
,OBJECT_NAME "Name"
,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
,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
,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"
from STATS$SQL_PLAN
where PLAN_HASH_VALUE = &PHV
order by
PLAN_HASH_VALUE
,SNAP_ID
,ID
;
set lines 80
set pages 100