Figure 7-6. The first part of the trace file created by the SQL in Figure 7-4. This part contains the hash bucket chain details. This text is diagrammed in Figure 7-5. While no lines have been modified, many lines have been removed.

Now let's see how Oracle keeps track of child cursor relationships. Figure 7-7 is the second part of the trace file generated by the Figure 7-4 SQL. This section of the trace file starts with ANONYMOUS LIST:, making it easier to locate. Notice there are three library object references and that each one of those has a handle matching one of our three child cursors! Find the bottom library object entry, which has a handle of 456db5ec. This is a cross-reference for the child cursor with the handle of 456db5ec. Notice it has two references: a reference to the table in its SQL statement (findme with handle 456fd410) and a reference to its parent cursor (handle 456dbf90). Take a minute to perform the same cross-reference with the other two child cursor library object entries.

Let's make a few observations. We can clearly see the library cache contains not only references to the objects themselves, but also their associations or connections. And these associations are being cached in memory, which provides potentially thousands of references when working with a single SQL statement. The library cache concurrency requires its contents be referenced frequently, and in most cases, with other sessions needing access to the same information. Concurrency is controlled through either latches or mutexes. So creating a cursor, referencing a cursor, soft and hard parsing, and destroying a cursor all require serialization control. Also, because the library cache contains object relationship information in addition to the object itself, Oracle is able to properly invalidate the fewest library cache objects possible.