> 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.
