Re: What are the gory details behind DBMS_STATS.AUTO_SAMPLE_SIZE?

  • From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • To: sac@xxxxxxxxxxxxx
  • Date: Fri, 2 Jun 2006 20:05:35 +0100

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:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2006