But Oracle 9 lets you monitor index usage, hoorah !
(so they say).
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
Mark D Powell wrote in message
<178d2795.0106040553.12cc9cf_at_posting.google.com>...
>"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 --
Received on Sun Jun 03 2001 - 14:38:04 CDT
