Originally posted by sysdba no script, but there is a way.
Put the index into a dedicated tablespace and
use statspack to monitor whether there is read from
Well, the question was something like that:
"I have 821 indexes on my system and I want to determine which of those 821 indexes is never used."
So you are suggesting to create 821 datafiles, put each index into a sepparate datafile and monitor datafile usage? Or are you suggesting to create one separate tablespace/datafile, and put each index into that tablespace one at a time for a monitoring period? Khm...
Anyway, even if you try to do this index-isolation method you'll find out that is not that straightforward as it seems at first. As chao_ping has allready mentioned, index writes might also cause index reads - some writes will be preceded with reads, some will not. So how will you tell which reads were caused by writes (inserts, deletes, updates) and which were not? Unless of course the table is a read-only one...
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Tom Kyte in 1-on-1 suggests capturing stored outlines, from which you can extract a list of used indexes. I have a performance problem with this - so I can't just have it set all the time on production, which is really what I want.