This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Mar 12, 2013 3:04 PM by jgarry RSS

Monitoring the index usage in oracle 11g

776454 Newbie
Currently Being Moderated
Hi,

We have customer requirement to monitor the Indexes which are not used and redundant.

oracle 11g
Linux platfrom
Sise 2 TB
Number of indexes is 9000 + a
user indexes is 7000+

What would be best way to monitor indexes ?

i assume alter

ALTER INDEX <indx name> MONITORING USAGE;
ALTER INDEX <indx name> NOMONITORING USAGE;

and get the report from v$object_usage is good for one or two but

what would be best method to capture for bulk index usage?

like
to run a statistics with “Oracle index usage monitoring” available in 11g ?

Thanks
  • 1. Re: Monitoring the index usage in oracle 11g
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    It would be very simple to
    set pages0
    spool enable_monitoring.sql
    select 'set echo on' from dual;
    select 'alter index   ' || index_name || '  monitoring usage ;' 
    from user_indexes
    order by 1;
    spool disable_monitoring.sql
    select 'set echo on' from dual;
    select 'alter index   ' || index_name || '  nomonitoring usage ;' 
    from user_indexes
    order by 1;
    spool off
    But enabling monitoring is not the right thing to do for at least --3-- 4 important reasons :
    1. It can falsely capture an index that is being incorrectly used by the optimizer -- i.e. a sub-optimal that the optimizer selects (for whatever reasons, including bad statistics)
    2. It can falsely capture an index that is used only once in one query out of a million queries that have been executed
    3. It can falsely NOT capture an index that is used only on specific days -- e.g. month-end reports which require a few specific indexes not used for the 28 other days of the month
    4. It can falsely NOT capture indexes that are required for constraints, simply because they are not used in queries (although they are important to maintain data consistency !)



    Hemant K Chitale

    Edited by: Hemant K Chitale on Jan 14, 2011 5:01 PM
  • 2. Re: Monitoring the index usage in oracle 11g
    776454 Newbie
    Currently Being Moderated
    Hi,

    Thanks for this quick help.

    My question is in the v$object_usage can be populated 7000+ rows means i am going to monitor 7000 + indexes at one shot in the view of v$object_usage ?


    I understand the notes/Reasons for false ones.. but we have the time line to monitor for 3 weeks where the peak load is expected. really thanks for this notes again.

    Thanks.
  • 3. Re: Monitoring the index usage in oracle 11g
    mtefft Journeyer
    Currently Being Moderated
    You could first identify those indexes that are required for 'logical' reasons:
    - They support a primary-key or unique constraint
    - They support a foreign-key constraint
    If the database was designed 'rationally' with primary keys and foreign keys, this would probably reduce the scope of your analysis significantly.
  • 4. Re: Monitoring the index usage in oracle 11g
    776454 Newbie
    Currently Being Moderated
    Hi,

    Today i tested but i am not seeing the object_usage is not populated... any idea ? am i missing anything here?

    SQL> alter index WYX.AB_FK2 monitoring usage ;

    Index altered.

    SQL> select count(*) from v$object_usage;

    COUNT(*)
    ----------
    0

    SQL> alter index WYX.AB_FK2 nomonitoring usage ;

    Index altered.
  • 5. Re: Monitoring the index usage in oracle 11g
    Mohamed Houri Pro
    Currently Being Moderated
    Dear Hermant

    You can add also to your list

    5. It can falsely NOT capture FK indexes that are used by Oracle behind the scene to avoid the locking threat


    [http://richardfoote.wordpress.com/2008/09/12/index-monitoring-and-foreign-keys-caution-kid-a/]

    Best Regards

    Mohamed Houri
  • 6. Re: Monitoring the index usage in oracle 11g
    776454 Newbie
    Currently Being Moderated
    hi,

    any answers for my question.

    any view exist to see the index monitoring? i understand only owner can log in and see the v$object_usage not the DBA..

    any views exists at DBA level to see the usage?

    Thanks.
  • 7. Re: Monitoring the index usage in oracle 11g
    Mohamed Houri Pro
    Currently Being Moderated
    Dear,
    any answers for my question
    In my opinion, you don't have to rely on this index usage monitoring in order to make a decision to drop the non used indexed as reported by this monitoring. It is the wrong path to follow.

    Instead, you can look to your indexes (particularly the b-tree ones) and try to find redundant indexes. By redundant indexes I mean indexes like the following
     SELECT   table_name
            ,index_name
            ,column_name
            ,column_position
        FROM user_ind_columns
    ORDER BY 
         table_name
        ,index_name;
    And look to your output if you have such a kind of following situation
     table_name   index_name   column_name  column_position
       t1               ind1        col1                  1
       t1               ind2        col1                  1
       t1               ind2        col2                  2
    In this situation we know that index ind1 is redundant because index ind2 contains already the col1 in position 1.

    This index Ind1 is candidate to be dropped. BUT, I will advise you even in this case to be carreful when droping the ind1 Index.

    It is always preferable to do not create a redundant index than to drop it afterwards.

    Because there exist situations where Index ind1 might have a perfect clustering factor which makes it desirable by the Oracle Optimiser when executing one of your queries. And when you will drop it (hoping that your query will be covered by ind2), the Oracle Optimiser will go for a FULL Table scan because the High Clustering Factor of Index Ind2 makes it not desirable by the Oracle Optimiser.

    So, you can
    (a) locate redundant indexes
    (b) drop them but test, test, test and test before implementing in PROD

    Best Regards

    Mohamed Houri
  • 8. Re: Monitoring the index usage in oracle 11g
    EdStevens Guru
    Currently Being Moderated
    user13051945 wrote:
    Hi,

    Today i tested but i am not seeing the object_usage is not populated... any idea ? am i missing anything here?

    SQL> alter index WYX.AB_FK2 monitoring usage ;

    Index altered.

    SQL> select count(*) from v$object_usage;

    COUNT(*)
    ----------
    0

    SQL> alter index WYX.AB_FK2 nomonitoring usage ;

    Index altered.
    How much time elapsed between your first two statements (turning on monitoring and selecting from v$object_usage)? If, as appears, they were back-to-back, then it is most likely no usage was generated in the 2 seconds it took you to type the second command. It's not like the stats are already being collected.
  • 9. Re: Monitoring the index usage in oracle 11g
    EdStevens Guru
    Currently Being Moderated
    Mohamed Houri wrote:
    Dear,
    any answers for my question
    In my opinion, you don't have to rely on this index usage monitoring in order to make a decision to drop the non used indexed as reported by this monitoring. It is the wrong path to follow.

    Instead, you can look to your indexes (particularly the b-tree ones) and try to find redundant indexes. By redundant indexes I mean indexes like the following
    SELECT   table_name
    ,index_name
    ,column_name
    ,column_position
    FROM user_ind_columns
    ORDER BY 
    table_name
    ,index_name;
    And look to your output if you have such a kind of following situation
    table_name   index_name   column_name  column_position
    t1               ind1        col1                  1
    t1               ind2        col1                  1
    t1               ind2        col2                  2
    In this situation we know that index ind1 is redundant because index ind2 contains already the col1 in position 1.

    This index Ind1 is candidate to be dropped. BUT, I will advise you even in this case to be carreful when droping the ind1 Index.

    It is always preferable to do not create a redundant index than to drop it afterwards.
    Yes, but the OP is dealing with the hand that was dealt to him. Hindsight is always 20-20 (and hopefully instructive) but we still have to deal with the situation as it exists today.
    >
    Because there exist situations where Index ind1 might have a perfect clustering factor which makes it desirable by the Oracle Optimiser when executing one of your queries. And when you will drop it (hoping that your query will be covered by ind2), the Oracle Optimiser will go for a FULL Table scan because the High Clustering Factor of Index Ind2 makes it not desirable by the Oracle Optimiser.
    I've never had to wrestle with clustering factor of indexes, but I'd think that if two indexes were defined the same, but actually varied due to clustering, you'd still be safe to drop one and simply rebuild the remaining one or re-gather stats on it.
    So, you can
    (a) locate redundant indexes
    (b) drop them but test, test, test and test before implementing in PROD

    Best Regards

    Mohamed Houri
  • 10. Re: Monitoring the index usage in oracle 11g
    776454 Newbie
    Currently Being Moderated
    Agreed.

    My concern is how to find out the usage of user level indexes as DBA?

    i can see user_ind_columns...is there i can see in dba_ind_coumns?

    Thanks
  • 11. Re: Monitoring the index usage in oracle 11g
    Zoltan Kecskemethy Expert
    Currently Being Moderated
    yes dba_* system views has all objects from db.
  • 12. Re: Monitoring the index usage in oracle 11g
    Mohamed Houri Pro
    Currently Being Moderated
    Dear EdStevens,
    I've never had to wrestle with clustering factor of indexes, but I'd think that if two indexes were defined the same,
     but actually varied due to clustering, you'd still be safe to drop one and simply rebuild the remaining one or re-gather stats on it.
    The clustering factor, I believe, will not change when you gather stats on its corresponding index. The Clustering Factor indicates how well data are packed within the index structure. If you see that the clustering factor of the index approaches the number of rows in the table then this is an indication of a bad clustering factor. If you know the value that corresponds to a good clustering factor then you can set the CF with this value using dbms_stats.set_index_stats.

    But we will not rebuild an index only to generate a perfect clustering factor. And I doubt that rebuilding the index will generate a good CF at all.

    I will give it a try and let you know

    Best Regards

    Mohamed Houri
  • 13. Re: Monitoring the index usage in oracle 11g
    CharlesHooper Expert
    Currently Being Moderated
    Mohamed,

    I am probably misunderstanding a little about what you wrote, and if that is the case, there is a chance that other people may also misunderstand.

    Rebuilding an index will not change its true clustering factor (the estimated value for the clustering factor may change, but not if the sample size is 100%). The clustering factor desribes the order of the index entries in relation to the order of the data in the table blocks. If that is what you were stating, I apologize for misunderstanding.

    Richard Foote has a little bit of information on the subject:
    http://richardfoote.wordpress.com/2008/01/21/introduction-to-reverse-key-indexes-part-iv-cluster-one/

    Jonathan Lewis wrote a chapter on the subject:
    http://apress.com/book/downloadfile/2410

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 14. Re: Monitoring the index usage in oracle 11g
    Mohamed Houri Pro
    Currently Being Moderated
    Charles,

    Thanks for your comment. Yes you are right. I haven't been clear with my own words.

    This is why I will re-phrase what I wanted to say about this thread and I will be very happy if you could correct me if I am still not getting the point

    (a) do not rely on index monitoring usage to take decision of dropping reported non used indexes
    (see richard foote link about the FK index usage monitoring )

    (b) This is what I wanted to say about the Clustering Factor definition using Jonathan Lewis words

    "The clustering factor is a single number that represents the degree to which data is randomly distributed through a table"

    This is what I wanted to say also about the CF value (using richard foote words)

    •“Good” CF generally has value closer to blocks in table
    •“Bad” CF generally has a value closer to rows in table

    (c) This is what I wanted to say about dropping redundant indexes

    Be carreful when you drop a redundant indexes because you might drop the index that has a good CF "

    (d) To EdStevens who said that he has never had to wrestle with clustering factor of indexes; in presence of redundant indexes he said, he will drop one index and rebuild or gather stats of the remaining index
    do you agree with EdStevens suggestion?


    Best Regards

    Mohamed Houri
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points