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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Wednesday, January 17, 2018 2:34 PM
To: oracle-l@xxxxxxxxxxxxx; MAdams@xxxxxxxxxx
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Matt Adams <MAdams@xxxxxxxxxx>
Sent: 17 January 2018 16:19:35
To: oracle-l@xxxxxxxxxxxxx
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=
--
//www.freelists.org/webpage/oracle-l
