RE: Histograms
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'charlottejanehammond@xxxxxxxxx'" <charlottejanehammond@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Aug 2005 12:10:40 -0700
Charlotte,
The broad-brush method has a few problems. Apart from the overhead of
gathering and storing histogram information, you will notice access to the
HIST_HEAD$ and HISTGRM$ internal tables during query parsing as seen in the
SQL trace. This occurs when the CBO searches the Data Dictionary for the
presence of histograms on columns that may benefit from a histogram - if
found histogram details would be loaded into the DD cache in the shared
pool, even if they are subsequently not used or even misused! And I won't
even mention latching (row cache/lib cache/shared pool) latching and
allocation overhead with shared pool access. The problem now takes two
different paths - one for 8i and one for 9i/10g.
In 8i, the default 'optimizer_max_permutations' is 80,000. This means that
by default, the CBO might perform upto 80,000 iterations as it works out the
best path, and it might go nuts parsing and generating various execution
path alternatives in a broad-brush collect-histograms-on-everything scenario
since there are now much more "options" than there were before. Note that 9i
(and 8i instances supporting Oracle Apps 11i) reduced this default to 2000
so the problem is somewhat mitigated.
In 9i/10g, the new "bind peeking" feature backfires and produces
inconsistent results when histograms are present (albeit only in cases where
bind variables are used and histograms are present on participating
predicate columns). Essentially, bind peeking was introduced in 9i to cover
cases when histogram information was not considered when bind variables were
used - the side effect is that the plan is "fixed" by the initial values in
the bind variables during the first parse. Subsequent executions will now
use this plan, but the value of the *current* bind variable is not
considered - it is then possible that the previously frozen path is not the
right one considering the *current* value.
I am not sure what happened in the problem Wolfgang mentioned, but it is
possible that one of the above kicked in. I did encounter an issue in an
Oracle Apps environment and used Histograms *judiciously* to fix this issue
- you can see details in my article on SELECT titled "Judicious Use of
Histograms in SQL tuning" or my paper in OAUG 2005 for the same in Apps. I
have feedback from a few others who also used this to fix some issues. The
performance of one report dropped from 1 1/2 hours to 2 minutes and in
another case went from an hour to 1 second. The keyword is "judicious"
though, but I wouldn't write off histograms entirely!
The article you mention also recommends "silver bullets", so 'nuff said
about that ;-)
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Fear connects you to the Negative, but Faith connects you to the Positive! I
Jn 4:18
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Charlotte Hammond
Sent: Tuesday, August 16, 2005 7:16 AM
To: ORACLE-L
Subject: Re: Histograms
Thanks all.
Perhaps to play devil's advocate slightly (as I said, we don't use
histograms much here for the reasons
given): why not turn this on its head and say gather ALL histograms (so CBO
has maximum information) and then remove those that cause trouble? (And
since you don't compulsively regather stats of course, it's only a one-off
exercise to get the histograms :-)
I guess I was also slightly interested in the detail behind the web site - I
think David Kurtz paraphrased it well when he said 'I heard about this from
some people, it sounds like a good idea but I haven't tried it for myself'.
Has anyone talked about this to the people listed, namely Jeff Maresh, Arup
Nanda or Mike Ault, who appear to HAVE actually tried it and seem to think
there's something in it; I'm curious as to their thinking.
Thank you
Charlotte
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> Like David and Yasin, I am against the "broad brush"
> method and use
> histograms only in specific cases with demonstrated benefits.
> I don't have a testcase, but I have a documented case at a client
> where they changed their week(end)ly statistics gathreing from the
> default "for all columns size 1" to "for all indexed columns size
> skewonly" and had the bottom fall out of a batch job that usually took
> 1.5 minutes because one frequently executes sql (1000s of times) went
> from .01 elapsed to around 80 seconds elapsed.
> So be careful whenever you change your statistics gathering strategy.
> And that is not limited to histograms.
>
> Charlotte Hammond wrote:
>
> > Hi All,
> >
> > Can I poll the list on their views on the use of histograms? I've
> > previously been quite
> conservative,
> > only gathering them when we've had a specific case
> of
> > bad CBO performance due to skewed data. However I
> was
> > interested in this article mentioning a "broad
> brush"
> > approach to histogram gathering:
> >
> >
>
http://www.dba-oracle.com/oracle_tips_all_columns_histograms.htm
> >
> > Any thoughts to share?
> >
> > Thank you
> > Charlotte
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
