Re: increase in waits when index monitoring is on

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 10 Feb 2012 11:03:44 -0000
Message-ID: <280AB073BA0E4FACB39CF8228C47590B_at_Primary>

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_at_gmail.com> To: "ORACLE-L" <oracle-l_at_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 Received on Fri Feb 10 2012 - 05:03:44 CST