写了一个抓取造成等待事件的SQL,里面应用到DECODE.记录一下。[@more@]
select /*+ ORDERED */
sql_text, hash_value, address
from v$sqltext c,
(select sql_hash_value, sql_address
from (select DECODE(sql_hash_value,
0,
prev_hash_value,
sql_hash_value) sql_hash_value,
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) sql_address
from v$session
--where status = 'ACTIVE'
--and username is not null
where sid in (select sid
from (select sid,
event,
p1raw,
p2raw,
p2,
p3raw,
p3,
a.WAIT_TIME,
a.SECONDS_IN_WAIT
from v$session_wait a
where event = 'latch free')))) b
where c.HASH_VALUE = b.sql_hash_value
and c.ADDRESS=b.sql_address
order by c.PIECE
sql_text, hash_value, address
from v$sqltext c,
(select sql_hash_value, sql_address
from (select DECODE(sql_hash_value,
0,
prev_hash_value,
sql_hash_value) sql_hash_value,
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) sql_address
from v$session
--where status = 'ACTIVE'
--and username is not null
where sid in (select sid
from (select sid,
event,
p1raw,
p2raw,
p2,
p3raw,
p3,
a.WAIT_TIME,
a.SECONDS_IN_WAIT
from v$session_wait a
where event = 'latch free')))) b
where c.HASH_VALUE = b.sql_hash_value
and c.ADDRESS=b.sql_address
order by c.PIECE
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28227905/viewspace-1060750/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28227905/viewspace-1060750/