Re: shared pool latch time out exceeding 40% HELP!!!

This was a while back, but in an Oracle 7 Performance Tuning class, the instructor said Oracle recommended setting the spin_count, on HP-UX only, to 1,000,000 to solve latch contention.

You might give that a try.

Steve

Kevin Brand wrote:

> Do you have the same problem just after instance start-up?
>
> If you do not, then you may be experiencing shared_pool free list
> fragmentation, which can cause really bad latch contention and subsequently
> slow response across the board.
>
> Try flushing the shared pool when you have lots of contention as a test. If
> contention goes down suddenly, then you do have this condition.
>
> -Kevin
> Andrew Babb wrote in message <372A5FC0.A274EBED_at_mail.com>...
> >Hi,
> >
> >This is an HP-UX specific comment, as far as I know. We were having bad
> latch
> >contention, and we ended up having the SPIN_COUNT set to 10,000 on an HP-UX
> >v10.20 and Oracle7 v7.3.4 database configuration. Ok, I know that you have
> >moved on with your configuration (11 and 8.0.5), but you might want to look
> at
> >possibly running a test with a high SPIN_COUNT.
> >
> >Rgds
> >Andrew
> >
> >Kunihito Ishibashi wrote:
> >
> >> upon doing further investigation of the oracle db problem that we
> >> wereexperiencing
> >> (cpu usage exceeding 100%), we discovered that the db isnot successfully
> >> obtaining the "latch" necessary to perform a transaction
> >> (in trying to obtain the latch, oracle goes through an excessive number
> >> ofloops).
> >>
> >> with the spin count set to 2,000 and using oracle's estat/bstat tool,
> >> we obtained the below results. according to oracle's documentation, the
> >> xxx latch time out
> >> statistics should not exceed 1%. however, in our tests,the shared pool
> >> latch time out percentage
> >> reached approximately 40% (out of10 million transaction, 3.9 million of
> the
> >> transactions failed).
> >> referring once again to oracle's documentation, it is noted that
> increasing
> >> the shared pool
> >> size to 300000000 its current value should improve performance
> >> (in other words, reduce the shared pool latch time out percentage to a
> more
> >> reasonable percentage).
> >> however, making such changes did not yield any improvement.
> >>
> >> we are wondering whether this is a problem with the HP-UX B.11.00 or with
> >> oracle8.0.5.1.0.
> >>
> >> any feedback is appreciated. thank you...
> >>
> >> xxx LATCH TIMEOUT (oracle's estat/bstat):
> >> ROW_CACHE_OBJECT 5%
> >> SHARD_POOL 40%
> >> LIBRARY_CACHE 12%
> >> REDO_ALLOCATION 0.09%
> >> REDO_COPY 0.05%
> >>
> >> SERVER SPEC:
> >> HP-UX k580a B.11.00 E 9000/800 (th) 32bit
> >> CPU PA-8200 x 6
> >> MEMORY 2GB
> >> DISK EMC SYMMETRIX MODEL3430 (5GB MEMORY/800GB DISK)
> >> EMC POWERPATH RELEASE 1.3
> >> ORACLE RELEASE 8.0.5.1.0
> >
Received on Mon May 03 1999 - 00:06:18 CDT