Oracle-L: RE: Alter table monitoring ... impact on performance??

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 16 Jan 2003 20:33:44 -0800
Message-ID: <F001.00532732.20030116203344@fatcity.com>

Bruce,

I need it just for that ... "identifying tables with stale stats ..."

I feel so much better now that I am ready to pitch this idea to dev team with no other options ... !!

Raj

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Thursday, January 16, 2003 6:59 PM To: Multiple recipients of list ORACLE-L

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

HTH,
Bruce Reardon

-----Original Message-----
Sent: Friday, 17 January 2003 10:26 AM

I've seen mention a of negligible performance hit for this.

It was from someone I trust, but I can't recall just who that was.

Jared

"Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> Sent by: root_at_fatcity.com 01/16/2003 12:50 PM

Subject: Alter table monitoring ... impact on performance??

Does any one know the performance impact on 'alter table monitoring' ?? (this is for Oracle 9202) Should we expect any slowness ?? Raj

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) INET: Bruce.Reardon_at_comalco.riotinto.com.au 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).

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.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).

text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Jan 16 2003 - 22:33:44 CST