--查看表上的索引SQL> Selectindex_name,table_name,num_rows From dba_indexes i Where i.table_name ='
WEBSITE_VIEW_TB'
;
INDEX_NAME TABLE_NAME NUM_ROWS------------------------------------------------------------ ----------CURRTIME_IDX WEBSITE_VIEW_TB 79284331ORDERNO_IDX WEBSITE_VIEW_TB 2021984ORDERSOURCE_IDX WEBSITE_VIEW_TB 938174--开启索引监控SQL> ALTER INDEX CURRTIME_IDX MONITORING USAGE;
Index altered--查看索引监控SQL> Select * From v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING------------------------------------------------------------ ------------------- --------- --------------------------------------CURRTIME_IDX WEBSITE_VIEW_TB YES NO 08/20/2013 14:19:48
注意: 如果开启了索引监控功能,用v$object_usage视图可以查看正在被监控的索引记录,USED列表示在开启索引监控过程中索引是否被使用,MONITORING列表示是否开启了索引监控,START_MONITORING表示开启索引监控的开始时间,END_MONITORING表示开启索引监控的结束时间
--执行查询语句,使用被监控的索引SQL> Select Count(*) From log.WEBSITE_VIEW_TB t 2 Where t.currtime between to_date('
2013-1-27'
,'
yyyy-mm-dd'
)And to_date('
2013-1-28'
,'
yyyy-mm-dd'
);
COUNT(*)---------- 750603--可以看到记录的USED列值变成了YES,表示索引被使用过。SQL> Select * From v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING---------------------------- ----------------------------- --------------- -------- ------------------------------ --------CURRTIME_IDX WEBSITE_VIEW_TB YES YES 08/20/2013 14:19:48--分析完毕后,关闭索引监控,因为监控也会占用一定的资源SQL> ALTER INDEX CURRTIME_IDX NOMONITORING USAGE;
Index altered--可以看到MONITORING列变为NO,END_MONITORING列被填充,索引停止监控SQL> Select * From v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING----------------------------------------------------------------- ------------------- -------- ------------------------------- ---------------------------CURRTIME_IDX WEBSITE_VIEW_TB NO YES 08/20/2013 14:19:48 08/20/2013 14:32:18--再次执行查询,监控记录无变化SQL> Select Count(*) From log.WEBSITE_VIEW_TB t 2 Where t.currtime between to_date('
2013-1-27'
,'
yyyy-mm-dd'
) And to_date('
2013-1-28'
,'
yyyy-mm-dd'
);
COUNT(*)---------- 750603SQL> Select * From v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING----------------------------------------------------------------- ------------------- -------- ------------------------------- ---------------------------CURRTIME_IDX WEBSITE_VIEW_TB NO YES 08/20/2013 14:19:48 08/20/2013 14:32:18--再次开启索引监控,相应监控记录值又发生了变化SQL> ALTER INDEX CURRTIME_IDX MONITORING USAGE;
Index alteredSQL> Select * From v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING------------------------------------------------------------ ---------- ---- --------------------------------------CURRTIME_IDX WEBSITE_VIEW_TB YES NO 08/20/2013 14:34:15
总结: 虽然v$object_usage表能记录索引监控和使用的状态,但它不能统计索引被使用的次数和频率,只记录了在开启索引监控的时间段索引是否被使用过,这一点要值的注意。