RE: [External] dbmst_stats estmate

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Fri, 20 Jan 2017 05:52:15 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA6370CF56FC4_at_HKWPIPXMB03C.zone1.scb.net>



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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jack van Zanen Sent: Friday, January 20, 2017 11:42 AM
To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 20 2017 - 06:52:15 CET

Original text of this message