使用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) 结束监控时间
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 1
- 2
- 3
- 4
- 5
- 6
- 7
begin~
- 创建测试用户vast
SQL> create user vast identified by oracle;
User created.
- 1
- 2
- 3
- 1
- 2
- 3
- 授权
SQL> grant resource,connect,dba to vast;
Grant succeeded.
- 1
- 2
- 3
- 1
- 2
- 3
- 连接
SQL> conn vast/oracle
Connected.
- 1
- 2
- 1
- 2
- 创建测试表test
SQL> create table test(a number,b number);
Table created.
- 1
- 2
- 3
- 1
- 2
- 3
- 创建索引
SQL> alter table test add constraint idx_test_pk primary key(a);
Table altered.
- 1
- 2
- 3
- 1
- 2
- 3
- 开启索引监控
SQL> alter index idx_test_pk monitoring usage;
Index altered.
- 1
- 2
- 3
- 1
- 2
- 3
- 确认开启
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
- 1
- 2
- 3
- 4
- 5
- 1
- 2
- 3
- 4
- 5
SQL> select * from test where a=1;
no rows selected
- 1
- 2
- 3
- 1
- 2
- 3
- 测试
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
- 1
- 2
- 3
- 4
- 5
- 1
- 2
- 3
- 4
- 5
- 关闭索引监控
SQL> alter index idx_test_pk nomonitoring usage;
Index altered.
- 1
- 2
- 3
- 1
- 2
- 3
- 确认关闭
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
- 1
- 2
- 3
- 4
- 5
- 1
- 2
- 3
- 4
- 5
end~