“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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26277071/viewspace-710807/