Tuesday, October 30, 2007

Which indexes are used and how many times they are used?

Which indexes are used and how many times they are used?

col c1 heading object|Owner format a15
col c2 heading object|Name format a25
col c3 heading operation format a10
col c4 heading option format a15
col c5 heading index|Usage|Count format 999,999

select
p.object_owner c1,
p.object_name c2,
p.operation c3,
p.options c4,
count(1) c5
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like 'INDEX%' and
p.sql_id = s.sql_id
group by
p.object_owner,
p.object_name,
p.operation,
p.options
order by
1,2,3,4;