Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h0H8dh802671 for ; Fri, 17 Jan 2003 02:39:43 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h0H8dgp02666 for ; Fri, 17 Jan 2003 02:39:42 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id VAA69396; Thu, 16 Jan 2003 21:21:52 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00532732; Thu, 16 Jan 2003 20:33:44 -0800 Message-ID: Date: Thu, 16 Jan 2003 20:33:44 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Jamadagni, Rajendra" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Jamadagni, Rajendra" Subject: RE: Alter table monitoring ... impact on performance?? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-37c46fa4-4d25-4ea2-8552-8938eea3045b" ------=_NextPartTM-000-37c46fa4-4d25-4ea2-8552-8938eea3045b Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2BDE1.32F87470" ------_=_NextPart_001_01C2BDE1.32F87470 Content-Type: text/plain; charset="iso-8859-1" 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@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" Sent by: root@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@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@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). ------_=_NextPart_001_01C2BDE1.32F87470 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Alter table monitoring ... impact on performance??

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-----
From: Reardon, Bruce (CALBBAY)
[mailto:Bruce.Reard= on@comalco.riotinto.com.au]
Sent: Thursday, January 16, 2003 6:59 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Alter table monitoring ... impact on = performance??


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@espn.com>
Sent by: root@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@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@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).

------_=_NextPart_001_01C2BDE1.32F87470-- ------=_NextPartTM-000-37c46fa4-4d25-4ea2-8552-8938eea3045b Content-Type: text/plain; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="ESPN_Disclaimer.txt" *********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1 ------=_NextPartTM-000-37c46fa4-4d25-4ea2-8552-8938eea3045b-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni@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@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).