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.
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.
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 18:22
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: gather stats
Actually, I disagree with Wolfgang's advice. True, Wolfgang's advice
will speed up statistics collection, but at the cost of the information
necessary to the optimizer ability to calculate proper execution plan. I
would suggest the following for METHOD_OPT
'FOR ALL COLUMNS SIZE 254 FOR ALL HIDDEN COLUMNS SIZE 254'
This, of course, is relevant only for version 11. Version 12 can
accommodate much large histograms.
I agree with the recommendation to use auto sample size because the new
algorithm for collecting column statistics is much faster than the old
one when it comes to determining NDV (the number of distinct values).
I have the following article bookmarked:
https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size
It explains why is it beneficial to use the new and much faster
algorithm to calculate the number of distinct values in a column. The
new algorithm only works with auto sample size. I would also recommend
gathering system statistics. It establishes the relative prices of db
file sequential and scattered read, or, in layman's terms, relative
speeds of an index read and full table scan read.
Regards
On 11/09/2015 01:06 PM, Matt Adams wrote:
I can understand not gathering histograms across all columns due to the
potential time it would take to do so. Is there any other technical reason
not to..(perhaps some issue with the optimizer I'm not currently aware of)?
I routinely gather histogram information for all INDEXED columns.
Matt Adams
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Wolfgang Breitling
Sent: Monday, November 09, 2015 12:13 PM
To: kp0773@xxxxxxxxx
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: gather stats
Use auto_sample_size and refrain from gathering histograms on all columns (
method_opt 'for all columns size 1')
Sent from my iPhone. Typing errors may have occurred.
On Nov 9, 2015, at 10:01, K R <kp0773@xxxxxxxxx> wrote:--
All,
What is the recommended way of gathering stats on 11gR2 database ( 2.5
TB) . If i keep estimate percent 10,15 then some of the table are taking
hours and hours .
Please let me know .
Thanks in advance.
Kart.
//www.freelists.org/webpage/oracle-l
**** This communication may contain privileged and/or confidential
information. If you are not the intended recipient, you are hereby notified
that disclosing, copying, or distributing of the contents is strictly
prohibited. If you have received this message in error, please contact the
sender immediately and destroy any copies of this document. ****
--
//www.freelists.org/webpage/oracle-l
