Best way to determine if an index is being used

that has indexes I do not think are being used, but is there a way in Oracle to show an index is never used? It has been suggested that I monitor the shared pool, but this does not seem like the most efficient way to determine if an index is being used.

I've run across this question in the past. And from what you've already indicated, you probably are already aware that there is no easy way to find out if a table or index is ever used. For a table, one could set up auditing on the table. For tables and indexes, one could query and find out what is *currently* in the buffer cache, but what if the index has been aged out of the cache before your query runs? You would miss it. Similarly, one could query the shared pool to see if there are queries which could be using the index. But again, the query could be aged out of the shared pool. It is unfortunate that there is no DBA_ view or V$ view which gives this information. But I suppose this is due to the fact that updating a view every single time a block is accessed out of an index or a table would be costly and could severly hamper the overall performance of your database.

But all is not lost! The best trick I've seen is to put the objects in question in their own tablespace. For instance, let's suppose that I want to verify that my TABLE_A_IDX index is being used. I create a tablespace for just this index (with the CREATE TABLESPACE command). I then move the index into that tablespace with the "ALTER INDEX table_a_idx REBUILD TABLESPACE new_ts;" command. If you are checking on a table (and have Oracle 8i), then you can easily move the table with the "ALTER TABLE table_a MOVE TABLESPACE new_ts;" command.

For this trick to work, it is imperative that this tablespace contains only one object! Now that you have an object in a tablespace by itself, it is pretty easy to see if that object is being accessed. Simply query from V$FILESTAT to determine if the tablespace's datafile has had I/O operations on it. Since there is only one object in that tablespace, then it follows that that one object must have had I/O on it! Use the following query to determine if I/O on that tablespace (and therefore the object) has taken place:

   SELECT d.file_name,s.phyrds,s.phywrts
   FROM   dba_data_files d, v$filestat s, v$dbfile f
   WHERE  f.file#=s.file# AND d.file_name=f.name
   AND    d.tablespace_name='NEW_TS';

If there are read (PHYRDS) or write (PHYWRTS) operations, then you know that that tablespace and its only object have experienced I/O. Therefore, that object has been used.

For More Information

• What do you think about this answer? E-mail us at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.