Re: Library cache latch contention and SPIN_COUNT

Doesn't sound like spin_count is the problem.

If you have a large shared_pool, the shared pool freelist can become fragmented to an extent that each parse call that generates a cache miss can obtain and hold the shared_pool latch for a very long time while it traverses the list looking for a free chunk of memory.

When you flush, you are truncating the list ( defragmenting it ), which allows user processes to traverse the freelist at normal speeds, fast, thus increasing overall Parse performance.

The real problem is too much literal ( non-sharable ) SQL statements. When you notice lots of latch waits, execute the following query several times. Each execution ( with the exception of the first ) will show you what statements are going in to the shared_pool and how many SQL shared_pool objects are being taken out to accommodate the new SQL statement:

• klmlrsiz: amount of contiguous mem being allocated 10k plus is bad
• klmlrnum: number of objects displaced
• ksmlrhon: name if pl/sql block or cursor
• ksmlrohv: hash value of object loaded
• ksmlrses: saddr of the loading session

select * from x$ksmlru ;
set echo off;

Possible solutions: any one will help, but all together is best:

    o reduce the size of the shared_pool     o use host variables in your applications, or     o otherwise use sharable SQL
    o pin all large procedures, triggers, and packages in shared_pool

If you can't fix the application, you may find that a flush at regular intervals is the only solution.

-Kevin
--

remove the x for an email reply
Stefan Deisz wrote in message <01be55c7$2c9b7130$dd0b010a_at_stefan>...
>Hi all,
>
>On a 30 CPU SUN E10000 we are running into some high library cache latch
>contention. Extending the shared pool doesn't seem to work, flushing the
>shared pool does work for a couple of hours.
>We have the SPIN_COUNT parameter at 2000, the default.
>
>Does anyone know if we can tune the SPIN_COUNT parameter, and if so: to
>what value?
>
>Thanks in advance!
>
>Stefan.
Received on Thu Feb 11 1999 - 13:43:41 CST