Lisa, >From Steve Adams site:
Despite the potential for improved statistics gathering, many DBAs have not yet adopted modification monitoring. One of the concerns that people have is that the monitoring might have a significant performance overhead. In fact, this is not the case. The modification counts are maintained in an efficient hash table in the SGA, and are updated without the protection of a latch (although the structure of the hash table itself is protected by the hash table modification latch). Even in heavy OLTP environments, the cost of maintaining the modification counts is likely to be less than 1% of additional CPU usage. However, because of the latch-free nature of the feature, the modification counts are not guaranteed to be accurate. Another source of potential inaccuracy is that if a transaction is rolled back, its changes to the modification counts are not rolled back as well. These inaccuracies have been allowed by Oracle to keep the performance overhead of this feature minimal. Therefore, you can use modification monitoring with confidence that it will not affect performance significantly, while giving you a very helpful indication of which tables may have stale statistics. http://www.ixora.com.au/newsletter/2000_07.htm#monitoring Jared On Wednesday 29 January 2003 07:09, Koivu, Lisa wrote: > 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 > > ---------------------------------------- > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > Content-Transfer-Encoding: quoted-printable > Content-Description: > ---------------------------------------- ---------------------------------------- Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: ---------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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).
