Re: Tuning - Indexes and Usage

"Devdewboy" <devdewboy_at_hotmail.com> wrote in message news:<blGS6.8764$3t5.261151_at_news.pacbell.net>...
> Good Morning,
>
> Is there a script someone can provided me that will query the dynamic views
> for index usages i.e. If an index has ever been utilized (this specifically)
> and other useful info?
>
> Thanks,
>
> dewboy

There isn't a v$ view that will really give you this information though there are v$ views you can use in a sampling task to find approximate usage. But with an index if the table has new data being added then the indexes on the table will be updated (assuming indexed columns present in data) even if no query will ever use them.

You could run the log miner utility and look for activity, but again that is only going to show you that the index was updated not used in a query.

An extreme method might be to run trace for the entire instance, tkprof all trace files, and grep for the index in question. But on a busy system this could result in thousands of trace files being created.

The view most often used for sampling is probably v$cache, which is an OPS view of the instance buffer pool but can be created and used in a non-ops instance if you have the enterprise edition so you can run catparr ( notice parr not proc).

• Mark D Powell --