Matt,
That is correct.
"estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE" is the default setting from
Oracle 11G and it perform a full table scan to determine the table statistics
very accurately.
If you are on Oracle 12C, this has the added advanage that the 2 new histogram
types - Hybrid and Top Frequency - are allowed to be created. This is a very
good thing as the hybrid histograms are significantly better than the height
balanced histograms they replace. Additionally, the gathering of Frequency and
Top Frequency histograms is effectivley free when using the defaults as it
allows Oracle to use a "HyperLogLog" algorythm to get the approvimate_ndv
values in a single pass. This also makes those histograms hugely accurate and
stable compared to histograms created by performing adaptive sampling of data.
I would recommend that you do not change from this default unless you have a
good, *proven* reason for the change.
(Whether you want lots of histograms on your data is a very different question)
regards
Neil Chandler
Database Guy
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Matt Adams <MAdams@xxxxxxxxxx>
Sent: 17 January 2018 16:19
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
