RE: dbms_stats

Lisa,

Have a look at ML Note:44961.1 (and maybe Note:114671.1 could also help). The 1% sample may work for larger tables - I am not sure....

John

> -----Original Message-----
> From: Koivu, Lisa [mailto:Lisa.Koivu_at_Fairfieldresorts.com]
> Sent: Thursday, May 29, 2003 11:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: dbms_stats
>
>
> 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.
>
>
>
> -----Original Message-----
> Sent: Thursday, May 29, 2003 1:36 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Lisa et al,
>
> Am I missing something or did someone ever mention that for
> STALE to work,
> one needs to set MONITORING on required objects? Straight
> from the Fine
> Manual:
>
> Enabling Automated Statistics Gathering
> The GATHER STALE option only gathers statistics for tables
> that have stale
> statistics and for which you have enabled the MONITORING attribute. To
> enable monitoring for tables, use the MONITORING keyword of
> the CREATE TABLE
> and ALTER TABLE statements, as described in "Designating Tables for
> Monitoring and Automated Statistics Gathering" on page 8-9.
>
> John Kanagaraj
> Oracle Applications DBA
> DBSoft Inc
> (W): 408-970-7002
>
> Great, uplifting music (and best of all commercial-free!) -
> http://www.klove.com
>
> ** The opinions and statements above are entirely my own and
> not those of my
> employer or clients **
>
>
> > -----Original Message-----
> > From: Darrell Landrum [mailto:dlandrum_at_zalecorp.com]
> > Sent: Wednesday, May 28, 2003 9:30 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: dbms_stats broken
> >
> >
> > Lisa,
> >
> > Wow, you might be saving me from peril right now. I have
> tested this
> > with a small set of tables with no problems (in and 8.1.7.4
> > instance).
> > I'm preparing to go 'schema' wide in the next week or so for further
> > testing prior to implementing in production.
> > I'd be very interested in more details of your problems in
> 8.1.7.4 and
> > of course I'll post reports of testing to the list as well.
> >
> > For starters, here is the code I use to obtain a list of 'stale'
> > qualified tables:
> > ( For proper credit, I think I got this from asktom.oracle.com)
> >
> >
> > set serverout on size 90000
> >
> > declare
> > l_objList dbms_stats.objecttab;
> > begin
> > dbms_stats.gather_schema_stats
> > ( ownname => '&1',
> > options => 'LIST STALE',
> > objlist => l_objlist );
> > for i in 1 .. l_objlist.count
> > loop
> > --dbms_output.put_line( l_objlist(i).objtype );
> > dbms_output.put_line( l_objlist(i).objname );
> > end loop;
> > end;
> > /
> >
> >
> > And the code to gather stats:
> >
> > set serverout on size 99000
> >
> > begin
> > dbms_stats.gather_schema_stats(
> > ownname=>'&1',
> > options=>'GATHER STALE',
> > cascade=>TRUE,
> > degree=>8,
> > granularity=>'ALL',
> > method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1'
> > );
> > end;
> > /
> >
> >
> > Thanks,
> > Darrell
> >
> >
> > >>> Lisa.Koivu_at_Fairfieldresorts.com 05/28/03 09:24PM >>>
> > Hello everyone,
> >
> > Is anyone using dbms_stats and gather stale or gather auto in 9.2?
> > I'm
> > trying to use dbms_stats gather schema stats with the stale
> option and
> > it just isn't working in 8.1.7.4. This is documented on Metalink.
> > I'd
> > love to hear from someone else if this is fixed in 9.2 and if it can
> > be
> > reliably used.
> >
> > Thank you
> > Lisa Monkey.
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Darrell Landrum
> > INET: dlandrum_at_zalecorp.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: John Kanagaraj
> INET: john.kanagaraj_at_hds.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).