Re: Strange session blocking issue

From: fairlie rego <>
Date: Fri, 21 Jan 2011 17:06:34 -0800 (PST)
Message-ID: <>

I have experienced this issue quite often

Queries on v$sql_bind_capture will acquire a library cache latch and will block 
other sessions which require it to do a parse.

The situation is a lot worse when you have sql statements with 1000s of child 

So you should lock down access to these views. i.e your colleague should not 
access them...

Access to v$sqlstats can perhaps be given since it is a latchless view

Fairlie Rego
Senior Oracle Consultant
M: +61 402 792 405

From: Guillermo Alan Bort <>
To: Martin Berger <>
Cc: oracle-l-freelists <>
Sent: Sat, 22 January, 2011 11:40:34 AM
Subject: Re: Strange session blocking issue

Well, we got an EM alert saying SESSION x (I don't remember the session number) 
is blocking 1153 sessions...

Apparently it was a latch contention...


On Fri, Jan 21, 2011 at 6:15 PM, Martin Berger <> 

>can you specify 'blocking'? 
>In general, as v$ views are accessing SGA data more or less directly, you can 
>need a latch so the x$ 'tables' are not changed during access. Of course that 
>can take a while. 
>Where came the alert from?
>Am 21.01.2011 um 22:01 schrieb Guillermo Alan Bort:
>A colleague was running this query:
>>wheresql_id in(
>>And we got an alert that he was blocking other sessions.
>>Anyone ever experienced something like this?

Received on Fri Jan 21 2011 - 19:06:34 CST

Original text of this message