Re: Cache buffer chains latch bug (8.1.7.2)

None of the below was the real issue. To quickly recap the original thread..

The application is a third party app, that inserts rows into a table to facilitate what is the main query of this app. It then takes those rows from that table and joins them to a 37 million row table.

The resulting range scan on the index of that 37 million row table was the contention point (the root block of the index). There are about 100 sessions possibly doing this query at the same time.

That query has been a problem since version 7.3.4, however the move from 8.0.6 to 8.1.7 brought it to its knees without a single application change.

Oracle did accept it as a bug, and did produce that one off patch.

We never did get the complete problem out of oracle, but it seems that they were pinning that block more than in the older versions (which I think Jonathon Lewis mentioned in that earlier thread.).

Just as another followup we have been on 8.1.7 with this 'production/test' database now for quite a few weeks, and have not had one single problem with it. Now we just have to convince managment to let us upgrade the real production servers again.

Scott

"andrew" <andrew_at_info.nl> wrote in
<3c453535$0$218$4d4ebb8e_at_news.nl.uu.net>:

>Hi,
>even though I didn't read the origin of this thread I can add
>some of my own experience about this:
>cache buffer chain latch contention may be due to lots of clones
>of the same block (because of consitent reads) in the buffer cache.
>This can happen for instance when there are many concurrent sessions
>doing full table scans or large index range scans using identical
>queries, like a webapplication accessing an oracle database that
>has many concurrent active sessions.
>When this latch really becomes the bottleneck try to rewrite
>these expensive queries so they will access less blocks. Also reducing the
>buffer cache size or putting the tables and indices, of which there are
>many clones in the buffercache, in the KEEP bufferpool may help.
>Logical reads aren't always that cheap!
>
>Andre van Winssen
>
>"Andrew Mobbs" <andrewm_at_chiark.greenend.org.uk> wrote in message
>news:l0E*7grep_at_news.chiark.greenend.org.uk...
>> Scott Gamble <zifnab_at_nospam.reddragon.org> wrote:
>> >Oracle Version = 8.1.7.2.1
>> >OS = Tru64 5.1 (might be 5.0 escapes me at the moment)
>> >
>> >Just wanted to keep those intersted in the cache buffer chains latch
>> >bug that was present in 8.1.7.1 and 8.1.7.2 that I had posted about a
>> >couple of times.
>>
>> Thanks for the update, I'm still very interested. I hope we see the same
>> success (when I finally get a chance to try - the test has been pushed
>> back a little).
>>
>> --
>> Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
>
>
>
Received on Thu Jan 17 2002 - 12:29:49 CST