RE: Suggestions Needed: Latch free - library cache

Content-Type: text/plain
Content-Transfer-Encoding: 7bit

> During the Hotsos course I thought I remember hearing that the pool
> could be too large and that could have a negative impact on the
> library cache latch.  Am I confusing this with something else (maybe
> the buffer busy event)?   If true how do you go about determining the
> optimal size?  

Having too large shared pool may cause contention on shared pool latch, especially when shared pool's LRU lists get very long, causing searching for unpinned recreatable chunks to take long time (using CPU and preventing others from scanning the list).

Before 9i there was only one shared pool latch protecting operations on whole shared pool. But starting from 9i, this issue can be relieved somewhat, but splitting the shared pool heap to several smaller heaps and assigning one shared pool child latch for each of them. This can be controlled using _kghdsidx_count parameter and the max in 9.2 seems to be 7, since there are 7 shared pool child latches (at least on W2K and Linux).

However, when you split your shared pool to several parts, you might hit ORA-4031 error even when some shared pool heaps do have enough usable space, but the current one where allocation was tried, doesn't.

Tanel.

Tanel.

--=_7a8a1c0b9aa05e805c10909c05709087--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).