Any script to find which index has never been used.?

[skdas]

Hi,

Any script to find which index has never been used.?

[KenEwald]

Not that I know of, unless that information is explicitly captured in some db trigger.

There are bigger brains than mine on this list... I'm interested in what they say.

[jmodic]

No, there is (in general) no way to find this out, at least not until 9i. In 9i you have the ability to monitor the index usage.

[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
that tablespace.

[chao_ping]

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
that tablespace.

When index is updated, isn't it read into memeory first?

[hrishy]

Hi

Yes you can put the index in a dedicated tablespace and use v$filestat to mointor it..it would give you a crude way of finding out wheather a index is used or not..

regards
Hrishy

[abhaysk]

Originally posted by jmodic
No, there is (in general) no way to find this out, at least not until 9i. In 9i you have the ability to monitor the index usage.

Please can u get details as to monitor usage of index on 9x

Abhay.

[hrishy]

Hi

In 9i you have something called as v$object_useage..through which you can monitor the index useage to a certain extent..

alter index my_index monitoring useage;

it cant tell us how many times a index has been used or nor hwne it was used..

The used column in v$object_useage will have an yes if the index was used since we turned on monitoring

But read the oracle docs before using it..

regards
Hrishy

[jmodic]

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
that tablespace.

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...

[DaPi]

I have come across two possible methods for 8i:

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.

John Weeg in http://www.dbazine.com/weeg12.html gives a solution by periodically sampling the objects in the buffers. I'm working on this now.

Either way: unused-indexes = all-indexes MINUS used-indexes.