oracle v$sql_plan周期,使用v$sql_plan查看执行计划

connect / as sysdba;

set lines 121

set pages 999

col sql_text format a80

select sql_text from

v$sqltext_with_newlines

where hash_value=364285806

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 = 364285806)

union all

select '--------------------------------------------------------------------------------' from dual;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19446/viewspace-220564/，如需转载，请注明出处，否则将追究法律责任。