RE: ** histograms

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Wed, 22 Nov 2006 12:41:37 -0800 (PST)
Message-ID: <20061122204137.21338.qmail@web58001.mail.re3.yahoo.com>

Thanks to Stephen Booth, Wolfgang Bretling , Ghassan Salem, Syed Jaffar Hussain, Alex Gorbachev,Alberto Dell'Era for the help. Detailed explanation and example were especially helpful. Thanks

Below is post from Alberto Dell'Era and Wolfgang : To add to the excellent explanation by Stephen - imagine what happens when the plan gets flushed from the library cache for some reason; the new plan will depend on 'Y' or 'X' being the next value, so basically
it will be "random" ... diagnosing this requires some time and an experienced
performance tuner, so many shops will simply think that "the application
is unstable" and move on (bad for reputation).

IMHO adding an histogram is as critical as (or even more critical than) adding,
say, an index; it mandates a lot of reasoning, planning and of course testing.

Wolfgang Breitling <breitliw_at_centrexcc.com> wrote: That is a very nice and accurate analysis. Allowing the CBO to make a more accurate cardinality estimates is what histograms are all about. That may have the consequence that the CBO chooses an index access path if a suitable index is available, but that is by no means the only possible consequence. Seeing histograms only in connection with an index is seeing it too narrowly.

PS. the testcase is in the paper 'Histograms - Myths and Facts" on my website.

At 11:41 AM 11/20/2006, Allen, Brandon wrote: I'm curious to see the example too. My guess is that the histograms made a difference in the CBO's cardinality estimate, which in turn caused it to choose a different join method or join order. Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email