Index Utilization
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Index Utilization

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  2. #2
    Join Date
    Oct 2000
    Posts
    467
    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.
    Vinit

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Thanks
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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#
    /

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    10x Pando, the output was I suspected. Very good query.


  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2001
    Posts
    16
    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

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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.
    I liked that :-) I agree with you on that.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width