Figure 7-2 is part of the massive trace output file created from the trace command shown in Figure 7-1. I located the part of interest by searching for from dual and then examining the SQL statement. Of interest to us at this point is that this one SQL statement was executed by just a single session, yet it created two child cursors.

Figure 7-2. Shown is a small yet significant part of the level 10 library cache dump executed from the end of Figure 7-1. Notice the same SQL statement has two child cursors. The second cursor was created because a session parameter was changed.

Relationships between library cache objects must be maintained not only for execution purposes, but also if a change occurs in one of the components. Suppose one table is referenced in 2,000 SQL statements, in 100 functions, and 20 packages. Now suppose one of the table's columns is renamed. Oracle will then invalidate all related SQL statement and programmatic constructs. This can result in a cascade effect that requires latching and locking. The combination of multiple involved sessions, invalidations, recompilations, and timing has resulted in entire Oracle instances locking up. Obviously, Oracle knows about this serious situation and is actively decreasing the likelihood of it occurring. But every DBA needs to know library cache relationships are very complex and can cause problems at times.