1 2 Previous Next 17 Replies Latest reply on Sep 10, 2015 12:16 PM by Jonathan Lewis

    how to get usage rate of indexes

    2690604

      Hi everyone

       

      In our production system, there are numbers of tables containing dozens of indexes on them. I want to find the usage rate of indexes, if some indexes is never used or seldom used, I will get to drop these indexes.

       

      I used to rely on DBA_HIST_SQL_PLAN to find the usage rate of indexes for drop index purpose, but I found DBA_HIST_SQL_PLAN just contains the sqls in AWR report, for the sqls (which is running fast with good indexes) may not be included in the AWR and DBA_HIST_SQL_PLAN. So querying DBA_HIST_SQL_PLAN is not an accurate way to analyse all the sqls and all the indexes also well.

       

      Could some guys give me a good way to find usage rate for all indexes? Thanks!

       

       

      my query:

      WITH Q AS

      (SELECT S.OWNER A_OWNER,

      TABLE_NAME A_TABLE_NAME,

      INDEX_NAME A_INDEX_NAME,

      INDEX_TYPE A_INDEX_TYPE,

      SUM(S.bytes) / 1048576 A_MB

      FROM DBA_SEGMENTS S, DBA_INDEXES I

      WHERE S.OWNER = UPPER('&INPUT_OWNER')

      AND I.OWNER = UPPER('&INPUT_OWNER')

      AND INDEX_NAME = SEGMENT_NAME

      GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE)

      SELECT A_OWNER OWNER,

      A_TABLE_NAME TABLE_NAME,

      A_INDEX_NAME INDEX_NAME,

      A_INDEX_TYPE INDEX_TYPE,

      A_MB MB,

      DECODE(OPTIONS, null, '       -', OPTIONS) INDEX_OPERATION,

      COUNT(OPERATION) NR_EXEC,

      (WITH TMP AS (SELECT TABLE_NAME,

      INDEX_NAME,

      TO_CHAR(WM_CONCAT(COLUMN_POSITION)

      OVER(PARTITION BY INDEX_NAME ORDER BY

      COLUMN_POSITION)) COLUMN_POSITION,

      TO_CHAR(WM_CONCAT(COLUMN_NAME)

      OVER(PARTITION BY INDEX_NAME ORDER BY

      COLUMN_POSITION)) COLUMN_NAME

      FROM USER_IND_COLUMNS)

      SELECT MAX(COLUMN_NAME) AS COLUMN_NAME

      FROM TMP

      GROUP BY INDEX_NAME

      HAVING MAX(TABLE_NAME) = UPPER('&INPUT_TBNAME')

      AND A_INDEX_NAME = INDEX_NAME) COLUMN_NAME,

      'DROP INDEX ' || A_INDEX_NAME || ';' SQL_DROP

      FROM Q, DBA_HIST_SQL_PLAN d

      WHERE D.OBJECT_OWNER(+) = q.A_OWNER

      AND D.OBJECT_NAME(+) = q.A_INDEX_NAME

      AND Q.A_TABLE_NAME = UPPER('&INPUT_TBNAME')

      GROUP BY A_OWNER,

      A_TABLE_NAME,

      A_INDEX_NAME,

      A_INDEX_TYPE,

      A_MB,

      DECODE(OPTIONS, null, '       -', OPTIONS)

      ORDER BY NR_EXEC

       

      Regards

      Li

        • 1. Re: how to get usage rate of indexes
          Arun#

          Hi,

           

          If your database is 10g or above, you could use Index Monitoring. https://oracle-base.com/articles/10g/index-monitoring

          • 2. Re: how to get usage rate of indexes
            BeGin

            Hi,

             

            You can use the index monitoring.

             

            You start it index by index by using

             

            alter index index_name monitoring usage

            then the usage of the index is stored in v$object_usage.

             

            Anyway be careful with these processes, you have to let it run during a long period depending on your applications, if you have some yearly processes you should let it run one year to be sure you have  all the indexes used.

             

            Complete process there : https://oracle-base.com/articles/10g/index-monitoring

             

            Regards,

             

            --

            Bertrand

            • 3. Re: how to get usage rate of indexes
              Pini Dibask

              Hello,

               

              Although Index Monitoring feature could be a good option, bear in mind that it only tells you whether the index was being used or not (since the time you enabled the monitoring), it doesn't tell you the rate of the index usage, which SQL's used it, how did they use it, etc.

               

              I do agree that DBA_HIST_SQL_PLAN only contains the SQL's which entered into the AWR Repository, so it's not perfect but you can also use V$SQL_PLAN in addition to the DBA_HIST_SQL_PLAN view. V$SQL_PLAN will also show you execution plans for child cursor that are still in the library cache. It may be helpful because in some cases will see execution plans that don't appear in the DBA_HIST_SQL_PLAN  because AWR snapshot didn't capture it. Obviusly, this is also limited because some execution plans may be aged out from the library cache.


              You can use the following SQL for example:

              select a.sql_id,sql_text,executions,loads, operation, options, projection, last_active_time

              from

              v$sqlarea a,v$sql_plan b

              where a.sql_id=b.sql_id and a.plan_hash_value = b.plan_hash_value

              and object_name='INDEX_NAME';

              • 4. Re: how to get usage rate of indexes
                2690604

                Thanks for your reply.

                But we don't come to get a good plan for the index usage.

                • 5. Re: how to get usage rate of indexes

                  2690604 wrote:

                   

                  Thanks for your reply.

                  But we don't come to get a good plan for the index usage.

                   

                  please quantify "good".

                  Either an INDEX is used or  it is not used; which is exactly what Index Monitoring measures.

                  • 6. Re: how to get usage rate of indexes
                    Richard Foote-Oracle

                    A little something I always get people to consider is that it's really easy to just create another index, but a lot lot harder to subsequently drop them ...

                     

                    A useful method of getting a good indication on just how frequently an index is used is to look at the segment statistics of your indexes (e.g. v$segment_statistics so long as statistics_level is not basic). Indexes with very large logical reads in comparison to db block changes are getting accessed for reasons other than simple index maintenance.

                     

                    Of course an infrequently used index might be critical for infrequent business reasons. SQL using frequently used indexes could potentially use another existing index in an adequately efficient manner.

                     

                    Cheers

                     

                    Richard Foote

                    https://richardfoote.wordpress.com/

                    • 7. Re: how to get usage rate of indexes
                      2690604

                      Thanks Richard.

                      I've looked for sqls with v$segment_statistics for index usage, someone said "physical reads" can indicate whether the index is used or not, but this is not useful to me. "physical reads“ can also change when the corresponding table has INSERT(or TRUNCATE...) operation without any SELECT operation on the table.I cannot rely on  "physical reads".

                      I also check the indexed with large logical reads as you indicated, some frequently used indexes can have large logical reads. I cannot rely on  "logical reads".

                       

                       

                      Regards

                      Li

                      • 8. Re: how to get usage rate of indexes
                        Jonathan Lewis

                        Looking at physical reads on index segments is fairly pointless as far as deciding whether or not the index has been used in an execution plan.

                        Richard's point was that

                        a) If an index is frequently used it will probably be subject to a large number of LOGICAL reads

                        b) If an index is only accessed for modification then then its value for "db block changes" will be a large fraction of its logical reads

                         

                        Hence

                        - an index with a large value for logical reads and a relatively small value for db block chagnes is probably frequently used for query.

                        - and index where db block changes is similar (say larger than 50% of) logical reads is probably not used

                         

                        It's an observation that points you at some suspect indexes, but

                        1) you may have some indexes that should be used but aren't because there are other indexes that shouldn't be used but are being used in error.

                        2) it is possible for Oracle to use the statistics (specifically num_distinct) from an index to help pick an execution plan without actually using the index in the plan.

                         

                         

                        On top of Richard's suggestion another INDICATOR or redundancy is to find indexes where one index is the leading set of columns of another index IN THE SAME ORDER. The shorter index ought to be redundant - though the optimizer might not want to use the larger index if you drop the smaller one.

                         

                         

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: how to get usage rate of indexes
                          jgarry

                          There's also a slightly philosophical problem:  How do you know there aren't indices that should be used that aren't being used?  It's the converse of what Richard noted about index accumulation; maybe some were added that should have been, and yet others still should be.  It's a scoping problem - tuning queries or sets of queries for particular objects, versus looking at the usage of all indices.

                           

                          It's hard if the problems are too subtle for users to complain about and the available aggregate information doesn't spotlight any problem.  You can't prove negative index usage, yet you know excess work can happen if all data access isn't optimized.

                          • 10. Re: how to get usage rate of indexes
                            2690604

                            Thanks for your explanation Jonathan.

                            In our production DB there are some hot big tables having over 100 columns and over 20 indexes for each of them. These tables and indexes were disgined years ago, with times goes by our bussiness has changed, maybe some indexes are redundant and never used, some index are soldem used. We want to know whether there are 2 types indexes(never and seldom used) since last DB startup. If there are, we will drop these redundant indexes for more efficient DML on the big tables.That is where I come from.

                            -----------------------------

                            Hence

                            - an index with a large value for logical reads and a relatively small value for db block chagnes is probably frequently used for query.

                            - and index where db block changes is similar (say larger than 50% of) logical reads is probably not used

                             

                            I trying a qurery on the big table as your suggestion as below:

                            select table_name  "table_name",

                            object_name "index_name",

                            (          WITH TMP AS (SELECT TABLE_NAME,

                            INDEX_NAME,

                            TO_CHAR(WM_CONCAT(COLUMN_POSITION)

                            OVER(PARTITION BY INDEX_NAME

                            ORDER BY COLUMN_POSITION)) COLUMN_POSITION,

                            TO_CHAR(WM_CONCAT(COLUMN_NAME)

                            OVER(PARTITION BY INDEX_NAME

                            ORDER BY COLUMN_POSITION)) COLUMN_NAME

                            FROM USER_IND_COLUMNS)

                            SELECT MAX(COLUMN_NAME) AS column_name

                            FROM TMP

                            GROUP BY INDEX_NAME

                            HAVING MAX(TABLE_NAME) = UPPER('KH_KHDD') AND object_name = INDEX_NAME) "column_name" , sum(DECODE(STATISTIC_NAME, 'db block changes', value, null)) "db block changes", sum(DECODE(STATISTIC_NAME, 'logical reads', value, null)) "logical reads", round(sum(DECODE(STATISTIC_NAME, 'db block changes', value, null)) / sum(DECODE(STATISTIC_NAME, 'logical reads', value, null)) * 100, 2) "d/l(%)"

                            from v$segment_statistics s, user_indexes i

                            where 1 = 1

                            and s.object_name = i.index_name

                            and s.owner = 'VEASMS'

                            AND s.STATISTIC_NAME IN ('db block changes', 'logical reads')

                            AND i.table_name = 'KH_KHDD'

                            group by table_name, object_name

                            order by "d/l(%)" desc

                             

                            RTX截图未命名.png

                             

                            From the outcome of snapshot, can we say that all the indexes on table KH_KHDD are used?

                             

                             

                            Regards

                            Li

                            • 11. Re: how to get usage rate of indexes

                              From my perspective, if INDEX is read then it is being used & you eliminate it at your own peril.

                              IMO, I think you are on a snipe hunt.

                               

                              https://en.wikipedia.org/wiki/Snipe_hunt

                               

                              Why don't you look at a statspack report & spend you efforts on resource intensive SQL reported by it?

                              • 12. Re: how to get usage rate of indexes
                                Jonathan Lewis

                                With those figures I'd be curious about all the indexes you've reported at > 30%.

                                On the other hand I'd also be suspicious that there was something funny going on with the DDBH index - perhaps it's being used in cases where it shouldn't be, perhaps it's suffered a structural disaster.

                                I'd also note that you have two indexes starting with the same 4 columns and perhaps you could produce a single index that was as effective as the pair.

                                Then you have three indexes starting with dp_date_time_date, and perhaps with some column re-arrangement one would be sufficient.

                                I'd also be looking at opportunities for index compression

                                 

                                These things take a lot of time - start with the largest tablees which show the most time spent PER ROW for inserts updates and deletes as the first tables to investigate. Use the "physical reads" as a secondary indicator because time is OFTEN (but not always) due to physical I/O

                                 

                                 

                                Regards

                                Jonathan Lewis

                                • 13. Re: how to get usage rate of indexes
                                  2690604

                                  Thanks Jonathan.

                                  1)indexes you've reported at > 30%

                                  I also use the first query at the begining of this thread, find indexes >30% also have never been written into DBA_HIST_SQL_PLAN. This will make me more confident to drop these indexes.

                                  2)On the other hand I'd also be suspicious that there was something funny going on with the DDBH index

                                  DDBH index(SYS_C007990) is primary index.

                                  3)I'd also note that you have two indexes starting with the same 4 columns and perhaps you could produce a single index that was as effective as the pair.  

                                  We will combine indexes IDX_KH_KHDD_DCCP and IDX_KH_KHDD_DCCD into 1.

                                  4)Then you have three indexes starting with dp_date_time_date, and perhaps with some column re-arrangement one would be sufficient. 

                                  Seemingly index IDX_DP_DATETIME_NEW2 can be drop.

                                   

                                  Regards

                                  Li

                                  • 14. Re: how to get usage rate of indexes
                                    jgarry

                                    Are these tables being used in an OLTP manner, or are they some kind of aggregated reporting tables loaded in batches?

                                    1 2 Previous Next