RE: gather stats

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
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Mladen Gogala [gogala.mladen_at_gmail.com]
Sent: 09 November 2015 18:22
To: oracle-l_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
> Sent: Monday, November 09, 2015 12:13 PM
> To: kp0773_at_gmail.com
> Cc: Oracle-L_at_freelists.org
> 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_at_gmail.com> 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.
> --
> http://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. ****
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>


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

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


--