Printable View
Hi Friends.
I have many indexes in my database taking a lot of space.
I have noticed some indexes on columns in tables which I am sure are never/rarely used, apparently the developers have put them there for the hell of it.
How can I find how often(if ever) an index has been utilised for queries so that I can identify those indexes that can be dropped ?
Thanks in advance
Suresh
Hi,
For that you need to know what sqls ur running in your application and if those sqls are picking up indexes or not. However in 9i you have a new feature called
alter index index_name monitoring usage .
Have a look on Note:144070.1 on metalink.
Thanks
there is this script on ixora
Code:
-------------------------------------------------------------------------------
--
-- Script: redundant_indexes.sql
-- Purpose: to find any redundant indexes
-- For: 8.0 and 8.1
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
column redundant_index format a39
column sufficient_index format a39
select
o1.name||'.'||n1.name redundant_index,
o2.name||'.'||n2.name sufficient_index
from
sys.icol$ ic1,
sys.icol$ ic2,
sys.ind$ i1,
sys.obj$ n1,
sys.obj$ n2,
sys.user$ o1,
sys.user$ o2
where
ic1.pos# = 1 and
ic2.bo# = ic1.bo# and
ic2.obj# != ic1.obj# and
ic2.pos# = 1 and
ic2.intcol# = ic1.intcol# and
i1.obj# = ic1.obj# and
bitand(i1.property, 1) = 0 and
( select
max(pos#) * (max(pos#) + 1) / 2
from
sys.icol$
where
obj# = ic1.obj#
) =
( select
sum(xc1.pos#)
from
sys.icol$ xc1,
sys.icol$ xc2
where
xc1.obj# = ic1.obj# and
xc2.obj# = ic2.obj# and
xc1.pos# = xc2.pos# and
xc1.intcol# = xc2.intcol#
) and
n1.obj# = ic1.obj# and
n2.obj# = ic2.obj# and
o1.user# = n1.owner# and
o2.user# = n2.owner#
/
10x Pando, the output was I suspected. Very good query.
But just for possible clarification to original poster (I don't doubt Julian or Pando know this): dont be misled by the query Pando provided.
It reports redundant indexes, not neccesserily indexes that are never used. Redundant indexes here means there are two indexes where one of them is allready "covered" by another one.
For example, you can have index1 on (c1, c2, c3) and another index2 on (c1, c2). In this case index2 will be reported as "redundant" and you can safely drop it, unless you have very good reasons to have them both, for example where you have some extreme performance demands for particular queries and want to use index2 instead of index1.
But the results of pando's query dont tell you anything about your original question: are those indexes ever used in the application? All of the indexes reported as redundand and all of indexes reported as sufficient in the query might be used havily, or none of them might ever be used. And there might be tons of indexes in your database that are not reported by this query at all, but none of them is ever used by your application.
To detect such "unused" indexes with 100% certainty is extremely difficult (actually it is impossible, unless certain specific conditions are met) prior to 9i. There are some methods to detect if a particualr indexe is allmost certanly not used, but they can be quite expensive in the terms of effort to detect them.
Probably the most cost effective method is to simply drop the suspected index and wait by the telephone for some angry users to start complaining. Of course, be sure in advance that this method will not possibly jeopardize your current job ;).
[Edited by jmodic on 12-07-2001 at 03:37 PM]
works fine on 8i, but when I use it on 8.0.5, I got Ora-00936,
missing expression at begining of the following select.
( select
max(pos#) * (max(pos#) + 1) / 2
from
sys.icol$
where
obj# = ic1.obj#
) =
Does this kind of expess. only work for 8i and above?
Thanks
James
I liked that :-) I agree with you on that.Quote:
Probably the most cost effective method is to simply drop the suspected index and wait by the telephone for some angry users to start complaining.