Re: [External] dbmst_stats estmate
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Jan 2017 11:13:31 +0000
(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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Niall Litchfield <niall.litchfield@xxxxxxxxx>
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@xxxxxxxxxxxx<
mailto:jack@xxxxxxxxxxxx>> 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@xxxxxx<
mailto:Hemant-K.Chitale@xxxxxx>> 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/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>
[
mailto: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<
mailto: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
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
