Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: dbms_stats
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: breitliw_at_centrexcc.com 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: ListGuru_at_fatcity.com (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).Received on Thu May 29 2003 - 14:25:21 CDT