索引rebuild和rebuild online時要慎重(轉載）

這是曾經發生的一次故障，作為教訓和備忘還是決定記錄下來。

生產系統有個千萬行級別的表，原本是晚上跑的job中，因某個儲存過程中的批次插入語句直到第二天白天沒法執行完原因，合作伙伴工程師覺得系統有點慢，為了加開插入速度而直接將該表主鍵意外的索引都刪除掉了。結果出現大量全表掃描和行鎖競爭（row lock contention），導致OLTP系統根本無法辦理業務。雖然作為DBA的我採用了rebuild online方式，但是還是無濟於事，終於關掉應用後重新建立了索引，具體所花時間如下：

2010-9-4 系統崩潰事件

1、刪除表table_name上除主鍵意外的索引 10分鐘

2、執行儲存過程(含批次插入語句)

3、重建索引 1.5小時

4、表和索引統計資訊重新收集 10分鐘

以上過程中只需要用到如下簡單SQL語句:

drop index index_name

Alter indexindex_name rebuild;

Alter indexindex_name rebuild online;

 

create index index_name on table_name (column_name)

tablespace tablespac_name

 

 

BEGIN

dbms_stats.gather_table_stats(ownname => 'Owner',tabname => 'table_name',cascade => TRUE);

end;

 

實際上作為DBA，對生產系統執行任何操作時，僅僅懂這些還是遠遠不夠的。需要明確如下幾點：

1、是不是業務時間（或業務高峰期），如果是，則儘量不要進行類此操作。因為在大物件中建立索引時不僅需要較大temp表空間，而且基於表原有索引的所有SQL語句的執行計劃都發生變化。這樣，這些SQL的重新解釋需要大量CPU資源。

2、當原來索引被刪除後，出現大量的全表掃描。這不僅對系統I/O產生壓力，而且buffer catch中的已快取資料塊很容易被擠出去，不僅對SGA帶來壓力，而且對I/O產生惡性迴圈。

3、綜合CPU、記憶體、I/O方面資源緊張，在加上正常業務辦理需要的各類DML操作，做種導致row lock contention、read by other session等一堆等待，最終系統被出現無法辦理業務的等待（系統慢）狀態。

4、瞭解rebuild和rebuild online區別。

 

因此在進行重建或建立所以之前，很有必要充分考慮生產環境、資料庫物件的大小和用途、temp表空間大小，硬體資源、回退方案等等

 

注:rebuild和rebuild online的區別

1、當rebuild 時一般對原先索引進行INDEX FAST FULL SCAN。

2、當rebuild online的時不用原先索引而執行TABLE ACCESS FULL

3、rebuild和rebuild online都會發生sort，即需要用到temp表空間。

4、rebuild 會阻塞dml語句而rebuild online則不會。

5、rebuild online時系統會產生一個SYS_JOURNAL_xxx的IOT型別的系統臨時日誌表，所有rebuild online時索引的變化都記錄在這個表中，當新的索引建立完成後，把這個表的記錄維護到新的索引中去，然後drop掉舊的索引，rebuild online就完成了。