Tags
I used to have a good query that I was happy with to do this, but I apparently lost it. Here’s some queries I’ve cobbled together today to identify full table scans and check their SQL…and determine that a maybe some new indexes might be in order based on real world queries…
select * from v$sql_plan where operation = ‘TABLE ACCESS’ and options = ‘FULL’
AND timestamp > sysdate – .01
order by TIME
and use the hash value to get the SQL:
select * from v$sql
where HASH_VALUE = {hash value of a result}