Archive for December, 2015

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


By Martin Klier in Oracle  .::. Read Comment (1)


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.