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.

Figure 7-4. Shown are a few lines of code, which show library cache object relationships. Figure 7-5 is a model of the library cache objects, and Figure 7-6 shows the key parts from the actual trace file.

Figure 7-4 starts with simply connecting to Oracle. To ensure I didn't lose any critical trace file pieces, I set my dump file size to unlimited. I then created a very simple table, named findme, to make it easy to find the SQL in the massive trace file. Just as I did in Figure 7-1, I executed the same SQL statement but altered an optimizer parameter. This should result in two child cursors. Then I executed a different SQL statement, which references the same findme table. I did this to show that different SQL statements will reference the same table handle. Finally, I performed the library cache dump and disconnected.