An ABAP developer asked me: Is it possible to determine the usage of an index on database level?

Hmm … interesting question, I have never checked this before. So, I rolled up my sleeves and started with the investigations.

What I found are similar scripts from Mr. Burleson here

http://www.dba-oracle.com/oracle_tips_unused_indexes.htm

and from a guy soumen here

http://soumen.wordpress.com/my-work-2/how-many-indexes-which-are-in-use-and-which-are-abusive-to-your-sap/

Thanks to both!

Actually I changed a script a little bit and below is what I used, because I mainly interested in VBEP!Z01 index and my SAPR3 user is SAPASE:

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 = ‘SAPASE’

and p.operation like ‘%INDEX%’

and p.sql_id = s.sql_id

and p.object_name = ‘VBEP~Z01′

group by p.object_name, p.operation, p.options

order by p.object_name, p.operation, p.options;

The result was:

What does it mean? That means 53 INDEX RANGE SCAN operations on index VBEP~Z01.

Happy tuning!