Monitoring Index Usage not reliable in 10.2.0.4

By | May 12th, 2009 | Category: 10g, Bugs, Oracle Database | 3 comments

I recently had to analyze a system in respect of index utilization. The application has dozens of indexes and we have to find out, which ones are really needed and which ones are candidates for removal.

There is a nice feature, called Index Monitoring which switches a flag if the index is used.

1

ALTER INDEX schema.index MONITORING USAGE;

Internally, a recursive SQL Statement is executed for every parsing of a SQL:

1

UPDATE object_usage SET flags=flags+1 WHERE obj#=:1 AND bitand(flags, 1)=0;

To check whether the index was used, you can query the view v$object_usage. Unfortunately, the view definition contains a where clause to list only indexes of the current schema.

1

   WHERE    io.owner# = userenv('SCHEMAID')

Therefore, you can create a new view or just omit the limitation from the where clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

 CREATE OR REPLACE VIEW V$ALL_OBJECT_USAGE 
         (OWNER,
         INDEX_NAME, 
         TABLE_NAME, 
         MONITORING, 
         USED, 
         START_MONITORING, 
         END_MONITORING) 
         AS 
           SELECT u.name, io.name, t.name, 
           decode(bitand(i.flags, 65536), 0, 'NO', 'YES'), 
           decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), 
           ou.start_monitoring, 
           ou.end_monitoring 
         FROM sys.user$ u, 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#
         AND u.user# = io.owner#

However, what is less known is that the DBMS_STATS can in some cases manipulate these flags. The issue is tracked with “Bug 6798910 – DBMS_STATS or EXPLAIN PLAN trigger index usage monitoring”. If you are using 10.2.0.4 and you are gathering stats manually with “CASCADE=>TRUE”, then the monitoring flag is set to “YES”. Obviously, in this case you can not identify candidate indexes for removal.

3 comments
Leave a comment »

  1. Christian Antognini May 15th, 2009 20:25 :

    Hi Martin

    Another case where MONITORING USAGE does a bad job is when an index is used to check a foreign key.
    So, all in all, MONITORING USAGE might help but we cannot simply take the information it provides without assessing what they really mean…

    Cheers,
    Chris

  2. admin May 15th, 2009 23:20 :

    Hi Chris,

    thank you for contribution. See you in Zurich.

    Martin

  3. Martin July 7th, 2009 14:28 :

    I have just found out that “index rebuilds” also interfere with index monitoring:

    SQL> select * from v$object_usage

INDEX_NAME TABLE_NAME  MON USE START_MONITORING    END_MONITORING
---------- ----------- --- --- ------------------- --------------
IDX1       IDXTEST     YES NO  06/07/2009 19:20:58
IDX2       IDXTEST     YES NO  06/07/2009 19:21:04


SQL> alter index IDX1 rebuild;

Index altered.

SQL> alter index IDX2 rebuild online;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME  TABLE_NAME  MON USE START_MONITORING    END_MONITORING
----------- ----------- --- --- ------------------- --------------
IDX1        IDXTEST     NO  YES 06/07/2009 19:20:58
IDX2        IDXTEST     NO  NO  06/07/2009 19:21:04

    So, the commands “ALTER INDEX … REBUILD” and “ALTER INDEX … REBUILD ONLINE” both set the Mon(itor) flag to NO. Moreover the “ALTER INDEX .. REBUILD” also sets the USE Flag to YES.

    I have found this information in Norbert Debes book “Secrets of the Oracle Database”.

Leave Comment