Hi Wolfgang, In the grand scheme of things, that probably isn't awful. However, if the analyze can't get the row count right (how easy is that?) then how can I trust it to get the rest of the statistics correct? Just my two cents.
Thanks for your reply. Lisa -----Original Message----- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L I wouldn't call 1.4% [ (603826-595500)/603826 ] "way wrong". Actually, for a 1% sample I find that pretty good. The problem I found with low sampling percentages is if you have skewed column values. If some values occur very often and others rather seldom, a 1% sample may only encounter the frequently occurring values and none of the infrequently occurring ones and come up with a really way off estimate for num_distinct. At 10:05 AM 5/29/2003 -0800, you wrote: >Hi John, > >Yes, monitoring was set. I wouldn't see anything in *tab_modifications if >monitoring wasn't set. > >Here's a new twist. What percentage are you comfortable with for valid >estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, >and he stated that adequate statistics can be gathered using 1% >sample. That was great news to me, who has time for huge estimates? OK, >so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and >estimate_percent set to 1. The rowcounts are way wrong. > >(FACP-LISA)>SELECT COUNT(*) > 2 FROM VEGAS_MART > 3 PARTITION (MAY_28_2003); > > COUNT(*) >---------- > 603826 > >(FACP-LISA)>select num_rows > 2 from dba_tab_partitions > 3 where table_name = 'VEGAS_MART' > 4 and partition_name = 'MAY_28_2003'; > > NUM_ROWS >---------- > 595500 > >(FACP-LISA)> > >And Jonathan if you happen to read this email, if I am mis-stating what >you stated in class please correct me. I am on 8.1.7.4 and that may be >the difference. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
