Re: AUTO_SAMPLE_SIZE is every row

I believe that the ESTIMATE_PERCENT=>100 does *not* use the improved NDV calculation 11g onwards. That is why 11g recommends AUTO_SAMPLE_SIZE

The ESTIMATE_PERCENT doesn't strictly drive the METHOD_OPT also, as the METHOD_OPT "SIZE" is the number of Histogram Buckets and the sampling is at the column level (different columns may have different sample sizes)

Hemant K Chitale

On Thu, Jan 18, 2018 at 6:14 AM, Givens, Steven <sgivens_at_fnni.com> wrote:

> I'm curious whether there will be any difference in running the statistics
> gather step mentioned below
>
> BEGIN
> DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'FRED',
> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
> cascade => TRUE );
> END;
>
> Vs. this:
>
> BEGIN
> DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'FRED',
> ,Estimate_Percent => 100,
> ,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO',
> cascade => TRUE);
> END;
>
> I know there was some discussion of Estimate_Percent => 100 being slower
> than Estimate_Percent => NULL, but I haven't found much difference in
> performance.
>
> Thanks,
>
> Steve
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Jonathan Lewis
> Sent: Wednesday, January 17, 2018 2:34 PM
> To: oracle-l_at_freelists.org; MAdams_at_equian.com
> Subject: [External] Re: AUTO_SAMPLE_SIZE is every row
>
>
> If you're running Oracle 12 you probably don't want to change from
> auto_sample_size, and if you're running 11g you might want to change it for
> only a few cases.
> In either case you could decide to gather basic stats using
> auto_sample_size then use a second call to gather histograms for a few
> columns.
>
> The critical issue is that gathering stats for a specified sample size
> makes Oracle use a count(distinct ) on every column, and that (a) very
> expensive for a reasonable sample size or (b) highly inaccurate for a small
> sample size. 11g onwards auto_sample_size means 100% but using an
> "approximate_ndv" mechanism that doesn't have to do the expensive
> "distinct", so it's much more efficient than the older method and also
> generally more accurate than any "cheap" sample size.
>
> 12c also uses 100% with a very clever algorithm for frequency and top-N
> frequency histograms IF you've specified auto_sample_size, and that means
> "perfect" histograms almost free of charge. 12c uses a small sample for
> hybrid histograms, and 11g uses a small sample size for both frequency and
> height-balanced histograms if you specify auto_sample_size and that can
> very easily lead to unstable and inaccurate histograms.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Matt Adams <MAdams_at_equian.com>
> Sent: 17 January 2018 16:19:35
> To: oracle-l_at_freelists.org
> Subject: AUTO_SAMPLE_SIZE is every row
>
> A previous DBA set up all our stats analyzation jobs to be of the form.
>
> BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'FRED', estimate_percent
> => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE ); END;
>
> But for every table I can see, the SAMPLE_SIZE is the same as NUM_ROWS,
> which is the same as the actual number of rows in the table.
>
> I read somewhere that actual value of AUTO_SAMPLE_SIZE can vary from
> release to release (and patch to patch), but why on earth would it always
> be every row in the table?
>
> I'm getting ready to change the estimate percentage to something more
> reasonable. Just wondering if there is something I'm missing somewhere
> that is influencing this behavior.
>
> Matt
>
> **** 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. ****
> --
> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.
> freelists.org_webpage_oracle-2Dl&d=DwIFEA&c=LkAXfnqL6_
> MvrMPL5JzdE3Ild0DUTpmjbCJvMv5_TcQ&r=p64P693r52tzs7tJCmFvOg&
> m=i5zhUfuV_Mr7DVlATPFD-PqtVgyXYlKK5fWIUBIrQ7c&s=-SaofzmrHOf_
> WDgErh7uwwA6IFckntL5REoU84b1lFY&e=
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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