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.

While hashing may seem rather dull at this point, this section will clearly demonstrate why two syntactically exact SQL statements are treated as two distinct and different SQL statements! If you have every wondered why select * from dual is considered different from select * from Dual, read on!