This is 9i feature. For 8i we dont have anything to monitor index usage as such.. but, we have something to monitor segment i/o. catio.sql is the script provided for this purpose. But, this script never worked and didnt have patience to gig into this script after that.. Script is under default $ORACLE_HOME/rdbms/admin location. Will be happy if somebody could experiment and post the results ;)
Originally posted by stmontgo
start with monitoring the indexes to see if they get used
with ALTER INDEX MONITORING USAGE and then look at v$object_usage..
Opionions may vary on how many indexes are just right. On a DW table who cares, if it's oltp you'll want to benchmark at which point
diminishing returns make extra indexes more harmful then helpful