Figure 7-7. The second part of the trace file created by the SQL in Figure 7-4. This text is diagrammed in Figure 7-5. While no lines have been modified, many lines have been removed.

Because library cache objects are related to other library cache objects that are consuming chunks of memory, Oracle may deem it necessary to release an object's memory. It is possible for the handle to remain, but the actual memory chunk to have been deallocated. If this occurs, before the cursor can be reexecuted, the library object must be reloaded into the library cache. Oracle keeps track of these reloads in the reloads column from v$library_cache, and for specific object statistics, in the loads column in v$sql and v$sqlarea. If reloading becomes a significant problem, CPU parse time will increase, and shared pool and library cache latch or mutex contention are likely to become your top wait events.

The more I have learned about the library cache, the less I complain about shared pool issues. The library cache's complexity is daunting and should bring about a level of awe.