Oracle allows the number of buckets to be viewed (even though the value may not represent the truth) via the instance parameter _kgl_bucket_count. The number of library cache latches is controlled via the instance parameter _kgl_latch_count. I know of no one who has increased the number of buckets in a production system and successfully reduced library cache latch contention. However, just as with the cache buffer chain latches, library cache latch contention can be reduced by increasing the number of library cache latches.

To demonstrate just how dramatic adding more latches can be, I created an experiment with the number of library cache latches set to 1 and 100. On a four-CPU core Oracle Database 10g Release 2 Linux system, I placed the load of only five concurrent processes, which essentially never ran the same SQL statement twice. I ran each test a few times. With just one library cache latch available, only 35% of the host's CPU capacity was used, 84% of the wait time was attributed to latch: library cache, and the execution rate was 260 exec/sec. With 100 library cache latches available, 64% of the host's CPU capacity was used, 2% of the wait time was attributed to latch: library cache, and the execution jumped up to an amazing 3,112 exec/sec. So, as expected, increasing the number of latches allows for increased concurrency and resource consumption. It's also significant that Oracle's spin/sleep latch acquisition algorithm did not saturate the CPU subsystem by constantly spinning on the one available latch!

Just for fun, I enabled mutexes, and the execution rate reached only around 710 exec/sec! While Figures 7-11 and 7-12 clearly show mutexes provide significant benefit when intense cursor pinning is required, when building cursors is an issue (a lot of hard parsing), having plenty of latches can provide increased performance over mutexes. With that said, this kind of latch and mutex comparison is highly load and Oracle release dependent. So I am not making the statement that latches will always beat mutexes during intense hard parsing.