RE: [External] dbmst_stats estmate
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/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-lReceived on Fri Jan 20 2017 - 06:52:15 CET