Home » RDBMS Server » Performance Tuning » Does Altering Index Monitoring have Locks
Does Altering Index Monitoring have Locks [message #225314] Mon, 19 March 2007 12:15 Go to next message
Messages: 294
Registered: February 2006
Senior Member

One doubt on a 24/7 OLTP application issuing this command will locks the table/Index,

alter index index_name monitoring usage;

Becuase what happened while starting to issue above command every thing seems to hang for some time, wanted to know does

Alter index monitoring also locks the table or what.

Oracle 9i Rel 2 Version

Re: Does Altering Index Monitoring have Locks [message #225873 is a reply to message #225314] Wed, 21 March 2007 15:49 Go to previous messageGo to next message
Messages: 9
Registered: March 2007
Junior Member
I am using it and do not see any handgin issues. I do not think it locks table to turn on monitoring on the indexes
Re: Does Altering Index Monitoring have Locks [message #228344 is a reply to message #225314] Mon, 02 April 2007 11:12 Go to previous message
Messages: 49
Registered: March 2005
This website has details about the impact of monitoring indexes.


flipping the monitoring state for an index invalidates any execution plans in the library cache which happen to make use of that index. Once invalidated, re-issuing the original query will cause the statement to have to be re-parsed, which won't do your library cache hit ratio any good, and (rather more importantly) is a serialisation and CPU performance problem in the making. It is for this reason that Oracle's own documentation for the index monitoring feature says words to the effect 'It is not recommended to use it on a heavily-used system or in the middle of heavy DML'.

Putting it bluntly: you don't want to be mucking around with index monitoring just out of idle curiosity, because it can have measurable performance impacts on your database by virtue of its invalidating parts of your library cache.
Previous Topic: Query regarding deleting the reords from DB
Next Topic: Tuning
Goto Forum:

Current Time: Fri Aug 18 04:00:44 CDT 2017

Total time taken to generate the page: 0.14298 seconds