Oracle Database Discussions

Enable monitoring and leave it for a while.

Use v$object_usage to see the usage.

[http://www.oracle-base.com/articles/10g/IndexMonitoring.php]


HTH
-Anantha

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1008.htm#i2050158

MONITORING USAGE | NOMONITORING USAGE

Use this clause to determine whether Oracle Database should monitor index use.

*

Specify MONITORING USAGE to begin monitoring the index. Oracle Database first clears existing information on index use, and then monitors the index for use until a subsequent ALTER INDEX ... NOMONITORING USAGE statement is executed.
*

To terminate monitoring of the index, specify NOMONITORING USAGE.

To see whether the index has been used since this ALTER INDEX ... NOMONITORING USAGE statement was issued, query the USED column of the V$OBJECT_USAGE dynamic performance view.

I don't think the DBA_HIST tables are guaranteed to record every transaction, so I don't think you can use the contents to identify indexes to drop.

It would be better to use index monitoring to identify unused indexes (but I have seen some reports that this is not accurate when the index is used by foreign keys)

You may find the following useful :-

http://blog.contractoracle.com/2009/09/index-monitoring-to-identify-unused.html

but regarding monitoring index does it effect performce...

and also monitoring index does not show any percenterage of usage of index....

if the index is used once it just updates v$object_usage to YES..and i may think that index is used very often ..which may not be the case..which i think gud to consider..

is there anyother way..

thanks

and also monitoring index does not show any percenterage of usage of index....>

No its doesn't affect the performance.You can put the indexes in monitoring for the few days and see what all indexes are being used and which are not.Those which are not being used can be dropped.As far as the percentage part is considered , yes monitoring does not show the percentage of index being used.It just shows whether the index is being used or not.And if the index is being used you shouldn't drop it.Dropping it can let you be more trouble, as the execution plan may change you then you again recreate the index.


Anand

Edited by: Anand... on Dec 24, 2009 12:03 AM

No.
Your objective is to find whether the index is being used or not. You can join dba_hist_sqltext and dba_hist_snapshot to find the context where they are used.

HTH
-Anantha

You said you want to identify what indexes are used. That is what monitoring does, with no performance impact.

An index is either used, or it is not. There cannot be any percentage.

Other options would be to check what index blocks are in the buffer cache, but this is dynamic and an index used yesterday may have been aged out of memory.

Check this one:

[http://www.orafaq.com/node/53]


HTH
-Anantha

The DBA_HIST tables should not be used to identify indexes safe to drop.

AWR only collects stats at regular intervals, and plans age out of the SGA, so the DBA_HIST tables don't show ALL of the transactions.

Agreed.

I provided the link just for the information to see how many times and under what context they are being used within a AWR snapshot.

I was trying to provide that we can use dba_hist tables to find the frequency of usage.