RE: What are the gory details behind DBMS_STATS.AUTO_SAMPLE_SIZE?
- From: "Schultz, Charles" <sac@xxxxxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Fri, 2 Jun 2006 15:20:22 -0500
Well this is a fun can of worms - now how do I answer all the new
questions that spawn from this little exercise? =)
Yes, I did find a whole slew of curious hints. Thanks. But also some sql
commands I had not seen before, like "sample" and "sample block".
Unfortunately, that is a common enough word it is hard to find any
specific reference on it. I am also starting to wish that the "record"
option of tkprof had a way to do recursive calls, and also splice in
bind variables to boot.
I just have to spend a few hours getting over the puzzle of this trace
file....
________________________________
From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Friday, June 02, 2006 2:06 PM
To: Schultz, Charles
Cc: oracle-l
Subject: Re: What are the gory details behind
DBMS_STATS.AUTO_SAMPLE_SIZE?
Hi
It isn't documented and is subject to change from version to version and
patchset to patchset. That all said sql_trace can be enlightening here.
You even get some curious hints to play with in your sandbox.
cheers
Niall
On 6/2/06, Schultz, Charles <sac@xxxxxxxxxxxxx> wrote:
I am curious how the algorithms behind
DBMS_STATS.AUTO_SAMPLE_SIZE actually pick a sample size (10.2.0.1). We
have witnessed a case where the sample size for a particular column was
reduced 91.6%, causing the number of distinct values to be inaccurate,
further causing a query to choose an FTS rather than an index. As far as
I can tell, the skew has not changed that much, if at all, and the
overall volume has increased by ~1%.
I am aware of workarounds (compute stats, lock stats, use an
outline, etc), but like I said, I am really curious why the algorithm
made such a drastic jump in the first place. I could not easily locate
an appropriate white paper on metalinks, and I get too many hits on
Google. Can anyone help satiate my burning desire to know? =)
charles schultz
oracle dba
aits - adsd
university of illinois
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
