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>>
