Oracle index usage

If you want to get basic usage statistics of indexes recently used in your Oracle instance, you can use this SQL sentence:

SQL> select p.object_owner, p.object_name, sum(t.disk_reads_total) as disk_reads_sum,
sum(t.rows_processed_total) as rows_processed_sum from dba_hist_sql_plan p, dba_hist_sqlstat t
where p.sql_id = t.sql_id and p.object_type like '%INDEX%' and p.object_owner not in
('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','TSMSYS')
group by p.object_owner, p.object_name order by p.object_owner, 4 desc;

OBJECT_OWNER         OBJECT_NAME                     DISK_READS_SUM ROWS_PROCESSED_SUM
-------------------- ------------------------------- -------------- ------------------
MYSCHEMA01           IDX_MYTABLE01                           620641               6209
MYSCHEMA01           IDX_MYTABLE02                           569879               4965
MYSCHEMA01           IDX_MYTABLE03_PK                            20               4793
MYSCHEMA01           IDX_MYTABLE04_PK                            20               4793
MYSCHEMA02           IDX_MYTABLE05                           414609           19940082
MYSCHEMA02           IDX_MYTABLE06                           559721            3776187
MYSCHEMA02           IDX_MYTABLE07                          1165298             621298
MYSCHEMA02           IDX_MYTABLE08_PK                           795             107678
MYSCHEMA02           IDX_MYTABLE09_PK                        174079              78627