Re: 8.1.7 cache buffer chains contention

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Fri, 24 Aug 2001 15:10:17 GMT
Message-ID: <3b866529.17385942@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?

This was weird too in the SGA breakdown:

shared pool dictionary cache                940,432      1,178,520

238,088
None of the others changed abnormally, just this one. Did you start the statspack shortly after the startup of the database or before start of daily peak usage?

>
> 2) changing the application to not hit that index so hard. But this
> to me implies a couple of things, you either stop making the call
> as often (dont think this is a choice, as it needs up to the minute
> prices), or somehow change the index/query to not need to read so
> many blocks from the index.

if 3rd party, that's out of the question.

If I was running something like this, I'd be killing ORACLE to give me the ability to partition my buffer cache by tablespace. Long overdue and would go a long way in helping the size and type of database and app you're running. But try and make them understand...

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 Fri Aug 24 2001 - 10:10:17 CDT