Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: Finding Bad Indexes

    Hi!
    Can soemone help me locating bad / unusable indexes in my database. Type of indexes that exist in my database are Normal and Bitmap.
    Database Version is 9i
    Any suggestion?

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Please define "bad"?

    If by bad you mean disabled use

    select index_name, status
    from dba_indexes
    where status = 'DISABLED'

    (this will work with user_indexes if it is your personal index)

  3. #3
    Join Date
    Jan 2004
    Posts
    84
    By Bad I mean indexes having become bigger in size than the tables say which are not helping in query execution in any way....
    Query to compare size of index with that of table takes really long where no. of indexes is pretty high...

  4. #4
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    Hi,

    If index size is greater than tablesize, then it is better to rebuild the indexes.

    there are few queries to check the index badness.

    1. first validate the index using ALTER INDEX <NAME> VALIDATE STRUCTURE.

    then you can query INDEX_STATS for lef_rows and del_lef_rows to know the percentage of deleted rows from the index. if the percentahe is more than 30% , then its better to rebuild.

    2. you can query dba_indexes and check blevel. if its greater than 4, then its a good candidate for rebuilding..
    Regards
    Suneel

  5. #5
    Join Date
    Jan 2004
    Posts
    84
    Thanks!

  6. #6
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333
    Hi,

    To locate Unusable indexes in UR Database,

    1. Create a SQL script to start monitoring all indexes except those
    owned by users SYS and SYSTEM

    2. Create another script to stop monitoring all indexes except those
    owned by users SYS and SYSTEM

    3. Connect as a user with ALTER ANY INDEX system privilege and run
    the start monitoring script

    4. Perform normal activities in your database

    5. After a period of time that is representative of your workload, run
    the stop monitoring script

    6. Query v$object_usage to see what indexes have not been used
    SATHISH .

  7. #7
    Join Date
    Jan 2004
    Posts
    84
    Thanks!
    Will enablinh Monitoring over several indexes(around 400 in number) not cause overhead on the system?

  8. #8
    Join Date
    Oct 2003
    Posts
    706

    Wink

    Monitoring will certainly affect the performance of the database while the monitoring is going on, but you can have the monitor turned-on for just a few minutes at a few representative times and gather a lot of the information you need to know.

    Choose times when the system is engaged in a representative variety of activities, not necessarily times when the quantity of work is extremely high (and the overhead of monitoring would thus be much more painful, plus the completion-times might be overstated due to limited CPU/IO resource availability).

    Also, focus on areas where you intuitively believe that a problem might exist. You put ointment only on places where it really hurts.

    Finally, consider the possibility that an index which has grown undesirably large might be a rather poor choice for an index; not a good one! Consider... An index like that is obviously highly volatile... the field value changes a lot, and is scattered among a wide domain of possible values. A "normal" index, if large, is clearly doing a lot of index-manipulation .. splits and joins and so-forth .. and doing it poorly. A "bitmap" index, if large, has a very large bitmap to deal with. It's worth experimenting, and of course, gathering data to prove or disprove the hypothesis.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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