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

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 Sun May 02 1999 - 08:54:22 CDT