RE: gather stats

Somewhere I've published a note which says something like:

"There are two possible strategies for dealing with histograms:  gather histograms for every column then fix the small number of cases where the histogram causes problems, or don't gather any histograms then fix the small number of cases where a carefully constructed histogram is needed."

Depending where you start from either could be the better strategy - but given the choice I'd avoid creating any histograms that I didn't need. Apart from the extra work at stats collection time, and the instability inherently in histograms, they're a trigger for the optimizer to do increasing amounts of extra work at optimization time (e.g. create an adapative execution plans and derive inflection points because a column has a (redundant) histogram) as you move through versions of Oracle.



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce@freelists.org] on behalf of Mladen Gogala [gogala.mladen@gmail.com]
Sent: 09 November 2015 23:20
To: oracle-l_at_freelists.org
Subject: Re: gather stats

Hi Jonathan, my comments are in-line

On 11/09/2015 01:54 PM, Jonathan Lewis wrote:
> Bear in mind that even though auto_sample_size means 100% approximate_ndv for simple stats Oracle will still sample for histograms; so many of your histograms could be built on a sample of about 5,500 rows; with some built on larger samples.
If there is a table for which such method produces undesirable SQL
execution plans, it is possible to set table preferences on per table
basis. This is, as a matter of fact, what I usually recommend my
clients. Sample size can be set in table level preferences.

>
> I think "for all hidden columns size 254" might be implied by "for all columns size 254", but the way - that might only be true for relative new  versions of Oracle, of course.

Hmmm, I tested it in 11.2.0.2 and did not get histograms on the virtual
column for the function based index without it. I haven't tested it in a
while, though. I've been using "FOR ALL HIDDEN COLUMNS" just
automatically, cut and paste from my collection of scripts. I will have
to re-test this.



--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l


--