Re: Event 'db file seq. read' stuck for hours on the same file#/block# ?

"Spendius" <spendius_at_muchomail.com> wrote in message news:aba30b75.0503010434.623c582a_at_posting.google.com...
> "Haximus" <e_at_t.me> wrote...
>>
>> Just a thought... latch contention? 'db file seq. read' is for control
>> file
>> and datafile header access, a latch must be required to access these
>> areas,
>> the process might be requesting one in willing-to-wait mode, not getting
>> it
>> and retrying... retrying... etc
> What is funny is that I either get this 'db file seq. read' event
> or 'latch free' on the same number (106 = library cache) with the
> number of tries staying at 0 (ZERO), for hours too, until I kill
> the session. But 9 times/10 we get the db file sequential read wait.
>
> I've dbverified the file returned by this event, everything's fine,
> I've checked V$SESS_IO, from the moment the session stalls the
> values in this view no more change, I've launched the first SQL
> on V$LOCK someone gave me in the 1rst reply: I get no row (I've
> run anyway ALL my scripts related to locking, including catblock.sql
> to create all these relevant views, but it never returns me anything
> helpful). It *really* looks as if the session suddenly gets suspended,
> everything looks frozen.
>
> A 'tail -f' of 10046 trace file always stops at the same line:
> a SELECT FOR UPDATE statement. I'm beginning to suspect the data
> in the tables involved must cause this procedure to conk out
> (because the same module exists on 22 different databases -they
> are replicated to the same master- but it only fails on this one)...

Excessive amount of wait time on library cache latches? This can happen when massive amounts of SQL parsing takes place, causes lots of library cache latch demand. What does the entire SELECT look like.... are there literals embedded in it that would cause it to be reparsed every time, instead of reused from the library cache? Received on Tue Mar 01 2005 - 10:22:48 CST