Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

finding unused indexes

user11278505Dec 23 2009 — edited Dec 23 2009
Hi all,

I have to find unused indexes for a particular table..

I am using below script to findout and remove the unused indexes.

select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner = 'NAME'
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;

so what my question is using this output of this script can i remove the indexes which are used very less or not all used.
and also this query gets from views

dba_hist_sql_plan p,
dba_hist_sqlstat s

so may i know how many days data will be there in these views.
so the above query gets the results about index usage of how many days????

Thanks

Comments

591186
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
sb92075
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.
Robert Geier
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
user11278505
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
Anand...
but regarding monitoring index does it effect performce...
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
591186
But regarding monitoring index does it effect performce...
No.
and also monitoring index does not show any percenterage of usage of index....
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
Robert Geier
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.
591186
Check this one:

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


HTH
-Anantha
Robert Geier
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.
591186
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.
1 person found this helpful
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 20 2010
Added on Dec 23 2009
#general-database-discussions
10 comments
2,618 views