DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, June 30, 2016

buffer_busy_sample



column wait format a15
column address noprint
column piece noprint
break on wait

select /*+ ordered */ distinct
  w.wait,
  t.address,
  t.piece,
  translate(t.sql_text, chr(13), ' ') sql_text
from
  ( select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
    union
    select sid,
           p3||' on '||p1||'.'||p2  wait
    from   sys.v_$session_wait
    where  event = 'buffer busy waits'
  )  w,
  sys.v_$session  s,
  sys.v_$sqltext  t
where
  s.sid = w.sid and
  t.address = s.sql_address
order by
  w.wait,
  t.address,
  t.piece
/




No comments: