Too Many Damn Kids!

Sure I’m informed children are a blessing, but there can be too much of a good thing. This applies to the Oracle database as well as real life. We’ll look at Oracle child cursors, how many we can get and why, look at the effects this can have and some ways to mitigate and what to monitor.

Cover dangers of default setting of 8000 possible child cursors in recent versions of Oracle, and what happens when we approach this value (parse times/pins etc etc). Review effect of reducing this value (cursors marked obsolete but issue of creating new children persists). Cover some reasons multiple child cursors are created, adaptive cursor sharing, bind length mismatch etc and ways to mitigate the number of children created