Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: What are the gory details behind DBMS_STATS.AUTO_SAMPLE_SIZE?

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

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 5 Jun 2006 15:15:45 -0700
Message-ID: <BEE6A332AA61424EAE305CF89D6F75C84BB4C5@USSCCEVS101.corp.hds.com>


Charles,  

I have found some interesting stuff when I turned on a level 4 trace on a DBMS_STATS collection session using AUTO_SAMPLE_SIZE. Unfortunately, I did not document what I saw, but I do remember that it performed multiple scans.... Not a Good Thing (tm).  

Regards,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)   

http://www.klove.com <http://www.klove.com/> - Positive, encouraging music 24x7 worldwide   

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schultz, Charles Sent: Friday, June 02, 2006 10:52 AM
To: oracle-l
Subject: What are the gory details behind DBMS_STATS.AUTO_SAMPLE_SIZE?

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

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 05 2006 - 17:15:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US