Diary of a Confused DBA…..

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….

<Start Script>

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

<End Script>