Re: 8.1.7 cache buffer chains contention

On Fri, 24 Aug 2001 16:44:39 GMT, zifnab_at_NOSPAM.reddragon.org (Scott Gamble) wrote:

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

Interesting. Is the replication active when this happens? If you're using ORACLE replication (I think you might, from the init.ora), then it might be worth spending some time looking into its schedule and make sure it's not conflicting with heavy use.

I'm with Jonathan in thinking this might be a specific table/SQL problem. As in: concentrate your effort in spreading the load on the table/index in question - partitioning being my first option here. I know, it's a pain. And the choices for partitioning would need some testing too. If you suss the index is the main cause of the problem, might be worth trying to partition it first?

I've got a funny feeling this is one of those things that crop up as a result of a bottleneck being removed somewhere else. I've had quite a few instances of similar problems in other upgrades: ORACLE improves one area of its mem/CPU/disk management, as a result the load balance in my system changes completely and a previously undetected bottleneck now becomes the main problem.

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

if you using replication, then ignore my strangeness at the first. Second might be worth checking.

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

I find that increase in dict cache quite abnormal. There must be something else causing it, the cache contention wouldn't be the cause. I'm just concerned if it is a consequence or a manifestation of some hidden cause. Sorry if that doesn't make sense, but I can't think of another way of saying it.

As I said before, please keep us posted on outcome. Very interesting problem. Whatever the solution ends up being, I think it would be educational for all of us to know how things go.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Fri Aug 24 2001 - 22:08:55 CDT