首页 » ORACLE 9i-23c » index monitoring usage 注意了！
index monitoring usage 注意了！
当系统中有很多索引或一个表上有很多索引，想找出平时不用的索引drop 掉，因为索引毕竟会给insert/update 等带来负担，于时ORACLE提供了monitoring index的功能， 但有时一些情况会被忽略，正面看我的实验
环境rhel 5, oracle 10201
SQL> create table testmon as select object_id,object_name from all_objects where object_id is not null;
Table created.
SQL> select count(*) from testmon;
COUNT(*)
----------
40694
SQL> create index idx_testmon_id on testmon(object_id);
Index created.
SQL> select * from v$object_usage;
no rows selected
SQL> alter index idx_testmon_id monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON YES NO 12/09/2011 15:19:25
--可以看到索引在监控中且没有使用，下面让它使用
SQL> set autot trace
SQL> select * from testmon where object_id<10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 350675818
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTMON | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TESTMON_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10)
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON YES YES 12/09/2011 15:19:25
The view V$OBJECT_USAGE canbe queried for the index being monitored to see if the index has been used. Theview contains a USED column whose value is YES or NO,depending upon if the index has been used within the time period beingmonitored. The view also contains the start and stop times of the monitoringperiod, and a MONITORING column (YES/NO) to indicate if usagemonitoring is currently active.
SQL> alter index idx_testmon_id nomonitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON NO YES 12/09/2011 15:19:25 12/09/2011 15:25:07
检查 视图的创建脚本
create or replace view v$object_usage
(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)
as
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
/
V$OBJECT_USAGE displaysstatistics about index usage gathered from the database for the indexes ownedby the current user. You can use this view to monitor index usage. All indexesthat have been used at least once can be monitored and displayed in this view.
Each time thatyou specify MONITORING USAGE, the V$OBJECT_USAGE view is resetfor the specified index. The previous usage information is cleared or reset,and a new start time is recorded. When you specifyNOMONITORING USAGE, nofurther monitoring is performed, and the end time is recorded for the monitoringperiod. Until the next ALTER INDEX...MONITORING USAGE statement isissued, the view information is left unchanged.
上面也提到每次设置只会使v$object_usage reset,而非清除，别外注意V$OBJECT_USAGE displaysstatistics about index usage gathered from the database for the indexes ownedby the current user.这句where io.owner# = userenv('SCHEMAID') ,提示视图中显示的只是当用schema里的。
清除方法
SQL> conn / as sysdba
Connected.
SQL> select * from object_usage;
OBJ# FLAGS START_MONITORING END_MONITORING
---------- ---------- ------------------------------ ------------------------------
52514 1 12/09/2011 15:19:25 12/09/2011 15:25:07
SQL> delete from object_usage where obj#=52514;
1 row deleted.
SQL> commit;
Commit complete.
SQL> conn anbob/anbob
Connected.
SQL> select * from v$object_usage;
no rows selected
看eygle的文章还发现在9205之前 'ALTER INDEX SYS.I_OBJAUTH1 MONITORING USAGE',存在一个bug 可以参考Metalink:Note:2934068.8 或 http://www.eygle.com/archives/2004/12/monitor_index_usage.html
再继续看有哪些操作会悄悄改变v$object_usage used 值
SQL> alter index idx_testmon_id monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON YES NO 12/09/2011 15:52:07
SQL> analyze table testmon compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON YES NO 12/09/2011 15:52:07
analyze table compute statistics = analyze table compute statistics for table for all indexes for all columns;
SQL> exec dbms_stats.gather_table_stats(user,'TESTMON');
PL/SQL procedure successfully completed.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON YES YES 12/09/2011 15:52:07
可以看到dbms_stats 修改了used的值，那样你以前监听索引是否使用就无意义了。而analyze 不会，不过analyze 又是不被推荐的，哎矛盾
查询得知这是一个bug,
Bug 6798910 - DBMS_STATS or EXPLAIN PLAN trigger index usage monitoring [ID 6798910.8]
Fixed:
This issue is fixed in
* 11.2.0.1 (Base Release)
* 10.2.0.5 (Server Patch Set)
* 10.2.0.4 Patch 5 on Windows Platforms
再看另一种情况
SQL> alter index idx_testmon_id nomonitoring usage;
Index altered.
SQL> alter index idx_testmon_id monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON YES NO 12/09/2011 16:13:32
SQL> alter index idx_testmon_id rebuid;
alter index idx_testmon_id rebuid
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
SQL> alter index idx_testmon_id rebuild;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON NO YES 12/09/2011 16:13:32
SQL> alter index idx_testmon_id nomonitoring usage;
Index altered.
SQL> alter index idx_testmon_id monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON YES NO 12/09/2011 16:15:05
SQL> alter index idx_testmon_id rebuild online;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED START_MONITORING END_MONITORING
-------------------- ------------------------------ --------- --------- ------------------------------ ------------------------------
IDX_TESTMON_ID TESTMON NO NO 12/09/2011 16:15:05
可见index rebuild 也会改变used 和monitoring的值
note:
所以即使监听的这个索引没有引用到，在删除的时候也要小心确认是不是外键所用
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。