latch on shared_pool and non-sharable SQL

From: Kevin Brand <kevin.brandx_at_tel.gte.com>
Date: Wed, 6 Jan 1999 17:57:22 -0600
Message-ID: <770tf5$f8b$1@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 Wed Jan 06 1999 - 17:57:22 CST