Oracle 11g and 12c Cardinality Feedback Leads to Hundreds of Child Cursors

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




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Oracle 11g and 12c Cardinality Feedback Leads to Hundreds of Child Cursors”

  1. Craig Says:

    You are the greatest DBA ever!

Leave a Reply