Re: 8.1.7 cache buffer chains contention

Scott Gamble wrote:
>
> 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
> >

Thanks for the update... I'll be interested in hearing what comes of this.

Cheers

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."