Re: How can shared_pool be too large? was Re: slowing down after a few days

Hi Andrey,

This is in fact a rather common performance problem. If the shared pool is too large, it is possible for the low-order freelists to become very long, which causes the shared pool latch to be held for a long time. This results in a very poor hit rate on that latch.

There are several answers on this matter on my web site (1st URL below), and also a script to report the lengths of the shared pool free lists. For more information on the internals of the shared pool, please see chapter 6 of my book "Oracle Internal Services" (2nd URL below).

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/

On Tue, 07 Dec 1999 14:52:58 GMT, andreyNSPAM_at_bookexchange.net (NetComrade) wrote:

>On Tue, 07 Dec 1999 12:15:44 GMT, steve.adams_at_ixora.com.au (Steve
>Adams) wrote:
>
>>Hello Peter,
>>
>>There are lots of possibilities. One common cause of these symptoms is
>>having too large a shared pool. Performance is great until the shared
>>pool gets full and degrades in proportion with activity thereafter.
>>You can monitor the 'free memory' statistic in V$SGASTAT to check out
>>this possibility.
>>
>>Regards,
>>Steve Adams
>>http://www.ixora.com.au/
>>http://www.oreilly.com/catalog/orinternals/
>>http://www.christianity.com.au/
>>
>>
>>On Mon, 6 Dec 1999 12:43:55 +0100, "Peter Vyvey"
>><peter.vyvey_at_skynet.be> wrote:
>>
>>>Hello,
>>>we have a strange problem with an application having the same workload every
>>>day, slowing down after a few days. A stop/start of the database resolves
>>>the problem.
>>>
>>>Why is this happening ??????
>>>
>>>
>>>Thanks
>>>
>>>peter_vyvey_at_hotmail.com
>
>---------------
>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
>AOL: NetComrade ICQ: 11340726 remove NSPAM to email
>
Received on Tue Dec 07 1999 - 14:22:50 CST