This is an usefull script, partaining to Oracle 10 G. Whenever you are doing performance analysis of oracle database behind an ERP like SAP or any big application, which has undergone lot of application maintenance, this will come in your use. The result output from this script, help you to findout which are unnecessary indexes in your database. Which you are using which are not.
I believe any insert, update or delete operation has always has an overhead if the underlying table has some indexes…..Removal of unnecessary indexes from database will help you curtail down the processing time overhead on indexes in any transaction processing system. Here is the excerpt….
col object_name format A30 col operation format A15 col options format A15 col ind_use_count format 99999999999 set Lines 150 set pages 2000 spool index_usage select i.table_name,i.index_name,nvl(x.operation,'Not Used') operation, nvl(x.options,'Not used') options,nvl(x.ind_use_count,0) ind_use_count from dba_indexes i,( select p.object_name, p.operation, p.options, count(1) ind_use_count from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner = 'SAPR3' and p.operation like ‘%INDEX%’ and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by p.object_name, p.operation, p.options) x where i.index_name = x.Object_name (+) ; Spool off set lines 80 set pages 20