Sounds like the best use of 'CLOSE_CACHED_OPEN_CURSORS' I ever heard of.
Kevin Brand wrote in message <770tf5$f8b$1_at_news.gte.com>...
>
>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 Fri Jan 08 1999 - 00:29:11 CST
