Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: any over head with DBMS_STATS.ALTER_DATABASE_TABLE_MONITORING
Too bad Steve Adams' site is not available, cuz that's
the place to be.
He says it better than I can, so I've appended a bit of info from him. I'd suggest getting to Steve's site as soon as it's up.
We have monitoring enabled on our 9.2.0.4 database, but it's not currently heavily used. But so far, it's been great.
>From Steve Adam's July 2000 newsletter
(http://www.ixora.com.au/newsletter/2000_07.htm, line
spacing below is mine):
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 is 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.
"
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: barbarabbaker_at_yahoo.com 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: ListGuru_at_fatcity.com (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).Received on Thu Jan 22 2004 - 11:39:26 CST
![]() |
![]() |