Obviously, you need to talk to your colleagues, check with Oracle support, and test the various options in your particular environment. If physically altering the SQL to use bind variables is not possible or extremely painful, cursor sharing can work marvelously. But you must be very diligent in your testing before your use the option in a production environment.

From a searching perspective, the library cache is architected in a hashing structure. So just like with the buffer cache chains, we can alter the number of hash buckets and, if used, the number of latches. When mutexes are used, Oracle sets the mutex memory structure relationship. For example, as shown in Figures 7-5 and 7-6, each library cache object has its own mutex.

Depending on your Oracle release, Oracle may not actually divulge the number of library cache buckets or latches. For example, Oracle Database 10g Release 2 may show the number of library cache buckets as nine and the number of library cache latches as zero! Significantly altering the shared pool size may have no affect on what Oracle reports. Furthermore, we can easily tell from Figures 7-5 and 7-6 that Oracle actually contains thousands of buckets.