Re: [External] dbmst_stats estmate
Date: Fri, 20 Jan 2017 11:13:31 +0000
Message-ID: <MMXP123MB0911AC23C8D574D56987885CA5710_at_MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>
>> (from memory 5,500 - I think this figure is due to Jonathan Lewis ).
Don't blame me, it wasn't my idea, I only reported it. e.g. http://jonathanlewis.wordpress.com/2015/09/30/estimate_percent/
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Niall Litchfield <niall.litchfield_at_gmail.com> Sent: 20 January 2017 11:01
To: Jack van Zanen
Cc: Chitale, Hemant K; ORACLE-L
Subject: Re: [External] dbmst_stats estmate
In 11g AUTO_SAMPLE_SIZE for histograms is only a few thousand rows (from memory 5,500 - I think this figure is due to Jonathan Lewis ).
On Fri, Jan 20, 2017 at 8:07 AM, Jack van Zanen <jack_at_vanzanen.com<mailto:jack_at_vanzanen.com>> wrote: Thanks for the links...i think you are right about the column histograms....we have a very skewed table and the histograms with estimate are giving us grief... For now i have locked in the stats that seem to work for us while i do some more investigation.
On 20 Jan 2017 16:53, "Chitale, Hemant K" <Hemant-K.Chitale_at_sc.com<mailto:Hemant-K.Chitale_at_sc.com>> wrote: 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> [mailto: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<mailto: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
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 20 2017 - 12:13:31 CET