A key library cache characteristic is the object relationships. In Figure 7-3, notice that table TBL 400 is associated with the three child cursors CCSR 600, CCSR 610, and CCSR 620. So, if table TBL 400 is altered, Oracle knows which library cache objects to invalidate. For example, if table TBL 400 were altered and Oracle deemed the alteration severe enough to invalidate the library cache entry, then all the associated library cache objects would also be invalidated. And, of course, serialization must be maintained, so you can see how even a relatively small library cache can become quite intense.

Also note the impact of using mutexes instead of latches. Since a mutex is associated with each library cache object, entire hash chains will not be made unavailable, resulting in a significant reduction in false contention as well as the acquisition CPU consumption, and therefore improved response time.

Now, let's take the conceptual model to a much more realistic level to clarify the library cache object relationships. Three figures are involved in this example. Figure 7-4 is the actual shell script containing the SQL. I simply copied and pasted the code into my Oracle user Linux session. Figure 7-5 is a library cache model with the actual real-life object references. Figure 7-6 shows the relevant parts extracted from the actual trace file.