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

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

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

Search Discussions

  • Orr, Steve at Jan 16, 2003 at 9:14 pm
    Once you have stats calc'd the impact of table monitoring is negligible...
    at least in my experience on 8.1.7.



    Steve Orr
    Bozeman, MT



    -----Original Message-----
    Sent: Thursday, January 16, 2003 1:50 PM
    To: Multiple recipients of list ORACLE-L

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

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Orr, Steve
    INET: sorr_at_rightnow.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).
  • Stephen Andert at Jan 16, 2003 at 9:30 pm
    I don't know about 9, but we've been using it on 8.1.7.3 for over 6
    months and have not noticed any negatives.

    Good luck and let us know what you find out. We're planning a move to
    9 this year.

    Stephen
    Rajendra.Jamadagni_at_espn.com 01/16/03 01:50PM >>>
    Does any one know the performance impact on 'alter table monitoring'
    ??
    (this is for Oracle 9202) Should we expect any slowness ??

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Stephen Andert
    INET: StephenAndert_at_firsthealth.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).
  • Jamadagni, Rajendra at Jan 16, 2003 at 9:33 pm
    Thanks Stephen,

    I am steering the dev team towards 100% compute statistics (once) and then
    set-up automated jobs to collect when stats become stale. To do this I need
    to enable monitoring (to decide on staleness). This is a heavy OLTP system
    running in RAC ...

    Initially we will test this in the dev environment, but that is RAC too..
    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    -----Original Message-----
    Sent: Thursday, January 16, 2003 4:28 PM
    To: Jamadagni, Rajendra; ORACLE-L_at_fatcity.com

    I don't know about 9, but we've been using it on 8.1.7.3 for over 6
    months and have not noticed any negatives.

    Good luck and let us know what you find out. We're planning a move to
    9 this year.

    Stephen
    Rajendra.Jamadagni_at_espn.com 01/16/03 01:50PM >>>
    Does any one know the performance impact on 'alter table monitoring'
    ??
    (this is for Oracle 9202) Should we expect any slowness ??

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    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
  • Jared.Still_at_radisys.com at Jan 16, 2003 at 11:25 pm
    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_at_fatcity.com
    01/16/2003 12:50 PM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    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

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc.
    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:
    INET: Jared.Still_at_radisys.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
  • Reardon, Bruce (CALBBAY) at Jan 16, 2003 at 11:59 pm
    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_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).
  • Jamadagni, Rajendra at Jan 17, 2003 at 4:33 am
    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-----

    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_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
  • Jared.Still_at_radisys.com at Jan 17, 2003 at 5:44 pm
    That's the one I had in mind.

    It was from Steve.

    Jared

    "Reardon, Bruce (CALBBAY)"
    Sent by: root_at_fatcity.com
    01/16/2003 03:59 PM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    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

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 16, '03 at 8:50p
activeJan 17, '03 at 5:44p
posts8
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase