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
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Mladen Gogala [gogala.mladen@xxxxxxxxx]
Sent: 09 November 2015 23:20
To: oracle-l@xxxxxxxxxxxxx
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 forIf there is a table for which such method produces undesirable SQL
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.
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.
