How many Indexes? Which are in use and which are abusive to your SAP?


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
Advertisements

5 thoughts on “How many Indexes? Which are in use and which are abusive to your SAP?”

  1. Wonderful goods from you, man. How many Indexes? Which are in use and which are abusive to your SAP? Diary of a Confused DBA….. I have understand your stuff previous to and you’re just extremely magnificent. I really like what you’ve acquired here, certainly like what you’re stating and the way in which you say it. You make it entertaining and you still take care of to keep it wise. I cant wait to read far more from you. This is actually a tremendous How many Indexes? Which are in use and which are abusive to your SAP? Diary of a Confused DBA….. informations.

  2. Hi and thank you for your post, i am wondering where would i execute this script on, is this a shell script that needs to be run on the Central Instance where the DB resides ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s