| Does Altering Index Monitoring have Locks [message #225314] |
Mon, 19 March 2007 12:15  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
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
Thanks
|
|
|
|
|
|
| Re: Does Altering Index Monitoring have Locks [message #228344 is a reply to message #225314] |
Mon, 02 April 2007 11:12  |
gkodakalla
Messages: 49
Registered: March 2005
|
Member |
|
|
This website has details about the impact of monitoring indexes.
http://www.dizwell.com/prod/node/72
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
