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

Charles,  

SAMPLE and SAMPLE BLOCK are documented features. See:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opti mops.htm#73147  

for some info...  

-Mark
 

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schultz, Charles Sent: Friday, June 02, 2006 4:20 PM
To: Niall Litchfield
Cc: oracle-l
Subject: RE: What are the gory details behind DBMS_STATS.AUTO_SAMPLE_SIZE? 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_at_gmail.com] 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_at_uillinois.edu> 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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 02 2006 - 15:33:54 CDT