Re: Strange session blocking issue
- From: Guillermo Alan Bort <cicciuxdba@xxxxxxxxx>
- To: fairlie rego <fairlie_r@xxxxxxxxx>
- Date: Fri, 21 Jan 2011 22:58:19 -0300
Fairlie,
That is what I figured happened, is this the expected behaviour? is this
documented somewhere?
thanks for your reply
Alan.-
On Fri, Jan 21, 2011 at 10:06 PM, fairlie rego <fairlie_r@xxxxxxxxx> wrote:
> 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@xxxxxxxxx>
> *To:* Martin Berger <martin.a.berger@xxxxxxxxx>
> *Cc:* oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
> *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@xxxxxxxxx>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 *
>>
>> from v$sql_bind_capture
>>
>> where sql_id in (
>>
>> 'b501tjnwb31zw',
>>
>> 'd88z78qwf8rn6',
>>
>> 'd0w015tn4pc1j',
>>
>> '9rwzxss9t1quz'
>>
>> );
>>
>>
>>
>>
>> And we got an alert that he was blocking other sessions.
>>
>> Anyone ever experienced something like this?
>>
>> Alan.-
>>
>>
>>
>
>
>
Other related posts:
