Cardinality feedback

One of my faviourite presentations in Collaborate 09 was a quick tip (30 minutes) on Tuning by Cardinality Feedback – i.e. comparing the optimizer’s estimates of cardinality with the actual rowcounts returned when you run the query.

The strategy is one that I first saw demonstrated in a presentation by Wolfgang Breitling a few years ago at one of the Hotsos seminars – but this latest example demonstrated an important variation on the theme in a short, precise, lesson.

I’ve asked the author (Michelle Deng of Sanofi Aventis) if I could post the presentation on my blog and she said I could, so here’s a pdf file of the slides. Since you won’t have Michelle’s comments running in time with the slides I’ve set the file to open in a new window so that you see the slides and read my comments at the same time.

The first few slides describe what we mean by cardinality and tell us ways in which we can get at the estimated cardinality (E-rows) and actual cardinality (A-rows).

Slide 9 shows us a query that took more than 22 hours to complete and the view that the query was based on. The week_fact is a very large partitioned table in that view and you might notice it is referenced twice: once as a scalar subquery in the from clause (where a couple of “)” have turned into “|” symbols) and then again as the main source of data from the view.

Slide 10 shows the execution plan (and estimated cardinality) from running explain plan and calling dbms_xplan.display, and you can see that Michelle has highlighted operation 19 – the full scan of prod_fctr_mv and the filter predicates used.

You will note that operation 8 is a nested loop, which joins the result of a hash join at operation 9 (estimated cardinality one) to partition 5 of the week_fact table using the week_fact_idx_ie3 index at operations 23, 24 and 25.

Results with a cardinality of one should always be treated with a little suspicion, and this part of the plan is suspect.  The cardinality of one starts with the (necessary) full scan of partition 5 of fact_week, which drives a nested loop with the full scan of prod_fctr_mv that Michelle has highlighted.

At this point, we could consider running the query again with the /*+ gather_plan_statistics */ hint in place – and in another 22 hours or so we would be able to compare estimates with actuals to see how accurate that cardinality estimate of one row really was!

Since running the whole query again was clearly not sensible (someone asked about using the 10g Tuning Advisor and was told that it hadn’t completed a run) Michelle used the filter predicates for operation 19 to query prod_fctr_mv and see how much data would be returned at that particular point in the plan.  The result, along with a few others, appears in slide 11.  Oracle’s estimate at this point is one row; but the actual number of rows is 42, which means that the big index range scan required by the plan would be executed 42 times, rather than once – and that’s a pretty big mistake given the amount of data that’s likely to be accessed by that range scan.

There were four separate conditions against prod_fctr_mv, and on slides 12 and 13  we see that the optimizer has frequency histograms on the four relevant columns, and can predict accurately the number of rows returned by each individual predicate – which means the problem is in the combination of the predicates.

Slide 14 defines the problem and demonstrates the degree of correlation, and slide 15 lists a few strategies for dealing with the problem. In this case Michelle adopted the strategy of setting the parameter optimizer_dynamic_sampling to level 4 – which means that when a query applies two or more predicates to a table that table will be sampled (32 or 64 blocks by default, depending on whether or not the table already has any statistics) to check the dependency of those predicates, thus allowing the optimizer to generate a dynamic selectivity as the query is optimised.

With this setting the optimizer discovered the cardinality problem with prod_fctr_mv and changed the plan from one that took over 22 hours to one that completed in less than 44 minutes, using the plan shown in slide 16. The change in plan is largely due to the more accurate cardinality estimate (highlighted in line 34 of the plan) for the full scan of prod_fctr_mv.

Conclusion:

The key point I wanted to emphasise about this example was that you can compare estimated cardinality with actual row counts to pinpoint the line in the execution plan where the optimizer broke down – but you don’t necessarily need to run the query to find that point. It’s a lesson that everyone should learn.

Footnote

A common oversight when talking about comparing estimates and actuals is to forget that to mention that the E-rows is per call to the operation while A-rows is summed over all calls, so your should be thinking of A-rows = E-rows * Starts. (See this blog note)

Even then, for partitioned tables and parallel execution, there’s another complication to consider (discussed here).