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:[EMAIL PROTECTED] > 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:[EMAIL PROTECTED] > > 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 > > > > > > >>> [EMAIL PROTECTED] 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: [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: John Kanagaraj > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
