In 11g AUTO_SAMPLE_SIZE seems to be 100% or “almost 100%” {There is a
reference to this in MOS Document ID 749227.1}
The enhancement was to change the algorithm for NDV (Number of Distinct Values)
to run faster.
Pre 11g it really used to start with small numbers and if it determined the
sample was inadequate (algorithm unpublished I believe), re-gather with a
larger number. {See MOS Document ID 343849.1}
However, the sample size for Column Histograms can vary (algorithm unpublished
I believe). Column Histogram sample sizes are much smaller than the 100% or
near-100% used for Table statistics.
See http://structureddata.org/2007/09/17/oracle-11g-enhancements-to-dbms_stats/
https://blogs.oracle.com/optimizer/entry/improvement_of_auto_sampling_statistics_gathering_feature_in_oracle_database_11g
https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size
https://blogs.oracle.com/optimizer/entry/i_thought_the_new_auto
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jack van Zanen
Sent: Friday, January 20, 2017 11:42 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: [External] dbmst_stats estmate
Hi all,
Maybe an easy one but my google search terms were not sufficient to find an
answer :-)
How does oracle decide which blocks to sample when you leave estimate set to
auto?
Jack van Zanen
[https://docs.google.com/uc?id=0BwovDucFT1fXaEREVHNWRWZyNjg&export=download]
-------------------------
This e-mail and any attachments may contain confidential material for the sole
use of the intended recipient. If you are not the intended recipient, please be
aware that any disclosure, copying, distribution or use of this e-mail or any
attachment is prohibited. If you have received this e-mail in error, please
contact the sender and delete all copies.
Thank you for your cooperation
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html