RE: dbms_stats

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: 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Koivu, Lisa
  INET: Lisa.Koivu_at_Fairfieldresorts.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).