Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as dev_test
--.1 V$OBJECT_USAGE中有记录，且MONITORING列为yes表示对这个索引开启了监控。
used列为yes标识开启索引监控后索引被使用过
|
SQL> SELECT INDEX_NAME, MONITORING, U.START_MONITORING, USED, U.END_MONITORING 2 FROM V$OBJECT_USAGE U; INDEX_NAME MONITORING START_MONITORING USED END_MONITORING ---------------------- ----------------- ------------------------------ ----- -------------------------- IDX_COMCODE NO 06/07/2011 10:33:13 NO 06/07/2011 10:36:22 IDX_COMCODE1 NO 05/13/2011 14:30:56 YES 06/07/2011 10:18:38 IDX_COMCODE2 NO 05/13/2011 14:30:57 NO 06/07/2011 10:18:41
|
注意：当重复对索引开启监控时V$OBJECT_USAGE只保留最新的监控记录。
-- 2.开启索引监控
|
ALTER INDEX &index_name MONITORING USAGE; |
--3. 查看当前用户被监控索引使用情况
|
SELECT * FROM v$object_usage |
--4.关闭索引监控
|
ALTER INDEX &indEx_name NOMONITORING USAGE; |
--5.查看所有用户下被监控索引的使用情况：
|
SQL> SELECT U.NAME OWNER, 2 IO.NAME INDEX_NAME, 3 T.NAME TABLE_NAME, 4 DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING, 5 DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED, 6 OU.START_MONITORING START_MONITORING, 7 OU.END_MONITORING END_MONITORING 8 FROM SYS.USER$ U, 9 SYS.OBJ$ IO, 10 SYS.OBJ$ T, 11 SYS.IND$ I, 12 SYS.OBJECT_USAGE OU 13 WHERE I.OBJ# = OU.OBJ# 14 AND IO.OBJ# = OU.OBJ# 15 AND T.OBJ# = I.BO# 16 AND U.USER# = IO.OWNER#; |