Gopal, I should have waited a bit longer.. It was about 12 minutes, when I replied...
Okay, I will test it out tomorrow.. It's getting late :( Now, go eat your lunch.. it's about lunch time for you... :) Regards, - Kirti -----Original Message----- From: K Gopalakrishnan [mailto:[EMAIL PROTECTED]] Sent: Wed 1/29/2003 10:58 PM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Global Stats Kirti: Sorry for the typo. It is 15 minutes. --- K Gopalakrishnan <[EMAIL PROTECTED]> wrote: > Kirti: > > I think the interval is changed to 5 minutes from > 3 hours starting from 9i (rel2?). > > > > Best Regards, > K Gopalakrishnan > > > > > -----Original Message----- > Kirti > Sent: Wednesday, January 29, 2003 8:19 PM > To: Multiple recipients of list ORACLE-L > > > Lisa, > Monitoring, by itself, does not fire any automatic analyze. It > simply > montiors the DML activity on the monitored table and counts > inserts/deletes/upates. Those counts may not be 100% accurate, but > are very > close. These can be viewed in dba_tab_modifications, and are dumped > there by > SMON every 3 hours or so (in 9i there is a new procedure, > flush_database_monitoring_info, to flush these counts to this view on > demand). These counts do not affect the ones maintained in *_tables > views. > > Monitoring is basically there to help identify which tables may need > statistics computed again. 'Gather stale' option will only analyze > tables > that have undergone DML activity (inserts/deletes/updates) that > amounts to > more than 10% of the number of rows (from previous analyze) in the > table. > And 'gather auto' option 'figures' out what tables to analyze, but > you must > execute dbms_stats. So, there is nothing automatic in gathering table > stats. > > You can test it yourself..... remember there is a last_analyzed > column ;) > > HTH, > > - Kirti > > > > > -----Original Message----- > From: Koivu, Lisa [mailto:[EMAIL PROTECTED]] > Sent: Wed 1/29/2003 9:09 AM > To: Multiple recipients of list ORACLE-L > Cc: > Subject: RE: Global Stats > > Hi Jared, > > Actually I think monitoring won't work in my case. Data loads fire > throughout the day and the docs say that in 8i, analyze can fire > based upon > table monitoring sometime within 3 hours after data changes. I would > rather > include a manual fire of analyze in my data load and avoid any > locking > issues or contention for resources. > > In addition, if temp space is blown during "auto-analyze" (fired > based upon > monitoring), would I know about it? > > Just my thoughts. Am I wrong? > > Lisa > > -----Original Message----- > Sent: Wednesday, January 29, 2003 3:55 AM > To: Multiple recipients of list ORACLE-L > > > > You may want to read up on table monitoring. > > Jared > > On Tuesday 28 January 2003 11:10, Koivu, Lisa wrote: > > Hi everyone, > > > > Back to the lovely world of Oracle :) I've been reading up on > statistics. > > Out of the 8.1.7 doco: > > /* > > Partitioned schema objects may contain multiple sets of statistics. > They > > can have statistics which refer to the entire schema object as a > whole > > (global statistics), they can have statistics which refer to an > individual > > partition, and they can have statistics which refer to an > individual > > subpartition of a composite partitioned object. > > > > Unless the query predicate narrows the query to a single partition, > the > > optimizer uses the global statistics. Because most queries are not > likely > > to be this restrictive, it is most important to have accurate > global > > statistics. Intuitively, it may seem that generating global > statistics > from > > partition-level statistics should be straightforward; however, this > is > only > > true for some of the statistics. For example, it is very difficult > to > > figure out the number of distinct values for a column from the > number of > > distinct values found in each partition because of the possible > overlap in > > values. Therefore, actually gathering global statistics with the > DBMS_STATS > > package is highly recommended, rather than calculating them with > the > > ANALYZE statement > > > > */ > > The table I need to generate stats for is currently 32GB and grows > by ~2GB > > per week. Even the smallest estimate with calculating global stats > will > > take a long long time and I may not be able to spring for all the > required > > temp space. > > > > How does the list feel about global stats? Does anyone agree with > the > > documentation that they "most important"? I'm thinking my > partitioned > > statistics are the "most important". > > > > Any input is appreciated. Thanks > > > > Lisa Koivu > > Oracle Database Administrator > > Fairfield Resorts, Inc. > > 5259 Coconut Creek Parkway > > Ft. Lauderdale, FL, USA 33063 >
<<winmail.dat>>