I haven't read the note yet, but what Rich mentions here may explain why I haven't encountered any problems. I started out testing with copies of prod tables which had no stats at all. So their first stats were gathered with gather_shema_stats.
>>> [EMAIL PROTECTED] 05/29/03 03:14PM >>> I looked at bug 1890016 on MetalClink and I'm confused. Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's test case? I thought this was a no-no??? If so, I'm wondering if it would it help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY, insert/update/delete test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL or the new 9i method that escapes me), then try DBMS_STATS w/STALE option. Just a thought... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: Koivu, Lisa [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 29, 2003 9:46 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: dbms_stats broken > > > Hi Darrell & Kirti - > > It was late last night when I was looking at this. It seems > I should be using GATHER EMPTY instead of GATHER STALE. > However this will put statistics on partitions with no rows. > > When I load new partitions tomorrow (they are empty prior to > the daily load), the number of rows inserted wouldn't reach > 10% change. It would take over a week to reach 10% change in > order for GATHER STALE to pick up on this and re-analyze > these partitions. I don't want statistics saying there are 0 > records in a partition that is indeed loaded. > > I guess GATHER STALE would be much more useful if the 10% > threshold could be modified, and/or the threshold could be > partition specific. > > And Kirti, the bugs I have seen are 1192012, 1890016, > 2157655. I thought I was running into 1890016. > > Looks like I'll have to code around this after all. Darn it > all. I really am a monkey. Sheesh > > Lisa > > > -----Original Message----- > Sent: Thursday, May 29, 2003 12:30 AM > To: Multiple recipients of list ORACLE-L > > > 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: Jesse, Rich 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: 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).
