Well, with very large tables, 100% sample size is completely
unacceptable. Full table scan, with or without distinct, can take
forever. However, for such tables one can set table preferences, which
would fix the situation.
On 01/17/2018 03:34 PM, Jonathan Lewis wrote:
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. ****
--
//www.freelists.org/webpage/oracle-l
