監視index的使用看看是否需要重建！

我們知道index的key值被刪除之後其使用的空間並不會理解釋放，只有當這個index block空間完全不被使用之後才可以被再次使用，這也就是index的pctused為0的緣故。有些時候由於表中的資料被大量刪除，此時index的leaf block中保留了很多沒有用的key 值，不僅浪費了空間，而且再次使用該index時效率可能也極其低下，可以透過對index的監視以及對index結構的有效性驗證來判斷index是否需要重建，重建時在高可用性系統中當然還需要考慮lock的問題。[@more@]

SQL> desc t
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME VARCHAR2(30)

SQL> select count(*)from t;

COUNT(*)
----------
9848

SQL> create index idx_t on t(id);

索引已建立。
SQL> SET autotrace on
SQL> select count(*)from t where id=1;

COUNT(*)
----------
1

執行計劃
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - access("ID"=1)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SET autotrace off
SQL> delete from t where id>1;

已刪除9847行。

SQL> commit;

提交完成。

SQL> analyze index idx_t validate structure;

索引已分析

SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats where name='IDX_
T';

BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
32 9848 21 9847

SQL> alter index idx_t monitoring usage;

索引已更改。
SQL> select count(*)from t where id=1;

COUNT(*)
----------
1

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES YES

SQL> alter index idx_t nomonitoring usage;

索引已更改。

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T NO YES
SQL> set autotrace on
SQL> select count(*)from t where id=1;

COUNT(*)
----------
1

執行計劃
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T | 1 | 2 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - filter("ID"=1)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> set autotrace off
SQL> alter index idx_t monitoring usage;

索引已更改。

SQL> select /*+ rule */ count(*)from t where id=1;

COUNT(*)
----------
1

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES YES

SQL> set autotrace on
SQL> select /*+ rule */ count(*)from t where id=1;

COUNT(*)
----------
1

執行計劃
----------------------------------------------------------
Plan hash value: 1500240790

-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | INDEX RANGE SCAN| IDX_T |
-----------------------------------

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

2 - access("ID"=1)

Note
-----
- rule based optimizer used (consider using cbo)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter index idx_t nomonitoring usage;

索引已更改。

SQL>
--================================
SQL> alter index idx_t monitoring usage;

索引已更改。

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES NO

SQL> set autotrace on
SQL> select /*+ no_index(t idx_t) */ count(*)from t where id=1;

COUNT(*)
----------
1

執行計劃
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 2 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("ID"=1)

統計資訊
----------------------------------------------------------
127 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ no_index(t idx_t) */ count(*)from t where id=1;

COUNT(*)
----------
1

執行計劃
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 2 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("ID"=1)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
IDX_T T YES NO

SQL>

我的試驗效果不是很理想，當刪除了大量資料之後，還是選擇使用了index，透過執行計劃對比發現使用索引所需要的邏輯讀確實比全表掃表還要小。