1 2 Previous Next 22 Replies Latest reply: Mar 12, 2013 5:04 PM by jgarry RSS

    Monitoring the index usage in oracle 11g

    776454
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              yes dba_* system views has all objects from db.
                              • 12. Re: Monitoring the index usage in oracle 11g
                                Mohamed Houri
                                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
                                  Charles Hooper
                                  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
                                    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