Symposium 2011 – My Presentation

I think the best approach here is to focus on the technical details of the mistake first and then follow with any whining, self-justification or philosophy.

The mistake I made in my presentation was to suggest at least twice that simply adding a new partition to a composite partitioned object is enough to completely invalidate the aggregated global stats at the table level. That was based on me taking a valid example from the white paper (listings 13 and 14) and an earlier blog post and (badly) converting it to a simple partitioned table example.

The bit I missed in the conversion (and subsequently reinforced verbally) was that it’s the subsequent gathering of statistics on a single subpartition and not all of the subpartitions that causes the stats to go bad because some subpartitions are missing statistics. To put it a more elegant way, I liked the way Wolfgang Breitling expressed it to me – ‘A DDL operation will not invalidate the stats’. He’s quite right. What invalidates the stats is making the wrong calls to DBMS_STATS.

The white paper hasn’t changed because the example there was always correct and reflected a real world issue. Here are the specific corrections I made to the slides.

Changed whole section to missing *Sub*partition stats to reflect the example in the paper correctly

Slide 35 – Changed
Solution – quickly add a new partition *and gather stats* (incorrectly gather stats, as it happens)

Slides 36-39
Changed diagrams to show new partition and subpartitions being added but stats only being gathered on one of the new subpartitions, which invalidates both the global and partition stats for the new partition because not all of the underlying component subpartitions have had stats gathered on them.

Essentially, the simple act of adding partitions and subpartitions does not invalidate aggregated global stats, but partially-gathered stats will.

Slides 47-49
Fixed repeated implication that adding partition invalidates aggregated stats – it does not.

Slide 53
Toned down some of the negativity about Dynamic Sampling after discussion with Wolfgang

With the benefit of time and reflection, it was a bad mistake but I don’t think it was anywhere near to invalidating the presentation which I still think was one of my better ones (no demos, you see 😉). I also think it was a perfect example of several essentials of sharing technical information either through presentations or articles.

– By publishing the scripts you’ve used and the results, others can look at the tests and see where you’ve gone wrong.
– By publshing the scripts you’ve used and the results, you can immediately see where you’ve gone wrong when someone questions your results! (As soon as I opened my laptop after being questioned by Wolfgang and Maria, I realised what I’d done.)
– When trying to translate real results to pretty pictures, make sure you don’t screw up the essential detail in an effort to make things look simpler!
– Don’t rush your slides 😉
– Don’t decide that what is on your slides must be true when you’ve already got a paper showing the truth!
– Don’t believe anything any presenter tells you without seeing the results and then checking for yourself. Trust but Verify is the common mantra.

Anyway, my thanks to Maria Colgan and particularly to Wolfgang Breitling for doing the right thing by highlighting my error and discussing it in some depth so that everyone can get towards the correct information. Although I was naturally a little grumpy that I’d made a mistake in an area I actually know quite a lot about, the subsequent discussions with Wolfgang is adding to our collected pool of knowledge in very interesting ways.

All of these links are to updated materials.

http://www.slideshare.net/dougburns/statistics-on-partitioned-objects
http://oracledoug.com/stats_slides.pdf
http://oracledoug.com/stats.pptx
http://oracledoug.com/stats.docx