Re: query for waiters queue

From: Sriram Kumar <k.sriramkumar_at_gmail.com>
Date: Mon, 21 Jan 2013 11:28:11 +0530
Message-ID: <CAEq4C0cYTp244V_eKWhaS_s86mQcD8tK+t4gyaG+y7vg+WaiBA_at_mail.gmail.com>

Hi Jonathan,
Yes. tested in 11.2.0.2, 11.2.0.3 and found a similar behaviour as reported by you. Blocking TM locks not captured by v$WAIT_CHAINS

Cross checked the documentation and it states V$WAIT_CHAINS is intended to capture all blocked sessions but ends up capturing only blocking TX locks and not blocking TM locks as tested.

V$WAIT_CHAINS is built on top of X$KSDHNG_CHAIN and from what i understand , this view is populated by DIAG process every 3 seconds for local hang analyse information and every10 seconds for RAC.

I would read the missing TM locks behaviour as a bug. Not sure how others would read this

interestingly, one more observation I had and is new learning to me and could be useful to others

Session 1. Lock table in exclusive mode
Session 2. Update a row in the table
Session 3. Update the same row that is locked in session 2

We will see session 2 and 3 waiting on Enqueue TM Contention. Now issue a commit or rollback on session 1. Session 3 would still show a TM contention. I was expecting a TX Enqueue when the first session completed but Oracle decides to keeps the old TM lock mode as the blocking mode.

Thanks again got to learn 2 new items today

best regards

sriram kumar

On Fri, Jan 18, 2013 at 2:00 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
>
> 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 Mon Jan 21 2013 - 06:58:11 CET