Sometimes, advanced features can bring advanced problems. Seeing a project with hundreds of child cursors with a different execution plan each, is never a pleasure, but there has to be a reason.
In fact, we are talking about the “USE_FEEDBACK_STATS” reason in v$sql_shared_cursor. Its meaning is: “A hard parse is forced so that the optimizer can reoptimize the query with improved optimizer inputs (for example, cardinality estimates)”. The issue was, that the DB executed a plan, got feedback, made a new plan, executed it, got feedback, made a new plan … This way,
Oracle 11g and 12c Cardinality Feedback Leads to Hundreds of Child Cursors!
And too many child cursors is causing all sorts of problems for efficient parsing, as I described some time ago.
What I did to get rid of the child cursor generator in this case:
- Simplifying the query by removing subqueries (changing them to joins wherever possible). This grossly reduces the degree of freedom for the optimizer.
- Creating better statistics by using extended statistics (multi column stats to improve combined cardinality). This was the key to success, made the initial guessing good enough for a stable plan.
With this combined, we had less plans, and the plans we got were used more than once.
Just the picture was specific to a recent version, but the reason was always there. The invalidation reason just surfaced it. Fixed it with quite a traditional approach, as I usually do – not fussing around with ADDM and stuff.
I’m a strong believer in the CBO’s dynamic. So please treat your Cost Based Optimizer nicely – you can’t beat it into submission. Make it see the facts, and it will behave well without strings attached.
Sorry that I can’t show you a reference case today. Hope this gives you an idea anyway, and if you have questions, just let me know.
Martin