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?
>

The buffer count is a bit high, but it never hurt us before :)

>>
>> 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?
>

The SQL's have always been bad they literally haven't changed since we purchased the app a few years ago and that is the puzzling part we know that there are inefficiances in the the very design of the tables/application however the move to 8.1.7.1 literally took us to a brick wall with this application. If something doesnt change there is no way we can upgrade and thats too bad, this application could surely use some of the 8.1.7 features (temp tables/tablespaces, and compressed indexes come to mind).

The tables are relatively small other than 2 big ones, however the query that is causing all the latch contention is against a 32M row table ( another problem that we have been asking the business/application folks about every few months but that hasn't changed with the upgrade).

Partitioning is possibly an option, just not sure we can convince the business to pay for that. This actually covers 2 databases (500 concurrent users each), the one that I posted is 12 replicated tables from the master for the sole purpose of handling SAP R/3 pricing RFC's.

The master is showing the same problems, though on a smaller scale it has other work that goes on as well.

>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?
>

Wasn't aware of that, looking right now.

>On another note, init.ora:
>replication_dependency_tracki TRUE
>resource_limit TRUE
>Do you really need them?
>
>

Not sure what they really are, let me take a look into 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?
>

The last week is a bit of a blur, but based on the times in the file it was in the middle of our peak usage when we had 100 sessions waiting on the same latch.

>
>>
>> 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.
>

Oracle has actually been fairly helpful on this, just haven't come up with any 'real' answers to the primary question which is why did 8.1.7.1 make this so much worse. There have been enough problems with 8.1.7.X regarding this, that they seem to be willing to say it still may be an Oracle problem and not only the apps fault.

>
>Cheers
>Nuno Souto
>nsouto_at_optushome.com.au.nospam
Received on Fri Aug 24 2001 - 11:44:39 CDT