latch: shared pool

Во время выполнения запроса из отчета столкнулся с latch: shared pool. Стало немого непонятно, откуда оно берется. Оказалось, что

library cache latch is already held while requesting for shared pool latch. Shared pool latch is acquired to request/release space from the shared pool free memory area and released immediately after that. Request for library cache latch can never be made while holding the shared pool latch (at least up to 9i) as latch level semantics will prevent that ( as shared pool latch gets are always in willing-to-wait mode).

Дальнейшее исследование навело на сайт, где описывается похожая проблема. Г-н Адамс рекомендует на своей странице следующее:

Shared pool too big

? We have recently migrated to Oracle8i, but it seems the performance is quite slow. The machine is large in terms of disks, CPU, and memory. Also, we have moved to IPC since we have batch jobs running on the database, and no users connected over the network. Could you tell me what I should diagnose on the database and the Solaris 2.6 server to find the bottlenecks? A utlestat report is attached.

This is a classic case of too large a shared pool causing extreme latch contention on the shared pool latch. Note the following statistics from your report.txt .

CPU used by this session 11639.58 (seconds)
latch free 25353.58 (seconds)

That is, you are spending twice as long waiting for latches than doing useful work, and much of your CPU time would be consumed while spinning. Note also where those latch sleeps are ...

shared pool 4292776 (sleeps)
library cache 252949 (sleeps)

That is, 94% of these sleeps are on the shared pool latch. The rest are secondary sleeps on the library cache latches.

The problem is that shared pool latch is being held too long while searching the free lists, because the free lists are too long. The free lists are too long, because the shared pool is too big. You need to make appropriate use of DBMS_SHARED_POOL.KEEP to mark valuable objects for keeping, and reduce the size of your shared pool dramatically.

? Could it be that there is not enough space in the shared pool?

No, the ideal situation is to have a small shared pool in which all the important reusable objects are marked for keeping and in which other objects are recycled quickly. People often attempt to increase the shared pool under these circumstances rather than reducing it. Normally, it appears to have worked for a while, because it takes longer for the LRU lists to begin to be flushed. But once that happens, you immediately get longer free lists and worse contention for the shared pool latch than would previously have been the case.