Re: query for waiters queue

From: John D Parker <orclwzrd_at_yahoo.com>
Date: Fri, 18 Jan 2013 07:01:39 -0800 (PST)
Message-ID: <1358521299.71569.YahooMailNeo_at_web163102.mail.bf1.yahoo.com>

here's the query I've been using on 11.2.0.(can't remember) it's been working well for tracking down "enq: TX - row lock contention" wait trees.

col xx for a20
col event for a35
col machine for a20
col username for a10
set lines 100 trim on trims on pages 50000 SELECT RPAD('+', LEVEL ,'-') || 'i '||instance||' p '||osid||' s '||d.sid xx,        blocker_sid,  in_wait_secs, wait_event_text event,        machine,type,b.status,b.username, --       c.sql_text,
       b.sql_id, b.prev_sql_id
FROM          v$wait_chains d, gv$process a,gv$session b,gv$sqlarea c where paddr = addr and a.inst_id = b.inst_id and d.sid = b.sid and d.instance = b.inst_id and b.sql_id = c.sql_id(+) and b.inst_id = c.inst_id(+)

CONNECT BY     PRIOR d.sid = d.blocker_sid
           AND PRIOR d.sess_serial# = d.blocker_sess_serial#
           AND PRIOR d.INSTANCE = d.blocker_instance

START WITH blocker_is_valid = 'FALSE'
/

---

 From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> To: oracle-l <oracle-l_at_freelists.org> Sent: Friday, January 18, 2013 2:30 AM
Subject: Re: query for waiters queue  

I've just tried a simple test on an 11.2.0.3 instance. Get 3 sessions to lock a table in exclusive mode - one gets it, the other two queue.
The query returned no rows.  In fact v$wait_chain held no rows.

I haven't checked the documentation yet, but the waits captured by v$wait_chain may include all the common waits we might like.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "John D Parker" <orclwzrd_at_yahoo.com> To: "Sriram Kumar" <k.sriramkumar_at_gmail.com> Cc: "oracle-l" <oracle-l_at_freelists.org> Sent: Wednesday, January 16, 2013 6:32 PM Subject: Re: query for waiters queue

| It appears that you win the prize!
|
| ________________________________
| From: Sriram Kumar <k.sriramkumar_at_gmail.com>
| To: orclwzrd_at_yahoo.com
| Cc: oracle-l <oracle-l_at_freelists.org>
| Sent: Wednesday, January 9, 2013 7:51 PM
| Subject: Re: query for waiters queue
|
|
| Hi,
|
| you can look at the following script by Guy Harrison. Displays the chain
of locks
|
| http://guyh.textdriven.com/OPSGSamples/Ch15/wait_chains.sql
|
| best regards
|
| sriram kumar
|
|
|
| On Thu, Jan 10, 2013 at 12:27 AM, John D Parker <orclwzrd_at_yahoo.com>
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
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 18 2013 - 16:01:39 CET