| I need a listing of Oracle indexes and how often they are used, how? [message #202631] |
Fri, 10 November 2006 11:01  |
aidi-h
Messages: 45
Registered: November 2005
|
Member |
|
|
I would like to find out which how often all the indexes are being used on my DB.
I have found out that you can use the 'alter index <index name> monitoring usage' command (which might do the trick) however this, I believe, only works on one index. How can I produce a listing on my DB so I have all the indexes and the frequency they are used?
|
|
|
|
|
|
| Re: I need a listing of Oracle indexes and how often they are used, how? [message #202664 is a reply to message #202633] |
Fri, 10 November 2006 15:29  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The problem with Index monitoring is that it will only tell you if the index has been used since the last time monitoring was switched on.
If you want to know how often it is used, you will need to set up a scheduled job to copy v$segment_statistics to a table every <time interval>. This will let you know how ofter they are used to within the <time interval> you specify
|
|
|
|
|
|
|
|
| Re: I need a listing of Oracle indexes and how often they are used, how? [message #203286 is a reply to message #202631] |
Tue, 14 November 2006 11:08 |
aidi-h
Messages: 45
Registered: November 2005
|
Member |
|
|
Thanks for all the comments.
one further question, does the 'alter index monitoring' command create entries in the v$object_usage_view if a read is done.
I presume entries are created if an insert or update is carried out.
I want to find out which indexes are required and used during a weeks worth of processing for users (including their enquiry programs).
Thanks again
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
