Re: query for waiters queue
- From: Sriram Kumar <k.sriramkumar@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Mon, 21 Jan 2013 11:28:11 +0530
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@xxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxx>
> To: "Sriram Kumar" <k.sriramkumar@xxxxxxxxx>
> Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxx>
> | To: orclwzrd@xxxxxxxxx
> | Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
> | 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@xxxxxxxxx>
> 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
> | >
> | >--
> | >//www.freelists.org/webpage/oracle-l
> | >
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
