If your contention for the child library cache latches is unevenly distributed
then additional processors are likely to make the latch contention problem
worse, not better. Check V$LATCH_CHILDREN for this. Check the Answers section of
the Ixora web site for a recent explanation of this.

Also, it sounds like your shared_pool might be too large. I suggest that you get
the shared_pool_free_lists.sql script from the Ixora web site and run it during
a period of bad performance. If you find long shared pool free lists then you
are likely to get some relief by reducing the shared pool size. Reducing the
number of child library cache latches to 1 using _kgl_latch_count can also help,
but is to be regarded as a last resort. Otherwise just upgrade to 8.1.6 where
the shared pool free list management has been GREATLY improved.

If it is not a shared pool problem, then increasing the number of child library
cache latches to 67 using _kgl_latch_count will mitigate the effect of the
failure to use bind variables. Otherwise, just upgrade to 8.1.6 and use
cursor_sharing to make all your literal SQL shareable.

You can also reduce load on the library cache latch using the session cursor
cache. Even for applications that use literal SQL, a modest value like 10 for
the session_cached_cursors parameter can make a noticeable difference.


-----Original Message-----


I'm the DBA for a website and periodically, under heavy load, the server
falls on it's knees. using the top utility, it hits 7,8 and 9. If I flush
the shared_pool then the load goes down again...so I believe the shared_pool
is fragmented. BUT the load still seems quite high, around 1-2 for most of
the day.

I think we need more processors, to manage 4GB of memory, and I've heard the
standard of 1 processor per 512M of memory. I'm being told by my boss that I
can never have enough memory, but I'm sure that's not the case. There's no
swapping going on and the hit rates are high (99) in the database. The
processor does hit 0 idle time though during heavy load.

Has anyone got any experience of this situation? We're going to try and put
2 more processors in the machine as a test. The only database activity is
'select' and a small minority of inserts (maybe 100 an hour).

Utlbstat/estat only really shows the 'latch free' statistic as a problem
when the server is on it's knees...actually library latch contention. We can
get around this by changing our code to use bind variables, but I still
think we need more processors?