Re: AUTO_SAMPLE_SIZE is every row

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.

"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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Jan 27 2018 - 16:12:18 CET