首页 » ORACLE » 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)评论关闭。

  1. Epifania Haar | #1
    2011-12-21 at 06:06

    Thanks for the information provided! I was looking for this information for quite some time, but I wasn’t able to see a dependable source.