The first time a cursor is executed, there is actually a parent cursor and a child cursor. Subsequent sessions, and even the same session executing the same exact SQL statement (that is, the hash value is the same), may require a different child cursor. While the SQL statement will be textually exactly the same, a child cursor is created to capture specific characteristics, such as optimization mode differences (for example, first_rows) resulting in different execution plans or different session-level parameters (cursor_sharing=similar). To demonstrate this, Figure 7-1 shows a simple situation where the same session executed the same SQL statement twice with a simple alter session in between the two executions. This is enough to force an additional child cursor to be created. The trace command is used to prove two child cursors are created.

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.