If you ask a group of performance analysts about their experiences with cursor sharing, you'll immediately get a seemingly conflicting and passionate discussion. Some, like myself, have had wonderful experiences with the similar option; others have had all sorts of problems. Some who have used the force option have seen their application SQL so aggressively transformed that the SQL result set was actually different.3 For example, instead of returning ten rows, the SQL returned two rows, effectively breaking the application!

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.