索引可以加快查詢的速度，但索引會佔用許多儲存空間，在插入和刪除行的時候，索引還會引入額外的開銷，因此確保索引得到有效利用是我們很關注的一個問題。在Oracle9i之前，要知道一個索引是否被使用是困難的，而Oracle 9i中提供了一個有效的監控方法：ALTER INDEX MONITORING USAGE。

這裡我以一個已有的索引，來演示怎樣監控其使用情況，演示之前，我們可以查詢一下檢視v$object_usage，看看有什麼結果，如果以前沒啟用過該功能，則查詢不到任何資訊。

首先，針對已有的index，啟用其監視功能：

alter index INDEX_DEPLOYEE_IS_SNZY monitoring usage;
alter index INDEX_DEPLOYEE_IS_UPDATE monitoring usage;
alter index INDEX_DEPLOYEE_IDCARD monitoring usage;
alter index INDEX_DEPLOYEE_SCHOOLID monitoring usage;
alter index INDEX_DEPLOYEE_BY_HAND monitoring usage;
alter index INDEX_DEPLOYEE_COUNTY monitoring usage;
alter index INDEX_DEPLOYEE_HJXZ monitoring usage;
alter index INDEX_DEPLOYEE_IS_FEE monitoring usage;
alter index INDEX_DEPLOYEE_LOWER monitoring usage;
alter index INDEX_DEPLOYEE_PROVINCE monitoring usage;
alter index INDEX_DEPLOYEE_SPE monitoring usage;
alter index INDEX_DEPLOYEE_STATUS monitoring usage;

然後檢視檢視v$object_usage，結果如下：

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME   MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ----------- ---------- ---- ------------------- -------------------
INDEX_DEPLOYEE_IS_SNZY          T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_IS_UPDATE        T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_IDCARD           T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_SCHOOLID         T_DEPLOYEE   YES        NO 12/10/2010 17:08:26
INDEX_DEPLOYEE_BY_HAND          T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_COUNTY           T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_HJXZ             T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_IS_FEE           T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_LOWER            T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_PROVINCE         T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_SPE              T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_STATUS           T_DEPLOYEE   YES        NO   12/10/2010 17:08:26

14 rows selected

然後透過查詢SQL語句，使部分索引起作用，再檢視該檢視，結果如：

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME   MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ----------- ---------- ---- ------------------- -------------------
INDEX_DEPLOYEE_IS_SNZY          T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_IS_UPDATE        T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_IDCARD           T_DEPLOYEE   YES        YES   12/10/2010 17:08:26
INDEX_DEPLOYEE_SCHOOLID         T_DEPLOYEE   YES        YES 12/10/2010 17:08:26
INDEX_DEPLOYEE_BY_HAND          T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_COUNTY           T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_HJXZ             T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_IS_FEE           T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_LOWER            T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_PROVINCE         T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_SPE              T_DEPLOYEE   YES        NO   12/10/2010 17:08:26
INDEX_DEPLOYEE_STATUS           T_DEPLOYEE   YES        NO   12/10/2010 17:08:26

14 rows selected

若不再需要監控索引使用情況時，只需要執行如下語句取消監控即可：

alter index INDEX_DEPLOYEE_IS_SNZY nomonitoring usage;
alter index INDEX_DEPLOYEE_IS_UPDATE nomonitoring usage;
alter index INDEX_DEPLOYEE_IDCARD nomonitoring usage;
alter index INDEX_DEPLOYEE_SCHOOLID nomonitoring usage;
alter index INDEX_DEPLOYEE_BY_HAND nomonitoring usage;
alter index INDEX_DEPLOYEE_COUNTY nomonitoring usage;
alter index INDEX_DEPLOYEE_HJXZ nomonitoring usage;
alter index INDEX_DEPLOYEE_IS_FEE nomonitoring usage;
alter index INDEX_DEPLOYEE_LOWER nomonitoring usage;
alter index INDEX_DEPLOYEE_PROVINCE nomonitoring usage;
alter index INDEX_DEPLOYEE_SPE nomonitoring usage;
alter index INDEX_DEPLOYEE_STATUS nomonitoring usage;

v$object_usage檢視解釋

從上面的例子中我們可以看出，索引的監視資訊都是存在在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#

下面是該檢視列的描述：
INDEX_NAME: sys.obj$.name 中的索引名字
TABLE_NAME: sys.obj$obj$name 中的表名
MONITORING: YES (索引正在被監控), NO (索引沒有被監控)
USED: YES (索引已經被使用過), NO (索引沒有被使用過)
START_MONITORING: 開始監控的時間
END_MONITORING: 結束監控的時間
所有被使用過至少一次的索引都可以被監控並顯示到這個檢視中。

監控Index是否被使用過的方法

相關文章