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
