Re: Q: Identify indexes which are no longer used ?

From: Jurij Modic <jmodic_at_src.si>
Date: Fri, 02 Jul 1999 10:55:51 GMT
Message-ID: <37819916.15989712@news.siol.net>

On Thu, 01 Jul 1999 21:05:43 GMT, tedchyn_at_yahoo.com wrote:

>Sir, is there a way identifying obsolete indexes which are no longer
>used ?

Beside the method suggested by Jonathan Lewis, another way to find this would be to periodically check the SYS.V$BH view for any block of the suspected index being in the buffer cache. Something like:

SELECT COUNT(*) FROM v$bh b, dba_extents e

 WHERE b.file# = e.file_id
   AND b.block# BETWEEN e.block_id AND (e.block_id+e.blocks)
   AND e.owner = '&index_owner'
   AND e.segment_name = '&index_name';

This query could be set to execute periodically and insert the result into a separate table. The interval should depend on the activity of the database, or in other words, how frequently are the rarely used index blocks flushed out of the buffer cache.

Of course, this brings an overhead to the normal database operations, so if the performance of the application is of primary concern then I wouldn't run this query too often.

Note also that V$BH might not exist on your database. If so, run the script CATPARR.SQL, which will create it. Although this script is intended for parallel server environments, there is no harm if you run it on your single instance database.

>thaks in inadvance (tedcyn_at_yahoo.com)

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Jul 02 1999 - 05:55:51 CDT