“rebuild index online hang住" 問題解析

1.Rebuild index online 的原理
關於兩種Rebuild index的區別，可以詳細看一下metalink上的這篇文章：Index Rebuild Is Hanging Or Taking Too Long [ID 272762.1]，主要看下面這段文字：

When you rebuild index online,
- it will do a full tablescan on the base table.
- At the same time it will maintain a journal table for DML data, which has
changed during this index rebuilding operation.
So it should take longer time, specially if you do lots of DML on the same table,
while rebuilding index online.

On the other hand, while rebuilding the index without online option, Oracle will grab
the index in X-mode and rebuild a new index segment by selecting the data from
the old index. So here we are
- not allowing any DML on the table hence there is no journal table involved
- and it is doing an index scan
Hence it will be pretty fast.

總結一下，rebuild index online速度慢，但是期間不會阻塞對基表的DML操作；而普通的rebuild index正好相反，速度快，但是會阻塞對基表的DML操作。

但是在10g中經常會碰到一個問題：如果rebuild index online開始時，資料庫有一個未提交的長事務正在執行，不但rebuild index online會hang住，連應用也會hang住，看下面實驗：

2. Rebuild index online 阻塞應用的實驗：

實驗前的準備工作：
--建表：
A105024@O02DMS1>create table test(a int,b varchar2(64));

Table created.

--插入資料：
begin
for i in 1..1000000 loop
insert into test values(i,'ok');
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

--建立索引
A105024@O02DMS1>create index idx_a1 on test(a);

Index created.

實驗開始：
--開啟一個session，記下session id：
A105024@O02DMS1>select userenv('sid') from dual;

USERENV('SID')
--------------
           528
--隨便更新其中一條記錄，不提交：

A105024@O02DMS1>update test set a=101 where a=1;

1 row updated.

--產生鎖如下：

A105024@O02DMS1>select * from v$lock where sid=528;

ADDR     KADDR      SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
0C5C332C 0C5C3344   528 TM     703614          0          3          0        138          0
0C706ECC 0C706EF0   528 TX     786450      28530          6          0        138          0

--開啟另一個session，記下session id：
A105024@O02DMS1>select userenv('sid') from dual;

USERENV('SID')
--------------
           519
--執行線上重建索引：
A105024@O02DMS1>alter index idx_a1 rebuild online;
--查一下看被那個session block住：
A105024@O02DMS1>select SID,BLOCKING_SESSION from v$session where sid=519;

  SID BLOCKING_SESSION
----- ----------------
  519              528
--從上面可以看出重建索引的session正是被之前的update 操作阻塞了，再看一下鎖的情況：
A105024@O02DMS1>select * from v$lock where sid in (528,519);

ADDR     KADDR      SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
0D69FB10 0D69FB24   519 DL     703614          0          3          0        600          0
0D69FBC8 0D69FBDC   519 DL     703614          0          3          0        600          0
0C5C332C 0C5C3344   528 TM     703614          0          3          0        969          1
0C5C33F0 0C5C3408   519 TM     703614          0          2          4        600          0
0C5C34B4 0C5C34CC   519 TM     703617          0          4          0        598          0
0C706ECC 0C706EF0   528 TX     786450      28530          6          0        969          0

--原來519被阻塞是因為它要請求一個模式為4的鎖，而這個鎖在528手裡。

--如果這時對基表進行DML操作，會不會也會阻塞呢？我們再開個session:
A105024@O02DMS1>select userenv('sid') from dual;

USERENV('SID')
--------------
           524
--隨意更新基表的一行（該行和第一次更新的行是不同行）：
A105024@O02DMS1>update test set a=102 where a=2;
--發現也被阻塞了，看一下是被哪個session阻塞了：
A105024@O02DMS1>select SID,BLOCKING_SESSION from v$session where sid=524;

  SID BLOCKING_SESSION
----- ----------------
  524              519
--果然是被519阻塞了，再看一下鎖的情況：

A105024@O02DMS1>select * from v$lock where sid in (528,519,524);

ADDR     KADDR      SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
0D69FB10 0D69FB24   519 DL     703614          0          3          0       1071          0
0D69FBC8 0D69FBDC   519 DL     703614          0          3          0       1071          0
0C5C332C 0C5C3344   528 TM     703614          0          3          0       1440          1
0C5C33F0 0C5C3408   519 TM     703614          0          2          4       1071          0
0C5C34B4 0C5C34CC   519 TM     703617          0          4          0       1069          0
0C5C3578 0C5C3590   524 TM     703614          0          0          3        108          0
0C706ECC 0C706EF0   528 TX     786450      28530          6          0       1440          0

--目前的情況是524在等519，而519又在等528，因此不但rebuild index online這個操作無法完成，連對基表的DML操作都無法完成，這個應用就hang住了。

3.解決方案
碰到這種情況，千萬不要隨意重啟資料庫或殺rebuild index session，而是應該找到阻塞rebuild index的session，讓它提交或者把它殺掉：

A105024@O02DMS1>alter system kill session '528,763';

System altered.

這時對基表的DML操作都可以順利進行：
A105024@O02DMS1>update test set a=102 where a=2;

1 row updated.

且等一段時間後，rebuild index online也會完成。

要想更清楚的瞭解其中鎖的佔用情況，可以看一下NinGoo的部落格：

rebuild index online的鎖機制淺析  http://www.ningoo.net/html/2008/lock_mechanism_of_rebuild_index_online.html

4. 總結
從上面的試驗可以看出，雖然rebuild index online在執行期間不會阻塞DML操作，但在操作的開始和結束階段，會阻塞表上的所 有DML操作。所以我們在做rebuild index online的時候，一定要在開始和結束階段觀察系統中是否有長事務的儲存，對於併發量較大的系統，最嚴重的後果，可能在這兩個關鍵點導致資料庫產生大量 鎖等待，系統負載飆升，甚至當機等。

注意：在11g後，Oracle做了改進，這個問題已經不存在了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26277071/viewspace-710807/，如需轉載，請註明出處，否則將追究法律責任。