1 2 Previous Next 22 Replies Latest reply: Mar 12, 2013 5:04 PM by jgarry Go to original post RSS
      • 15. Re: Monitoring the index usage in oracle 11g
        Charles Hooper
        Mohamed,

        Thank you for clarifying your words from the previous post - I requested the clarification just in case someone finds this thread at a later point in time through a search engine.

        I am not sure how to reply to Ed Steven's comment. I think that in part it depends on what he means by "redundant indexes". If he intended to suggest that if the clustering factor is poor for an index, that the index should be rebuilt or statistics should be gathered for the index, I think that your clarified post addresses that statement.

        Here is an interesting example that does show the clustering factor changing after a rebuilt, but there is a catch (read the comments carefully to see why the clustering factor changes):
        http://jonathanlewis.wordpress.com/2009/10/23/quiz-night-4/

        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.
        • 16. Re: Monitoring the index usage in oracle 11g
          krishan Jaglan
          Hi,

          Look like you using owner prefix with Index name, I presume you login using Sys or System but not by actual owner of index/table ie. WYX.

          If you login using WYX and query V$object_usage , you will see data, , if you login using any other including super user, no data will be shown as V$object_usage defination usage current user(login) as filter .

          Thanks
          krishan
          • 17. Re: Monitoring the index usage in oracle 11g
            Richard Foote-Oracle
            Others have already commented on the merits and otherwise of using index monitoring.

            Two other places to consider for determining the extent that an index is being used are:

            DBA_HIST_SQL_PLAN (assuming you have sufficient licencing), searching for which SQL plans are being used by which indexes. You can then tie the SQL_ID back to a number of other views, such as DBA_HIST_ACTIVE_SESS_HISTORY to determine the frequency of such usage. This will give you a good idea of how and when a particular index is (or not) being used.

            Another useful source of information is V$SEGMENT_STATISTICS. Those indexes with very high logical read counts are likely used a lot. Those indexes with logical reads in the same ball park as db block changes are likely used less frequently and worthy of more investigation.

            The golden rule I state with indexes is that it's really quite easy to create a new index. However, it's generally a lot lot harder to subsequently get rid of them :)

            Cheers

            Richard Foote
            http://richardfoote.wordpress.com/
            • 18. Re: Monitoring the index usage in oracle 11g
              user12057594
              Use the below query if login as sys or other administrative or other user than monitoring index owner schema.

              SELECT io.NAME index_name, t.NAME table_name, DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') Monitoring,
              DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') Used, ou.start_monitoring ,
              ou.end_monitoring
              FROM SYS.obj$ io, SYS.obj$ t, SYS.ind$ i, SYS.object_usage ou
              WHERE i.obj# = ou.obj#
              AND io.obj# = ou.obj#
              AND t.obj# = i.bo#;

              Regards
              Q.A.Rab
              • 19. Re: Monitoring the index usage in oracle 11g
                RByrd
                NB. in RAC, use gv$segment_statistics. You will see the instance level statistics and need to combine them. You will also see how work is balanced for particular indices.
                • 20. Re: Monitoring the index usage in oracle 11g
                  damorgan
                  Additionally monitoring will not capture situations where the optimizer uses the index stats to pick a good plan that does not include using the index on which the stats were collected.

                  Be very careful dropping indexes. Do it slowly, deliberately, and be prepared to recreate the index immediately if you find any of the situations we have discussed in this thread. They don't happen often ... but they do happen.
                  • 21. Re: Monitoring the index usage in oracle 11g
                    marksmithusa
                    Here are some of my notes. I'm fighting with index monitoring right now, as it turns out.

                    The object_usage view is actually coded to show only YOUR indexes that YOU have monitored. In order for you to see them all, you need to take that DDL and remove the user condition.

                    You can exclude your NONUNIQUE indexes from the list of indexes you monitor. You can exclude 'all but your app user' indexes from the list of indexes you monitor. Both of which narrow down things nicely.

                    +"It can falsely capture an index that is being incorrectly used by the optimizer"+ - it's still being used by the optimizer, though, so it is being used, whether a performance tuning or a statistics update would disagree or not.

                    +"It can falsely capture an index that is used only once in one query out of a million queries that have been executed"+ - again, it's still been used. That one query could be your year-end process that will run for weeks if you don't have that index in there. How many times it's been used isn't the point - it's the fact that it HAS been used

                    +"It can falsely NOT capture an index that is used only on specific days"+ - that's why you make sure the monitoring period is exhaustive: it captures your entire batch cycle (week, month, year)

                    The way we're identifying them is to allow for an entire batch cycle (13 weeks in one case) and for all indexes which have monitoring on and have NOT been used we provide a list to the application team.

                    If the application team has concerns, the index gets eliminated from the list. After a period of reflection (i.e. enough time for everyone to get their say), we make the indexes invisible. This keeps them up-to-date but tells the optimizer not to consider using them unless you specifically hint at doing so.

                    After a reasonable time with the indexes invisible and no users screaming or catastrophe caused, drop the index, but keep the DDL in case someone, somewhere does really need it.

                    Mark
                    • 22. Re: Monitoring the index usage in oracle 11g
                      jgarry
                      marksmithusa wrote:
                      Here are some of my notes. I'm fighting with index monitoring right now, as it turns out.

                      The object_usage view is actually coded to show only YOUR indexes that YOU have monitored. In order for you to see them all, you need to take that DDL and remove the user condition.

                      You can exclude your NONUNIQUE indexes from the list of indexes you monitor. You can exclude 'all but your app user' indexes from the list of indexes you monitor. Both of which narrow down things nicely.

                      +"It can falsely capture an index that is being incorrectly used by the optimizer"+ - it's still being used by the optimizer, though, so it is being used, whether a performance tuning or a statistics update would disagree or not.
                      This doesn't go far enough; it influences the optimizer even if it isn't used. I believe both Jonathan and Richard have demo'd that. Beyond that, the more modern baselines and adaptive cursors and all that stuff favor more indices to handle changing data distributions, while good development practices favors finding some "best" plan and locking that in, and storage indices turn everything on its head.

                      >
                      +"It can falsely capture an index that is used only once in one query out of a million queries that have been executed"+ - again, it's still been used. That one query could be your year-end process that will run for weeks if you don't have that index in there. How many times it's been used isn't the point - it's the fact that it HAS been used

                      +"It can falsely NOT capture an index that is used only on specific days"+ - that's why you make sure the monitoring period is exhaustive: it captures your entire batch cycle (week, month, year)
                      From the comments I've seen every time people ask about this on the forums, they really don't exhaust the possibilities; no one wants to wait more than a year, few want to put in the resources to optimize year-end performance.

                      >
                      The way we're identifying them is to allow for an entire batch cycle (13 weeks in one case) and for all indexes which have monitoring on and have NOT been used we provide a list to the application team.
                      I'm glad someone Does It Right (not sarcasm, either).

                      >
                      If the application team has concerns, the index gets eliminated from the list. After a period of reflection (i.e. enough time for everyone to get their say), we make the indexes invisible. This keeps them up-to-date but tells the optimizer not to consider using them unless you specifically hint at doing so.

                      After a reasonable time with the indexes invisible and no users screaming or catastrophe caused, drop the index, but keep the DDL in case someone, somewhere does really need it.
                      That invisible index thing seems to make all this fuss more reasonable. Edit: Except when it changes behavior over patch levels http://richardfoote.wordpress.com/2008/11/20/visible-invisible-indexes-the-invisible-band/

                      Edited by: jgarry on Mar 12, 2013 3:04 PM
                      1 2 Previous Next