Any script to find which index has never been used.?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Any script to find which index has never been used.?

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    Any script to find which index has never been used.?

    Hi,

    Any script to find which index has never been used.?

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Not that I know of, unless that information is explicitly captured in some db trigger.

    There are bigger brains than mine on this list... I'm interested in what they say.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, there is (in general) no way to find this out, at least not until 9i. In 9i you have the ability to monitor the index usage.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2000
    Posts
    295
    no script, but there is a way.

    Put the index into a dedicated tablespace and
    use statspack to monitor whether there is read from
    that tablespace.

  5. #5
    Originally posted by sysdba
    no script, but there is a way.

    Put the index into a dedicated tablespace and
    use statspack to monitor whether there is read from
    that tablespace.
    When index is updated, isn't it read into memeory first?
    www.cnoug.org

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Yes you can put the index in a dedicated tablespace and use v$filestat to mointor it..it would give you a crude way of finding out wheather a index is used or not..

    regards
    Hrishy

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    No, there is (in general) no way to find this out, at least not until 9i. In 9i you have the ability to monitor the index usage.
    Please can u get details as to monitor usage of index on 9x

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    In 9i you have something called as v$object_useage..through which you can monitor the index useage to a certain extent..

    alter index my_index monitoring useage;

    it cant tell us how many times a index has been used or nor hwne it was used..

    The used column in v$object_useage will have an yes if the index was used since we turned on monitoring

    But read the oracle docs before using it..

    regards
    Hrishy

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sysdba
    no script, but there is a way.

    Put the index into a dedicated tablespace and
    use statspack to monitor whether there is read from
    that tablespace.
    Well, the question was something like that:

    "I have 821 indexes on my system and I want to determine which of those 821 indexes is never used."

    So you are suggesting to create 821 datafiles, put each index into a sepparate datafile and monitor datafile usage? Or are you suggesting to create one separate tablespace/datafile, and put each index into that tablespace one at a time for a monitoring period? Khm...

    Anyway, even if you try to do this index-isolation method you'll find out that is not that straightforward as it seems at first. As chao_ping has allready mentioned, index writes might also cause index reads - some writes will be preceded with reads, some will not. So how will you tell which reads were caused by writes (inserts, deletes, updates) and which were not? Unless of course the table is a read-only one...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I have come across two possible methods for 8i:

    Tom Kyte in 1-on-1 suggests capturing stored outlines, from which you can extract a list of used indexes. I have a performance problem with this - so I can't just have it set all the time on production, which is really what I want.

    John Weeg in http://www.dbazine.com/weeg12.html gives a solution by periodically sampling the objects in the buffers. I'm working on this now.

    Either way: unused-indexes = all-indexes MINUS used-indexes.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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