索引可以加快查詢的速度，但索引會占用許多存儲空間，在插入和刪除行的時候，索引還會引入額外的開銷，因此確保索引得到有效利用是我們很關(guān)注的一個問題。 在Oracle9i之前，要知道一個索引是否被使用是困難的，而Oracle 9i中提供了一個有效的監(jiān)控方法:ALTER INDEX MONITORING USAGE。下面我講具體說明如何使用該方法來鑒別未使用的索引。
一、我們先通過一個例子具體說明“ALTER INDEX MONITORING USAGE”的使用方法：
1、建測試表
create table test(id number(3),name varchar2(10));
insert into test values(1,'aaaaaaaa');
insert into test values(2,'www.ncn.cn');
insert into test values(3,'aadfaaaa');
insert into test values(4,'gototop');
insert into test values(5,'shenzhen');
insert into test values(6,'china');
commit;
alter table test add (constraint test_pk PRimary key (id));
2、查詢v$object_usage(因為沒有監(jiān)視，所以還看不到內(nèi)容)
column index_name format a12
column monitoring format a10
column used format a4
column start_monitoring format a19
column end_monitoring format a19
select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
SQL> l
1* select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage
SQL> /
no rows selected
Elapsed: 00:00:00.00
3、開始監(jiān)控索引的使用情況
SQL> alter index test_pk monitoring usage;
Index altered.
Elapsed: 00:00:00.05
4、查詢v$object_usage(可以看到正監(jiān)視中)
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
TEST_PK YES NO 05/15/2003 13:28:22
Elapsed: 00:00:00.00
5、使用索引進行查詢
SQL> set autotrace on eXPlain
SQL> select * from test where id = 2;
ID NAME
---------- ----------
2 www.ncn.cn
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE access (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)
SQL> set autotrace off
SQL> /
ID NAME
---------- ----------
2 www.ncn.cn
Elapsed: 00:00:00.00
SQL>
從上我們可以看到確實使用了索引。
6、查詢v$object_usage(可以看到索引被使用過，但目前還處于被監(jiān)視過程中)
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
TEST_PK YES YES 05/15/2003 13:28:22
Elapsed: 00:00:00.00
7、停止監(jiān)視，并查詢v$object_usage
SQL> alter index test_pk nomonitoring usage;
Index altered.
Elapsed: 00:00:05.03
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
TEST_PK NO YES 05/15/2003 13:28:22 05/15/2003 13:40:00
Elapsed: 00:00:00.64
到此為止，監(jiān)視結(jié)束，MONITORING為NO，END_MONITORING給出了時間戳。
二、v$object_usage視圖解釋
從上面的例子中我們可以看出，索引的監(jiān)視信息都是存在在v$objec_usage視圖中，該視圖的定義如下：
CREATE OR REPLACE VIEW SYS.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#
/
COMMENT ON TABLE SYS.V$OBJECT_USAGE IS
'Record of index usage'
/
GRANT SELECT ON SYS.V$OBJECT_USAGE TO "PUBLIC"
/
下面是該視圖列的描述：
INDEX_NAME: sys.obj$.name 中的索引名字
TABLE_NAME: sys.obj$obj$name 中的表名
MONITORING: YES (索引正在被監(jiān)控), NO (索引沒有被監(jiān)控)
USED: YES (索引已經(jīng)被使用過), NO (索引沒有被使用過)
START_MONITORING: 開始監(jiān)控的時間
END_MONITORING: 結(jié)束監(jiān)控的時間
所有被使用過至少一次的索引都可以被監(jiān)控并顯示到這個視圖中。
三、監(jiān)視數(shù)據(jù)庫中所有索引的使用情況
1、生成開始/結(jié)束監(jiān)視索引的SQL腳本：
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
select 'alter index 'owner'.'index_name' monitoring usage;'
from dba_indexes
where owner in ('YOUR','PROD_DB','OWNER','LIST');
spool off
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
select 'alter index 'owner'.'index_name' nomonitoring usage;
'
from dba_indexes
where owner in ('YOUR','PROD_DB','OWNER','LIST');
spool off
set heading on
set echo on
set feedback on
2、進行監(jiān)視并查詢結(jié)果：
在業(yè)務(wù)量比較多的一天上班時運行start_index_monitor.sql，下班前運行stop_index_monitor.sql，之后就可以在各用戶自己的v$object_usage視圖中看到該SCHEMA下的索引使用情況了：
SQL> conn t/t
Connected.
SQL> select index_name,table_name,used
2 from v$object_usage
3 where used='NO';
INDEX_NAME TABLE_NAME USED
------------ ------------------------------ ----
TEST_PK TEST NO
1 row selected.
SQL>
3、改進結(jié)果查尋方法
你也許已經(jīng)注重到，上面查詢結(jié)果是需要我們單獨查詢各SCHEMA中的v$object_usage，其實我們可以通過給v$object_usage視圖添加一個owner列來創(chuàng)建一個可以存儲所有SHCEMA的v$object_usage視圖，不妨叫做v$all_object_usage，定義如下：
CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
(
OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING
)
AS
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.en
