Re: query for waiters queue

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 10 Jan 2013 10:11:27 +1000
Message-ID: <50EE072F.90307_at_tpg.com.au>

I used to use this (not sure if it still works in 11g though):

col ob    for a40       head "Object Name"
col lm    for a14       head "Locking|Mode"
col tm    for 9,999,990 head "Time (sec)"
col bl    for 9999      head "Blocking|Sid"
col ws    for 9999      head "Waiting|Sid"
col ser   for 99999     head "Waiting|Serial"
col wa    for a20       head "Waiter"

break on bl on ws on ser on wa on lg on ob

select /*+ all_rows */

        l1.sid             bl
      , l2.sid             ws
      , s2.serial#         ser
      , s2.username        wa
      , decode(l2.lmode, 0, 'None',
                         1, 'Null',
                         2, 'Row-S (SS)',
                         3, 'Row-X (SX)',
                         4, 'Share',
                         5, 'S/Row-X (SSX)',
                         6, 'Exclusive', to_char(l1.lmode)
               )  lm
      , o.name             ob
      , to_char(s2.logon_time, 'DD-Mon-YYYY, HH24:MI:SS')  lg
   from  v$lock     l2
    ,    v$lock     l1

    , v$session s2
    , sys.obj$ o

    where  o.obj#      = l1.id1
      and  s2.sid      = l2.sid
      and l1.type      = 'TM'
      and l2.type      = 'TM'
      and l1.id1       = l2.id1

   order by l1.sid, o.name, l2.sid
/

Cheers,
Tony

On 10/01/13 2:27 AM, John D Parker wrote:
> So I have all the classic queries for finding blockers and waiters. I keep running into the situation where I have one holder and 600 waiters. I clear off the blocker and then another one picks up and blocks. Is there a query that will give the current waiter queue? I see that Jonathan talks about a trace event to create a trace file but that's not particularly useful in my situation. Anyone have a query for this?
> Thanks in advance!
>
> John
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 10 2013 - 01:11:27 CET