Statistics - To collect histograms or not to collect
937246May 14 2012 — edited May 15 2012Hi,
I was discussing with a friend about collecting statistics with histogram, if it worth spend time studying the table, its queries and where predicates or if the performance gains worth the spent time.
Checking the documentation, the default statistics gathering method for 10g and onwards is FOR ALL COLUMNS SIZE AUTO, so, it gets some workload to calculate the statistics, unlike the 9i version, which uses FOR ALL COLUMNS SIZE 1.
Also, checking the Ask Tom webpage (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:897382100346033938#2468091800346350999), he says that either:
a) do not gather histograms
b) you use the maximum buckets, if there are less than 254, we only use what we need and get exact cardinalities for values. If there are more, we use 254 and 'guess' at the cardinalities.
Since there's no magic formula to check which tables are elegible, how many buckets and which histogram statistics should be collected (height-balanced of frequency), I'd like to know the opinion of you guys to this question.
Thanks in advance