Oracle Index Monitoring

Today someone asked how to Monitor Index usage. It’s simple to turn on monitoring on index. Oracle support told there is no easy way to do it and in some term that is right too.

But the funny thing is, object usage report might be wrong while you rebuild or gather statistics on index and some other things. Well, Sometimes monitoring is not the right way to identify index usage. Mostly, this whole exercise is done for some space gain or for performance. For example in warehouse for each load they disable index and then rebuild after load and at this point of time performance degradation takes place while rebuilding indexes. This is not the part of discussion but we will include this also little bit today.

Here are some examples of this.

Let’s start with how v$object_usage columns are.

  • Create two Different Users test1 & test2
  • TEST1@taral > @desc sys.v$object_usage;
    
    Col# SEG_COL_ID Column Name                    Null?      Type
    ---- ---------- ------------------------------ ---------- -------------------------
       1          1 INDEX_NAME                     NOT NULL   VARCHAR2(30)
       2          2 TABLE_NAME                     NOT NULL   VARCHAR2(30)
       3          3 MONITORING                                VARCHAR2(3)
       4          4 USED                                      VARCHAR2(3)
       5          5 START_MONITORING                          VARCHAR2(19)
       6          6 END_MONITORING                            VARCHAR2(19)
    
    6 rows selected.
    
    TEST1@taral > @view sys.v$object_usage;
    
    Owner      VIEW_NAME                      View Text
    ---------- ------------------------------ ----------------------------------------------------------------
    SYS        V$OBJECT_USAGE                 select 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.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
                                              where io.owner# = userenv('SCHEMAID')
                                              and i.obj# = ou.obj#
                                              and io.obj# = ou.obj#
                                              and t.obj# = i.bo#
    
    
    First Test Case With Two Different Users
    =========================================
    drop table test1.xx purge;
    create table test1.xx as select object_id,object_name from dba_objects;
    create index test1.xx_idx on test1.xx(object_id);
    select * from v$object_usage;
    
    TEST1@taral > select * from v$object_usage;
    
    no rows selected
    
    TEST1@taral > alter index test1.xx_idx MONITORING USAGE;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 10:57:15
    
    TEST1@taral > select * from test1.xx; --Lines Not Displayed
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 10:57:15
    
    select * from test1.xx where object_id=41;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES YES 10/20/2010 10:57:15
    
    But if i tried from different User (Because o.owner# = userenv('SCHEMAID'))
    
    TEST2@taral > select * from v$object_usage;
    
    no rows selected
    
    
    Second Test Case With Explain Plan
    =========================================
    set autotrace off;
    drop table test1.xx purge;
    create table test1.xx as select object_id,object_name from dba_objects;
    create index test1.xx_idx on test1.xx(object_id);
    select * from v$object_usage;
    alter index test1.xx_idx MONITORING USAGE;
    select * from v$object_usage;
    SET AUTOTRACE TRACEONLY EXPLAIN;
    select * from test1.xx where object_id=41;
    set autotrace off;
    select * from v$object_usage;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 15:32:32
    
    TEST1@taral > SET AUTOTRACE TRACEONLY EXPLAIN;
    TEST1@taral > select * from test1.xx where object_id=41;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2275028263
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| XX     |     1 |    79 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | XX_IDX |     1 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    TEST1@taral > set autotrace off;
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES YES 10/20/2010 15:32:32
    
    
    Third Test Case With Index Rebuild
    -----------------------------------
    set autotrace off;
    drop table test1.xx purge;
    create table test1.xx as select object_id,object_name from dba_objects;
    create index test1.xx_idx on test1.xx(object_id);
    select * from v$object_usage;
    alter index test1.xx_idx MONITORING USAGE;
    select * from v$object_usage;
    alter index test1.xx_idx rebuild;
    select * from v$object_usage;
    ALTER INDEX test1.xx_idx NOMONITORING USAGE;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 15:38:58
    
    TEST1@taral > alter index test1.xx_idx rebuild;
    
    Index altered.
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             NO  YES 10/20/2010 15:38:58
    
    TEST1@taral > alter index test1.xx_idx MONITORING USAGE;
    
    Index altered.
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 15:39:50
    
    So, this query will solve this issue
    
    SELECT * FROM v$object_usage WHERE (monitoring='YES' AND used='YES') OR (used='YES' AND end_monitoring IS NOT NULL) ;
    
    

    As, in starting i said we will see some examples of how this can’t be useful.

    Let’s say there are 5-6 indexes on one table and i had turned on monitoring for all this indexes. Some query only hit this index once and that change the status of index. So, my report from v$object_usage will say i have to keep this index even though it has been used only once. So, this won’t say how many times this index has been really used. Also, there is other danger involved with this is you might end up deleting some index which you might need as monitoring may not give you whole picture.

     

     

    
    set autotrace off;
    drop table test1.xxf purge;
    drop table test1.xx purge;
    create table test1.xx (object_id number constraint xx_pk primary key, object_name varchar2(128)) ;
    insert into test1.xx select rownum,object_name from dba_objects where rownum < 2001; commit; select * from v$object_usage; alter index test1.xx_pk MONITORING USAGE; set autotrace on; select * from test1.xx where object_id=41; select * from test1.xx where object_id=100; set autotrace off; select * from v$object_usage; TEST1@taral > set autotrace off;
    TEST1@taral > drop table test1.xxf purge;
    
    Table dropped.
    
    TEST1@taral > drop table test1.xx purge;
    
    Table dropped.
    
    TEST1@taral > create table test1.xx (object_id number constraint xx_pk primary key, object_name varchar2(128)) ;
    
    Table created.
    
    TEST1@taral > insert into test1.xx select rownum,object_name from dba_objects where rownum < 2001; 2000 rows created. TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral > select * from v$object_usage;
    
    no rows selected
    
    TEST1@taral > alter index test1.xx_pk MONITORING USAGE;
    
    Index altered.
    
    TEST1@taral > set autotrace on;
    TEST1@taral > select * from test1.xx where object_id=41;
    
     OBJECT_ID OBJECT_NAME
    ---------- --------------------------------------------------------------------------------------------------------------------------------
            41 FET$
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 266797879
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |     1 |    79 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| XX    |     1 |    79 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | XX_PK |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    TEST1@taral > select * from test1.xx where object_id=100;
    
     OBJECT_ID OBJECT_NAME
    ---------- --------------------------------------------------------------------------------------------------------------------------------
           100 FIXED_OBJ$
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 266797879
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |     1 |    79 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| XX    |     1 |    79 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | XX_PK |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    TEST1@taral > set autotrace off;
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_PK                          XX                             YES YES 10/20/2010 16:50:20
    

    So, this is not telling you how many times this index has been used.

    
    create table test1.xxf (object_id number, object_type varchar2(128) , constraint xxf_fk foreign key(object_id ) references xx(object_id));
    insert into test1.xxf select decode(mod(rownum,1000),0,1,mod(rownum,1000)) object_id, stage.object_type from dba_objects stage;
    commit;
    create index test1.xxf_fk on test1.xxf(object_id);
    exec dbms_stats.gather_table_stats(user,'XX');
    exec dbms_stats.gather_table_stats(user,'XXF');
    alter index test1.xxf_fk monitoring usage;
    select * from v$object_usage;
    set autotrace on;
    delete test1.xx where object_id = 2000;
    commit;
    set autotrace off;
    select * from v$object_usage;
    
    TEST1@taral > create table test1.xxf (object_id number, object_type varchar2(128) , constraint xxf_fk foreign key(object_id ) references xx(object_id))
    
    Table created.
    
    TEST1@taral > insert into test1.xxf select decode(mod(rownum,1000),0,1,mod(rownum,1000)) object_id, stage.object_type from dba_objects stage;
    
    72302 rows created.
    
    TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral > create index test1.xxf_fk on test1.xxf(object_id);
    
    Index created.
    
    TEST1@taral > exec dbms_stats.gather_table_stats(user,'XX');
    
    PL/SQL procedure successfully completed.
    
    TEST1@taral > exec dbms_stats.gather_table_stats(user,'XXF');
    
    PL/SQL procedure successfully completed.
    
    TEST1@taral > alter index test1.xxf_fk monitoring usage;
    
    Index altered.
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XXF_FK                         XXF                            YES NO  10/20/2010 16:50:23
    XX_PK                          XX                             YES YES 10/20/2010 16:50:20
    
    TEST1@taral > set autotrace on;
    TEST1@taral > delete test1.xx where object_id = 2000;
    
    1 row deleted.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 203824999
    
    ----------------------------------------------------------------------------
    | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | DELETE STATEMENT   |       |     1 |     4 |     1   (0)| 00:00:01 |
    |   1 |  DELETE            | XX    |       |       |            |          |
    |*  2 |   INDEX UNIQUE SCAN| XX_PK |     1 |     4 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("OBJECT_ID"=2000)
    
    Statistics
    ----------------------------------------------------------
            178  recursive calls
              7  db block gets
             35  consistent gets
              0  physical reads
            116  redo size
            679  bytes sent via SQL*Net to client
            611  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              6  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral > set autotrace off;
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XXF_FK                         XXF                            YES NO  10/20/2010 16:50:23
    XX_PK                          XX                             YES YES 10/20/2010 16:50:20
    
    

    If you see now foreign key index is not been used from v$object_usage. So, it’s safe to delete it.

    
    TEST1@taral > drop index test1.xxf_fk;
    
    Index dropped.
    
    TEST1@taral > set autotrace on;
    TEST1@taral > delete test1.xx where object_id = 1999;
    
    1 row deleted.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 203824999
    
    ----------------------------------------------------------------------------
    | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | DELETE STATEMENT   |       |     1 |     4 |     1   (0)| 00:00:01 |
    |   1 |  DELETE            | XX    |       |       |            |          |
    |*  2 |   INDEX UNIQUE SCAN| XX_PK |     1 |     4 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("OBJECT_ID"=1999)
    
    Statistics
    ----------------------------------------------------------
            190  recursive calls
              7  db block gets
            217  consistent gets
              0  physical reads
            732  redo size
            679  bytes sent via SQL*Net to client
            611  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              6  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral >
    

    See how much now more work we have to do after removing index which was useful. So, don’t rely on v$object_usage. It’s not a metric.



    Reference: 

  • Secrets of the Oracle Database – Norbert Debes
  • Tim Hall

  • Advertisements

    About Taral
    I am Rookie to Oracle Technology so let's see where it goes

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: