Oracle Database Discussions

Hi all,

It's only been recently that I've been learning a small bit more about the AWR. One script I found (by Burleson) claims to report index invocation counts, etc. It also claims that if the index is not reported on, that's because it wasn't used, and hence isn't in the AWR.

I disagree with this. For example, I ran a simple statement which, according to the execution plan, would use index X -- I know this isn't a guarantee that it did use the index, but it executed quickly on a 1.2 million row table, so I can be pretty sure the index was used. But, this index usage never showed up in the AWR (yes, I did wait for later snapshots to be taken).

Then I read somewhere, that low intensive SQL, by default, isn't recorded anywhere in the AWR. Because it's not really interesting. Is this correct? Doesn't that make using the AWR for this sort of thing useless?

If so, can anyone suggest a reliable way of finding unused indexes using the AWR, besides index monitoring?

FYI, the script is something like this:

select p.object_name, count(*)
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and p.object_owner = 'MYSCHEMA' and p.operation = 'INDEX'
group by p.object_name;




Regards,
Cormac