Home » RDBMS Server » Performance Tuning » I need a listing of Oracle indexes and how often they are used, how?  () 1 Vote
I need a listing of Oracle indexes and how often they are used, how? [message #202631] Fri, 10 November 2006 11:01 Go to next message
aidi-h
Messages: 44
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 #202633 is a reply to message #202631] Fri, 10 November 2006 11:23 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can look into [DBA|ALL|USER]_INDEXES to view indexes in the database.

To monitor index usage, as you mentioned, enabling monitoring on the index is going to be your best option, however you may want to look at the physical and logical statistic values in v$SEGMENT_STATISTICS.
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 Go to previous messageGo to next message
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 #203243 is a reply to message #202631] Tue, 14 November 2006 07:54 Go to previous messageGo to next message
aidi-h
Messages: 44
Registered: November 2005
Member
I dont seem to have any records in the v$segment_statistics table. Does something have to switch this on
Re: I need a listing of Oracle indexes and how often they are used, how? [message #203263 is a reply to message #203243] Tue, 14 November 2006 09:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
From the docs

alter index EXP_ERR_CODE_PK monitoring usage;
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 Go to previous messageGo to next message
aidi-h
Messages: 44
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
Re: I need a listing of Oracle indexes and how often they are used, how? [message #203329 is a reply to message #203243] Tue, 14 November 2006 15:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
aidi-h wrote on Tue, 14 November 2006 08:54
I dont seem to have any records in the v$segment_statistics table. Does something have to switch this on

Do you mean there are zero rows when you query V$SEGMENT_STATISTICS or that there are no records for the segment in particular ? This view should be updated automatically without any prior preparation on your part.

Re: I need a listing of Oracle indexes and how often they are used, how? [message #203537 is a reply to message #203286] Wed, 15 November 2006 07:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I believe it creates an entry whenever the index is used, regardless of what type of DML operation uses it.
Previous Topic: Analyze tables
Next Topic: Pls help in reducing the buffer gets for the query below
Goto Forum:
  


Current Time: Sun Dec 11 02:43:19 CST 2016

Total time taken to generate the page: 0.07362 seconds