Re: Tuning - Indexes and Usage

Jonathan Lewis wrote:
>
> 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 --

...although if its 'alter index ... monitoring' then it would suggest that you'll get ins/upd/del stats only ... I hope I'm wrong.

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."