| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Histograms
Hi John, do you have any links where i can download your paper??
Regards
Galo
-----Mensaje original-----
De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En
nombre de John Kanagaraj
Enviado el: Martes, 16 de Agosto de 2005 14:11
Para: 'charlottejanehammond_at_yahoo.com'; ORACLE-L
Asunto: RE: Histograms
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
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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
> 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
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 16 2005 - 15:01:49 CDT
 |
 |