FAQ
Hey does anyone know if 10g has depreciated the monitor nomonitor options when you create index or table?



Looks like the default is to turn monitoring on in 10g. But I'm not sure.



Thanks,
Lizz

Search Discussions

  • Dennis Williams at May 30, 2008 at 12:17 am
    Lizz,

    Yes, according to the manual it has been depreciated.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2095331

    I am trying to locate the precise tool in 10g that replaces that
    functionality. I think it is the AWR.

    Dennis
  • Yong Huang at May 30, 2008 at 4:17 pm
    Lizz,

    In addition to what others said, here's what I wrote about statistics_level and
    the parameters controlled by it. Critique is welcome.

    Modification Monitoring: _dml_monitoring_enabled
    This 10g param allows you to do what you used to be able to do in 9i with alter
    table (no)monitoring, but not at table level any more; it's system wide either
    all monitoring or all nomonitoring. But I think it¡¯s still better to disable
    this parameter and leave statistics_level to typical than set statistics_level
    to basic when you want to disable table monitoring. Documentation writers must
    have a dilemma, though; they probably wish the first underscore were dropped.

    Yong Huang
    Date: Thu, 29 May 2008 14:21:41 -0700 (PDT)
    From: laura pena
    Subject: create table and create index monitor or nomonitor deprecated in 10g?
    Hey does anyone know if 10g has depreciated the monitor nomonitor options when
    you create index or table?


    Looks like the default is to turn monitoring on in 10g. But I'm not sure.


    Thanks,
    Lizz


    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at May 30, 2008 at 5:22 pm

    On Fri, May 30, 2008 at 9:17 AM, Yong Huang wrote:

    Modification Monitoring: _dml_monitoring_enabled
    This 10g param allows you to do what you used to be able to do in 9i with
    alter
    table (no)monitoring, but not at table level any more; it's system wide
    either
    all monitoring or all nomonitoring. But I think it¡¯s still better to
    disable
    this parameter and leave statistics_level to typical than set
    statistics_level
    to basic when you want to disable table monitoring. Documentation writers
    must
    have a dilemma, though; they probably wish the first underscore were
    dropped.
    There's at least one exception.

    If you are stuck on 10.2.0.2 (not allowed to upgrade) then you best not
    use ASMM, and have statistics_level = basic, as well as db_cache_advice=off.

    While benchmarking a 10.2.0.2 database a nasty performance bug appeared.

    See ML 5918631.8 and 3452409.8 regarding waits on 'simulator lru latch'

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfgang Breitling at May 30, 2008 at 5:54 pm
    You can turn monitoring on or off, at least for
    tables, database wide or schema wide with
    dbms_stats.alter_database_tab_monitoring or
    alter_schema_tab_monitoring respectively.

    As for ASMM, can anyone give me a good reason for using it?
    At 11:22 AM 5/30/2008, Jared Still wrote:
    On Fri, May 30, 2008 at 9:17 AM, Yong Huang
    <yong321_at_yahoo.com> wrote:
    Modification Monitoring: _dml_monitoring_enabled
    This 10g param allows you to do what you used to
    be able to do in 9i with alter
    table (no)monitoring, but not at table level any more; it's system wide either
    all monitoring or all nomonitoring. But I think it¡¯s still better to disable
    this parameter and leave statistics_level to typical than set statistics_level
    to basic when you want to disable table monitoring. Documentation writers must
    have a dilemma, though; they probably wish the first underscore were dropped.


    There's at least one exception.

    If you are stuck on <http://10.2.0.2>10.2.0.2
    (not allowed to upgrade) then you best not
    use ASMM, and have statistics_level = basic, as well as db_cache_advice=off.

    While benchmarking a <http://10.2.0.2>10.2.0.2
    database a nasty performance bug appeared.

    See ML 5918631.8 and 3452409.8 regarding waits on 'simulator lru latch'




    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com
  • Yong Huang at May 30, 2008 at 7:54 pm

    Jared Still wrote:
    On Fri, May 30, 2008 at 9:17 AM, Yong Huang wrote:

    Modification Monitoring: _dml_monitoring_enabled
    This 10g param allows you to do what you used to be able to do in 9i with
    alter
    table (no)monitoring, but not at table level any more; it's system wide
    either
    all monitoring or all nomonitoring. But I think it¡¯s still better to
    disable
    this parameter and leave statistics_level to typical than set
    statistics_level
    to basic when you want to disable table monitoring. Documentation writers
    must
    have a dilemma, though; they probably wish the first underscore were
    dropped.
    There's at least one exception.

    If you are stuck on 10.2.0.2 (not allowed to upgrade) then you best not
    use ASMM, and have statistics_level = basic, as well as db_cache_advice=off.

    While benchmarking a 10.2.0.2 database a nasty performance bug appeared.

    See ML 5918631.8 and 3452409.8 regarding waits on 'simulator lru latch'

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Thanks, Jared. I can't view 5918631.8 but I read 3452409.8 before. My article
    also says (hope April Sims won't mind) that "Whatever %simulator$ latch
    activity is probably due to these nearly unused advisors (people that know
    don't use it; people that don't know never check it!)." I was talking about
    "Shared Pool Advice: _library_cache_advice" but the idea is the same. Whether
    it's 10.2.0.2 or not, if you see too much simulator related latch contention,
    turn off db_cache_advice and _library_cache_advice. There're a few other
    %advice% params but it's better to work with Oracle to pinpoint exactly the one
    that caused the problem. Most (or all?) can be done with alter system
    dynamically so checking the effect is quick. db_cache_advice is particularly
    bad because you almost always give the rest of memory to buffer cache, and
    using it means 100 bytes is wasted for each buffer (Note:148511.1).

    Yong Huang

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 29, '08 at 9:21p
activeMay 30, '08 at 7:54p
posts6
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase