alter index rebuild online 所引起 DB hang 事件

大家都知道 alter index rebuild online  是可以同時進行DML 操作的，但要是操作不正確有可能造成 DB hang 現象

以下做一個實驗  (DB版本 10.2.0)
--建一個 table
SQL> create table test(a int,b varchar2(64));

Table created.

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

PL/SQL procedure successfully completed.

--建立index
SQL> create index idx_a1 on test(a);

Index created.

--更新資料 , 建立一個lock  , 不要commit
SQL> update test set a=1000000 where a=110;


--開啟另外一個視窗,在線重建索引
SQL> alter index idx_a1 rebuild online;

發現此命令過了很久還不能執行完畢
查一下 v$lock
select * from v$lock where block >0;

看來  rebuild online 正在等待 sid 160 ，也就是一開始的 update 的指令， rebuild online 之前的未完成的事務它會去等待它，假設這時候又有一個DML操作 ?!   我們再繼續實驗下去。

--開啟另外一個視窗，進行 update

SQL> update test set a=1000000 where a=111;   

過了10分中，還沒執行完畢，果真整個 hang 住

我們查一下 lock 

可以明顯的看到 text 這個table 目前鎖的來源(TX)是 SID 160 也就是 一開始的 update 的指令，只要這個SID 160 事務不提交的話，後面的業務也無法作業。這邊也看到 rebuild online 對表加了個Level 4 的鎖(share: 共享鎖 禁止其他DML的操作)把整個 table lock 住後面的業務都再等 rebuild online ，導致系統 hang 在這邊

第一 kill 掉 rebuild online  就可以運作
第二 kill 掉 在 update 的業務就可以運作


同樣的實驗在11G 上驗證  (DB版本 11GR2)

--建一個 table
SQL> create table test(a int,b varchar2(64));

Table created.

--插入資料
SQL> begin
  2  for i in 1..1000000 loop
  3  insert into test values(i,'ok');
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

--建立index
SQL> create index idx_a1 on test(a);

Index created.

--更新資料 , 模擬出一個lock  , 不要commit
SQL> update test set a=1000000 where a=110;


--開啟另外一個視窗,在線重建索引
SQL> alter index idx_a1 rebuild online;

發現此命令過了很久還不能執行完畢
查一下 v$lock
select * from v$lock where block >0;

看來  rebuild online 正在等待 sid 33 ，也就是一開始的 update 的指令

這時候 開啟另外一個視窗
SQL> update test set a=1000000 where a=111;

1 row updated.

結論是: 11G 修正了這個問題 ，rebuild online  之後的業務可以正常運作系統不會 hang ，但 rebuild online 還是會等到所有 update 完成後，才能執行完成