v$sql_plan format

set lines 121
set pages 999
col sql_text format a80
select sql_text from
v$sqltext_with_newlines
where hash_value=4041323804
order by piece;
set heading off
select ‘——————————————————————————–‘ from dual
union all
select ‘| Operation                                                    | PHV/Object Name     | Rows  | Bytes| Cost   |’ as "Optimizer Plan:" from dual
union all
select ‘——————————————————————————–‘ from dual
union all
select *
from (select
rpad(‘|’||substr(lpad(‘ ‘,1*(depth-1))||operation||
decode(options, null,”,’ ‘||options), 1, 62), 63, ‘ ‘)||’|’||
rpad(decode(id, 0, ‘—– ‘||to_char(hash_value)||’ —–‘
, substr(decode(substr(object_name, 1, 7), ‘SYS_LE_’, null, object_name)
||’ ‘,1, 20)), 21, ‘ ‘)||’|’||
lpad(decode(cardinality,null,’ ‘,
decode(sign(cardinality-10000), -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, ‘ ‘) || ‘|’ as "Explain plan"
from v$sql_plan
where hash_value = 3918835632)
union all
select ‘——————————————————————————–‘ from dual;

发表评论

电子邮件地址不会被公开。 必填项已用*标注