Have you got some large indexes creeping around?
Some of my systems have 10GB indexes.
These take valuable resources away from the Oracle database:
– DML time (INSERT, UPDATE etc).
– Stats generation time.
– Segment space (hard disk).
– Structure validation time.
– Recovery time.
Removing the index may not be an option. But what if it’s not actually used!!
How can we tell if it’s used?
Well you could monitor all SQL in the shared pool using the AWR capture script https://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_usage_tracking.htm, or you could use Jonathan’s script https://jonathanlewis.wordpress.com/segment-scans/ to see if the index segment(s) has been scanned (full scan). But these don’t comprehensively give you a definitive answer.
There could be holes in your monitoring.
Instead, you could use the V$OBJECT_USAGE view to monitor index usage.
Although a very basic method, if you only want to know definitively if an index has been used or not, then it gives you the answer.
It is described in great detail here: https://wiki.oracle.com/page/Monitor+Index+Usage
Turn on:
alter index <INDEX> monitoring usage;
ALTER INDEX SAPSR3."JCDS~Z01" monitoring usage;
Turn off:
alter index <INDEX> nomonitoring usage;
ALTER INDEX SAPSR3."JCDS~Z01" nomonitoring usage;
Check usage (must be as owner of monitored objects):
select count(1) from v$object_usage;
Or use query below to see all monitored objects:
SELECT
u.name owner ,
io.name index_name ,
t.name table_name ,
DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring ,
DECODE(bitand(ou.flags, 1), 0, 'NO', 'YES') used ,
ou.start_monitoring start_monitoring ,
ou.end_monitoring END_MONITORING
FROM
sys.user$ u ,
sys.obj$ io ,
sys.obj$ t ,
sys.ind$ i ,
sys.object_usage ou
WHERE
i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#
AND u.user# = io.owner#;
