Figure 7-1. Shown is the same session running the same SQL statement with a simple alter statement between executions. This is enough to force Oracle to create two child cursors. The trace statement is used to prove two child cursors are created.

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.