Re: Strange session blocking issue

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 
cursors

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
http://el-caro.blogspot.com/
M: +61 402 792 405
 




________________________________
From: Guillermo Alan Bort <cicciuxdba_at_gmail.com>
To: Martin Berger <martin.a.berger_at_gmail.com>
Cc: oracle-l-freelists <oracle-l_at_freelists.org>
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...

weird
Alan.-



On Fri, Jan 21, 2011 at 6:15 PM, Martin Berger <martin.a.berger_at_gmail.com> 
wrote:

Alan, 
> 
>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?
>
>
>Martin 
>
>
>
>
>Am 21.01.2011 um 22:01 schrieb Guillermo Alan Bort:
>
>
>A colleague was running this query:
>>
>>
>>select*
>>fromv$sql_bind_capture
>>wheresql_id in(
>>'b501tjnwb31zw',
>>'d88z78qwf8rn6',
>>'d0w015tn4pc1j',
>>'9rwzxss9t1quz'
>>);
>>
>>
>>
>>And we got an alert that he was blocking other sessions.
>>
>>Anyone ever experienced something like this?
>>
>>Alan.-
>>
>



      

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