程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 應用alter index ××× monitoring usage;語句監控索引使用與否，altermonitoring

應用alter index ××× monitoring usage;語句監控索引使用與否，altermonitoring

編輯：Oracle教程

應用alter index ××× monitoring usage;語句監控索引使用與否，altermonitoring

　　隨著時間的累積，在沒有很好的規劃的情況下，數據庫中也許會存在大量長期不被使用的索引，如果快速的定位這些索引以便清理便擺在案頭。我們可以使用"alter index ××× monitoring usage;"命令將索引至於監控狀態下，經過一定的監控周期，那些不被使用到的索引便會在具體Schema下的v$object_usage視圖中得以體現。展示一下這個過程，供參考。
友情提示：生產數據庫中的索引添加和刪除一定要慎重，需要做好充分的測試。

1.環境准備

--1、創建表T
SQL> create table t (x int);

Table created.

--2、初始化一條數據
SQL> insert into t values (1);

1 row created.

SQL> select * from t;

         X
----------
         1

--3、在表T的X字段上創建索引
SQL> create index i_t on t(x);

Index created.

2.將索引I_T置於監控狀態下

SQL> alter index I_T monitoring usage;

Index altered.

3.查看v$object_usage視圖中記錄的信息

SQL> col INDEX_NAME for a10
SQL> col TABLE_NAME a10
SQL> col START_MONITORING for a20
SQL> col END_MONITORING for a20
SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING  USED      START_MONITORING     END_MONITORING
---------- ---------- ---------- --------- -------------------- -----------------
I_T        T          YES        NO        07/17/2010 22:27:13

此時MONITORING字段內容為“YES”，表示I_T已經處於被監控狀態。USED字段內容為“NO”表示該索引還未被使用過。

4.模擬索引被使用

SQL> set autot on
SQL> select * from t where x = 1;

         X
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2616361825

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T  |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X"=1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        508  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

從執行計劃上可以看出，該查詢使用到了索引I_T

5.再次查看v$object_usage視圖中記錄的信息

SQL> set autot off
SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED      START_MONITORING     END_MONITORING
---------- ---------- --------- --------- -------------------- -----------------
I_T        T          YES       YES       07/17/2010 22:27:13

此時USED字段內容變為“YES”，表示I_T索引在監控的這段時間內被使用過。
如果在一個較科學的監控周期下USED字段一直處於“NO”的狀態，則可以考慮將此類索引刪掉。

6.停止對索引的監控，觀察v$object_usage狀態變化

SQL> alter index I_T nomonitoring usage;

Index altered.

sec@ora10g> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED      START_MONITORING     END_MONITORING
---------- ---------- --------- --------- -------------------- -------------------
I_T        T          NO        YES       07/17/2010 22:27:13  07/17/2010 22:32:27

此時MONITORIN字段內容為“NO”，表示已經停止對索引I_T的監控。

7.再次啟用索引監控，觀察v$object_usage狀態變化

SQL> alter index I_T monitoring usage;

Index altered.

sec@ora10g> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED      START_MONITORING     END_MONITORING
---------- ---------- --------- --------- -------------------- ------------------
I_T        T          YES       NO        07/17/2010 22:36:40

MONITORIN字段內容為“YES”，表示索引I_T處於被監控中；USED字段為“NO”，表示再次啟用監控後的這段時間內該索引沒有被使用過。
停起對索引的監控的過程相當於索引監控重置的過程。

8.一次性生成當前用戶下所有索引的監控語句

可以使用SQL生成SQL腳本的方法來完成。
以對SECOOLER用戶下所有索引生成監控語句為例

SQL> select 'alter index '||owner||'.'||index_name||' monitoring usage;' as "Monitor Indices Script" from dba_indexes where owner in ('SECOOLER');

Monitor Indices Script
---------------------------------------------------------------
alter index SECOOLER.I_T monitoring usage;
…… 省略 ……

如果您對PL/SQL熟悉的話，可以更方便的完成批量將索引置為被監控狀態。

SQL> conn secooler/secooler
SQL> begin
  2  for rec in (select index_name from user_indexes)
  3    LOOP
  4        dbms_output.put_line(rec.index_name);
  5        EXECUTE IMMEDIATE 'alter index '||rec.index_name||' monitoring usage';
  6    end loop;
  7  end;
  8  /

I_T
…… 省略其他索引名字 ……

PL/SQL procedure successfully completed.