Re: latch on shared_pool and non-sharable SQL

Actually reducing the size of the shared pool should help to improve performance, but fixing the application is the way to go (and sooner or later that will get done, believe me).

Anjo.

>
>Has anyone had experience tuning the shared pool in an environment where
>there is too much non-sharable SQL ( almost every non-PL/SQL call ) and so
>many users that contention for the shared_pool latch ( 38 on 7.3.4.3 ) is a
>major problem.
>
>The real problem here is the non-sharable SQL from the application, but it
>is so wide-spread, that fixing this will take a major effort and the vendor
>is not willing to do so.
>
>I've done the following:
>shared_pool=262144000 # 256M
>shared_pool_reserved_size=25600000 # 25M
>shared_pool_reserved_min_alloc=5000
>session_cached_cursors=50
>
>...and pinned all large packages, procedures, and triggers at startup.
>
>I've tuned down the redo, rollback, cache buffers chains ( LRU ) and others
>so that this one has really become the bottleneck. I consistently get sub
>50% cache hit on the shared_pool.
>
>I don't think increasing the size of the shared_pool will help much because
>nearly all SQL issued is unique, which means almost every statement
>generates a cache miss. An increase would probably just prolong the
filling
>up of the shared_pool.
>
>If you're familiar with it, the x$ksmlru table always has rows in it, which
>confirms the above. Usually, and especially after pinning all large
>objects, the most statements I see displaced is 8, but they are continually
>rolling over within the shared_pool.
>
>What else can I do to relieve contention for the shared_pool latch?
>
>-Kevin
>
>--
>
>remove the x for an email reply
>
>
Received on Wed Jan 13 1999 - 13:17:12 CST