How to debug the issue of index monitoring wherein index entry is not found in table v$object_usage?

I am facing an issue while enabling monitoring on indexes , I executed following command to enable index monitoring and then checked the entry in v$object_usage view but couldn’t find any record in it :

alter index REPORT.DY_SUM_DLY_SCH_TRN_DIV monitoring usage;

O/p is Index REPORT.DY_SUM_DLY_SCH_TRN_DIV altered.

Checking the entry in v$object_usage :

SELECT index_name,table_name,monitoring,used,start_monitoring,end_monitoring
FROM  v$object_usage

In output no records are coming .

How to debug this issue ?

Answer

If you are not seeing records in the v$object_usage is either for one of the two following reasons:

• The index is not used by any statement
• You are not querying the view with the owner of the index.

Normally, it is the second cause the reason.

The V$OBJECT_USAGE view does not contain an OWNER column so you must to log on as the object owner to see the usage data. From Oracle 12.1 onward the V$OBJECT_USAGE view has been deprecated in favour of the {DBA|USER]}_OBJECT_USAGE views. The structure is the same, but the DBA_OBJECT_USAGE view includes an OWNER column.

Logged as SYS

SQL> drop table t1 ;

Table dropped.

SQL> create table t1 ( c1 number , c2 varchar2(20) ) ;

Table created.

SQL> create index idx_t1 on t1 ( c1 ) ;

Index created.

SQL> declare
  2  begin
  3  for h in 1 .. 10000
  4  loop
  5    insert into t1 values ( h , round(dbms_random.value(1,100000)) ) ;
  6  end loop;
  7  commit;
  8* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 ;

  COUNT(*)
----------
     10000

SQL> analyze table t1 compute statistics ;

Table analyzed.

SQL> analyze index idx_t1 compute statistics ;

Index analyzed.

SQL> set autotrace traceonly explain
SQL> set lines 220 pages 600
SQL> select * from t1 where c1 = 1009 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3491035275

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_T1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=1009)

SQL> alter index idx_t1 monitoring usage ;

Index altered.

SQL> set autotrace off
SQL> select * from t1 where c1 = 1009 ;

        C1 C2
---------- --------------------
      1009 21639

SQL> select index_name from v$object_usage  where index_name = 'IDX_T1' ;

no rows selected

Logged as Owner

SQL> conn test1/Oracle_12
Connected.
SQL> col  index_name for a30
SQL> col table_name for a30
SQL>
SQL> select index_name,table_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage  where index_name = 'IDX_T1' ;  2

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_T1                         T1                             YES YES 09/22/2021 08:06:28

SQL>