Mladen,
Sweeping generalisations are not helpful, and this is the 2nd time you have
made this one, fishing for a response. So let me respond.
Some of the time a full table scan may be problematically slow, but it is
simplistic to assume that sampling a fractional percentage will result in good
overall outcomes.
The full scan provides other benefits, by minimising additional work needed to
be taken by the stats gathering job. It ensures increased accuracy of the
stats, improves single-sample adaptive stat collection by understanding NULL
distributions (and therefore sample escalations are proactively anticipated),
and allows perfect frequency and top frequency histograms at marginal
additional cost. It is needed for Hybrid histograms to replace the largely
ineffectual Height-balanced histograms. It allows the use of approximate_ndv to
lower the overall impact of the stats gather. All of those benefits are lost by
implementing any other estimate_percent.
Given estimate_percent=auto_sample_size is the Oracle default, and has been
through several major releases, there is a good chance that it has rather a lot
of benefits. As a default, it's not going away.
If your table is so large that the scan time is unacceptable, the better
solution is more likely to be to implement partitioning and use incremental
stats (or a locked-partition manual version), or maybe even look to manually
fabricate your stats if you know how to do that. It depends upon your goals
(and licensing).
If you are going to suggest alternatives, please ensure you list at least some
of the benefits and disbenefits associated with the alternatives so people can
make informed descisions about their approach.
regards
Neil Chandler
Database Guy.
________________________________
On 01/17/2018 12:48 PM, Neil Chandler wrote:
"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.
And for some tables, full table scan is unacceptable, regardless of the
accuracy.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
