使用v$object_usage监控索引

V$OBJECT_USAGE

V$OBJECT_USAGE displays statistics about index usage gathered from the database for the indexes owned by the current user. You can use this view to monitor index usage. All indexes that have been used at least once can be monitored and displayed in this view.

V$OBJECT_USAGE显示当前用户索引使用的统计信息。你可以使用此视图监视索引使用情况。该视图能够监控和显示所有索引至少一次使用。

Column              Datatype        Description
INDEX_NAME          VARCHAR2(30)    索引名
TABLE_NAME          VARCHAR2(30)    表名
MONITORING          VARCHAR2(3)     是否监控
USED                VARCHAR2(3)     是否使用
START_MONITORING    VARCHAR2(19)    开始监控时间 
END_MONITORING      VARCHAR2(19)    结束监控时间

begin~

• 创建测试用户vast

SQL> create user vast identified by oracle;

User created.

• 授权

SQL> grant resource,connect,dba to vast;

Grant succeeded.

• 连接

SQL> conn vast/oracle
Connected.

• 创建测试表test

SQL> create table test(a number,b number);

Table created.

• 创建索引

SQL> alter table test add constraint idx_test_pk primary key(a);               

Table altered.

• 开启索引监控

SQL> alter index idx_test_pk monitoring usage;

Index altered.

• 确认开启

SQL> select * from v$object_usage;

INDEX_NAME    TABLE_NAME      MONITO  USED   START_MONITORING      END_MONITORING
------------ ------------- ---------  -----  -------------------   ------------------ 
IDX_TEST_PK   TEST             YES    NO     05/26/2017 23:55:38

SQL> select * from test where a=1;

no rows selected

• 测试

SQL> select * from v$object_usage;

INDEX_NAME    TABLE_NAME   MONITO   USED     START_MONITORING              END_MONITORING 
------------- ----------  -------- -------  ----------------------    -----------------------
IDX_TEST_PK   TEST         YES      YES      05/26/2017 23:55:38

• 关闭索引监控

SQL> alter index idx_test_pk nomonitoring usage;

Index altered.

• 确认关闭

SQL> select * from v$object_usage;

INDEX_NAME   TABLE_NAME   MONITO   USED     START_MONITORING                   END_MONITORING
---------- -------------- -------- ------- -------------------------- --------------------------
IDX_TEST_PK   TEST        NO       YES      05/26/2017 23:55:38             05/26/2017 23:57:14

end~