FAQ
DB Version: 10.2.0.5
I want to turn index monitoring on in spots to see if some of our indexes
are being used. We have an very active OLTP, so before doing that I am
running some tests of index monitoring.
Has anyone else noticed this? This is fairly large for the simple tests I
am running


tool: runstats
I have a database all to myself

Using one of our tables.

2 indexes
1 column non-unique
4 column unique

I made a copy of this table with the same indexes. So one table would have
index monitoring turned on and one did not have index monitoring turned.

The increase in waits happens on inserts. I am inserting 50 records at a
time as a test.

I have run this test many times and the waits are consistent. I also
dropped and created new tables. This increase is consistent.
I also change the test so that the 2nd insert is to the table with index
monitoring.
I am not turning index monitoring on during the test. I have 2 different
tables. One with index monitoring and one with out (doing that would skew
the results)

Run 1 is on a table with index monitoring on 1 non-unique index
Run 2 does not have index monitoring on.


LATCH.library cache lock 214 32 -182
LATCH.row cache objects 321 93 -228
STAT...recursive calls 304 2 -302

Search Discussions

  • Jonathan Lewis at Feb 10, 2012 at 11:04 am
    This seems a little surprising.

    Have you run the test with SQL trace enabled so that you can see if there
    is any recursive SQL to account for the difference.

    Every time you optimize a statement that uses a monitored index Oracle will
    attempt to insert or update a row in a real table to show that the index is
    monitored - so some related activity may account for what you see.

    Can you create a simple reproducible test that show the effect on a
    sequence of steps like:

    create table
    insert data
    commit

    insert
    commit

    enable monitoring
    insert
    commit



    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Oracle Core (Apress 2011)
    http://www.apress.com/9781430239543


    ----- Original Message -----
    From: "Dba DBA" <oracledbaquestions@gmail.com>
    To: "ORACLE-L" <oracle-l@freelists.org>
    Sent: Wednesday, February 08, 2012 7:35 PM
    Subject: increase in waits when index monitoring is on


    DB Version: 10.2.0.5
    I want to turn index monitoring on in spots to see if some of our indexes
    are being used. We have an very active OLTP, so before doing that I am
    running some tests of index monitoring.
    Has anyone else noticed this? This is fairly large for the simple tests I
    am running


    tool: runstats
    I have a database all to myself

    Using one of our tables.

    2 indexes
    1 column non-unique
    4 column unique

    I made a copy of this table with the same indexes. So one table would have
    index monitoring turned on and one did not have index monitoring turned.

    The increase in waits happens on inserts. I am inserting 50 records at a
    time as a test.

    I have run this test many times and the waits are consistent. I also
    dropped and created new tables. This increase is consistent.
    I also change the test so that the 2nd insert is to the table with index
    monitoring.
    I am not turning index monitoring on during the test. I have 2 different
    tables. One with index monitoring and one with out (doing that would skew
    the results)

    Run 1 is on a table with index monitoring on 1 non-unique index
    Run 2 does not have index monitoring on.


    LATCH.library cache lock 214 32 -182
    LATCH.row cache objects 321 93 -228
    STAT...recursive calls 304 2 -302


    --
    http://www.freelists.org/webpage/oracle-l




    -----
    No virus found in this message.
    Checked by AVG - www.avg.com
    Version: 2012.0.1913 / Virus Database: 2112/4791 - Release Date: 02/05/12


    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Feb 10, 2012 at 11:06 am
    This seems a little surprising.

    Have you run the test with SQL trace enabled so that you can see if there
    is any recursive SQL to account for the difference.

    Every time you optimize a statement that uses a monitored index Oracle will
    attempt to insert or update a row in a real table to show that the index is
    monitored - so some related activity may account for what you see.

    Can you create a simple reproducible test that show the effect on a
    sequence of steps like:

    create table
    insert data
    commit

    insert
    commit

    enable monitoring
    insert
    commit




    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Oracle Core (Apress 2011)
    http://www.apress.com/9781430239543


    ----- Original Message -----
    From: "Dba DBA" <oracledbaquestions@gmail.com>
    To: "ORACLE-L" <oracle-l@freelists.org>
    Sent: Wednesday, February 08, 2012 7:35 PM
    Subject: increase in waits when index monitoring is on


    DB Version: 10.2.0.5
    I want to turn index monitoring on in spots to see if some of our indexes
    are being used. We have an very active OLTP, so before doing that I am
    running some tests of index monitoring.
    Has anyone else noticed this? This is fairly large for the simple tests I
    am running


    tool: runstats
    I have a database all to myself

    Using one of our tables.

    2 indexes
    1 column non-unique
    4 column unique

    I made a copy of this table with the same indexes. So one table would have
    index monitoring turned on and one did not have index monitoring turned.

    The increase in waits happens on inserts. I am inserting 50 records at a
    time as a test.

    I have run this test many times and the waits are consistent. I also
    dropped and created new tables. This increase is consistent.
    I also change the test so that the 2nd insert is to the table with index
    monitoring.
    I am not turning index monitoring on during the test. I have 2 different
    tables. One with index monitoring and one with out (doing that would skew
    the results)

    Run 1 is on a table with index monitoring on 1 non-unique index
    Run 2 does not have index monitoring on.


    LATCH.library cache lock 214 32 -182
    LATCH.row cache objects 321 93 -228
    STAT...recursive calls 304 2 -302


    --
    http://www.freelists.org/webpage/oracle-l




    -----
    No virus found in this message.
    Checked by AVG - www.avg.com
    Version: 2012.0.1913 / Virus Database: 2112/4791 - Release Date: 02/05/12


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 8, '12 at 7:36p
activeFeb 10, '12 at 11:06a
posts3
users2
websiteoracle.com

2 users in discussion

Jonathan Lewis: 2 posts Dba DBA: 1 post

People

Translate

site design / logo © 2022 Grokbase