Re: 8.1.7 cache buffer chains contention

nsouto_at_optushome.com.au.nospam (Nuno Souto) wrote in <3b866529.17385942_at_news>:

>On Fri, 24 Aug 2001 13:32:06 GMT, zifnab_at_NOSPAM.reddragon.org (Scott
>Gamble) wrote:
>
>PMFJI
>
>>I put it up at http://www.reddragon.org/~zifnab/oracle/sp_6_11.lst
>
>thanks, very interesting stuff. 396000 buffers? that's a serious DB
>right there you got in your hands. Have you considered increasing the
>block size from 4K or is that something you don't want to consider?
>
>>
>> 1) Partition the table and index to spread the load out (multiple
>> root
>> blocks, not really application fix, but should help)
>
>You have one or two SQLs that seem to be the potential cause. Find
>out the tables/indexes used, then spread just those. That would be my
>first plan of attack. How big are tabs/indexes? Can you partition
>them?
>
>I've got a bell in the back of my mind ringing too: there was a
>metaclick note about some 8.1.7 releases having a problem with
>timed_statistics active and large numbers of buffers. u aware of
>this?
>
>On another note, init.ora:
>replication_dependency_tracki TRUE
>resource_limit TRUE
>Do you really need them?

Just a little update in case anyone is still interested. We were supposed to downgrade back to 8.0.6 a couple of weeks ago, but had a failure in doing that, and ended up restoring the cold backups from prior to the downgrade attempt ( one little view with an 8.1.7 dependency created by statspack doomed the downgrade)

Anyway the latch free contention has continued to the point where a batch process that we used to run in 44 hours has not completed in 7 days worth of processing.

Due to our having management telling us we would be doing a downgrade, we have had a machine to test that process on, and it has been back and forth between 8.1.7.1 and 8.0.6.1 repeatedly.

We created a test case to simulate the problem and have had mixed results on it. We can reproduce the 'hot block' in 8.1.7.1 and have found that in 8.0.6.1 for the same application and data that index block is not part of the hottest hash chains. However we have not been able to reproduce the performance problems that were evident in 8.1.7.1.

Oracle has been able to reproduce the 'hot block' being hot in 8.1.7 and not in 8.0.6.1. So no resolution as of yet, but it appears to be getting somewhere.

Scott Gamble

>
>This is an interesting one. Please keep us posted on developments.
>I love it when support tells us the problem is the application. Great
>help. Like, we're gonna dump it because they think it's rubbish?
>Yeah, right.
>
>
>Cheers
>Nuno Souto
>nsouto_at_optushome.com.au.nospam
>
Received on Thu Sep 06 2001 - 14:08:36 CDT