Figure 7-5 shows bucket 14778 is associated with the cursor that has two identical SQL statements, yet an optimizer parameter was altered between their execution, creating the two child cursors. Notice both the parent and child cursors have the same type: CRSR. If you look closely at the top of the Figure 7-6 trace file, you'll see bucket 14778, its associated handle, mutex, name, and two child cursors, each with a handle. Bucket 60635 is associated with one object: the findme table with its handle 456df410.

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.