Written by In the last post I discussed a test case generating lot of child cursors. Today I wanted to show you, for the very same test case, that in 11.2 the parse time might increases linearly with the number of child cursors per parent cursor. This is the expected behavior. In fact, to check whether an already available child cursor can be reused, the list of child cursors must be scanned. And, in case no one of the already available child cursors is compatible, every entry needs to be probed.
Note that to generate the previous chart I simply run, in 11.2.0.2, the PL/SQL block of the previous post while SQL trace was enabled. Then I extracted from the trace file the elapsed time for every parse operation and loaded the values in Excel.
In 11.2.0.3, because of the artificial limitation of the number of child cursors per parent cursor, the parse time is almost constant. Notice, however, that in this case the number of child cursors is spread across a lot of parent cursors, not a single one as in 11.2.0.2.
Looks like there is an index missing on the column(s) that define if a child cursor can be reused (optimizer environment). But on the other hand, the high number of child cursors that would justify the index is the actual problem.