RE: [External] dbmst_stats estmate

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Fri, 20 Jan 2017 19:07:09 +1100
Message-ID: <CAFeFPA8e8MVsO3mVw0XbU2fPZfwGR252H+C0yhckQSjHSqXuZQ_at_mail.gmail.com>



Thanks for the links...i think you are right about the column histograms....we have a very skewed table and the histograms with estimate are giving us grief...
For now i have locked in the stats that seem to work for us while i do some more investigation.

On 20 Jan 2017 16:53, "Chitale, Hemant K" <Hemant-K.Chitale_at_sc.com> wrote:

> In 11g AUTO_SAMPLE_SIZE seems to be 100% or “almost 100%” {There is a
> reference to this in MOS Document ID 749227.1}
>
> The enhancement was to change the algorithm for NDV (Number of Distinct
> Values) to run faster.
>
> Pre 11g it really used to start with small numbers and if it determined
> the sample was inadequate (algorithm unpublished I believe), re-gather with
> a larger number. {See MOS Document ID 343849.1}
>
>
>
> However, the sample size for Column Histograms can vary (algorithm
> unpublished I believe). Column Histogram sample sizes are much smaller
> than the 100% or near-100% used for Table statistics.
>
>
>
> See http://structureddata.org/2007/09/17/oracle-11g-
> enhancements-to-dbms_stats/
>
>
>
> https://blogs.oracle.com/optimizer/entry/improvement_
> of_auto_sampling_statistics_gathering_feature_in_oracle_database_11g
>
>
>
> https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size
>
>
>
> https://blogs.oracle.com/optimizer/entry/i_thought_the_new_auto
>
>
>
>
>
> Hemant K Chitale
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Jack van Zanen
> *Sent:* Friday, January 20, 2017 11:42 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* [External] dbmst_stats estmate
>
>
>
> Hi all,
>
>
>
> Maybe an easy one but my google search terms were not sufficient to find
> an answer :-)
>
>
>
> How does oracle decide which blocks to sample when you leave estimate set
> to auto?
>
>
>
>
>
>
> Jack van Zanen
>
>
>
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 20 2017 - 09:07:09 CET

Original text of this message