alter index rebuild online引发的血案

alter index rebuild online引发的血案

‘早上起来没有一个人……‘，伸手抓起手机，‘喂，……应用hang住了……rac的一节点在手工shutdown……需要到现场……’。赶紧穿上衣服，拿起电脑往客户办公室赶。路上还接到客户电话具体描述了下故障现象。
原来是客户在rac的其中一个节点进行index rebuild online操作，结果命令发出去不久应用就hang住了，没有办法的客户就直接用了大绝招：把操作的节点数据库重启了，现在的问题是shutdown immediate也hang住了。听到这里，马上喊他看日志，回复日志没报错，郁闷的shutdown，如果有大事务在运行，岂不是要rollback死，由于此应用级别很高，等不起，只能死马当活马医了。不管了，先让它关了在说，先直接操作系统杀客户端进程，使资源能更快的释放。于是，指导输入命令ps –ef|grep oracle|grep LOCAL=NO|grep –v grep|awk ‘{awk print $2}’|xargs kill -9。系统终于关闭了，直接startup……
等我到现场时，系统居然还没启动起来，难道真的有大事务需要恢复，难道就是重建索引？
登录服务器查看，日志显示数据库正在recover，想起经常的recover几个小时的情况，我再次对贸然的shutdown很是郁闷。不过好在是rac，可客户反应即使连接到好的节点还是不能使用，登录查看，检查锁，检查等待。发现存在大量的锁，而且有一个锁已经2个月了，恰恰就是重建索引的表，明显这个锁有问题，直接kill。等一会，另一个节点启动好了，问应用的情况，也恢复了，就这么简单？就是那个锁的问题？我晕，守一会业务后我就撤退了。
在路上我就在想，rebuild index online怎么会把整个应用hang死，难道它锁了整个表，怎么会呢，在印象中online的同时是可以进行dml操作的啊，奇了怪了，先睡一觉，空了再来重现故障分析……
N天过后……
先上一篇文章，关于rebuild和rebuild online的区别metalink Note:272762.1 
大家耐心看完哟，原理是很重要的……
========
- Online Index rebuild takes a long time.
- ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX
Symptoms:
=========
Performance issues while rebuilding very large indexes.
- The offline rebuilds of their index is relatively quick -finishes in 15 minutes.
- Issuing index rebuild ONLINE statement => finishes in about an hour.
- This behavior of ONLINE index rebuilds makes it a non-option for large tables
as it just takes too long to scan the table to rebuild the index. The offline may not be feasible due to due to the 24/7 nature of the database.
- This may be a loss of functionality for such situations.
- If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter hanging behavior indefinitely (or more than 6 hours).
DIAGNOSTIC ANALYSIS:
--------------------
We can trace the sessions rebuilding the indexes with 10046 level 12.
Comparing the IO reads for the index-rebuild and the index-rebuild-online reveals the following:
-ONLINE index rebuilds
It scans the base table and it doesn't scan the blocks of the index.
-OFFLINE index rebuilds
It scans the index for the build operation.
- This behaviour is across all versions.
Cause
Cause/Explanation
=============
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.
Fix
Solution/Conclusion:
===========
- The ONLINE index rebuild reads the base table, and this is by design.
- Rebuilding index ONLINE is pretty slow.
- Rebuilding index offline is very fast, but it prevents any DML on the base table.
好，我们现在明白了，rebuild online是可以同时进行dml操作的，但是online会维护一个操作日志，会使rebuild时间大幅加长。巴拉巴拉……这一切的一切，跟我遇到的应用挂起没有任何关系啊，难道是应用骗了我，其实应用可以用？还是模拟一下当时的场景再说。
Action：
Conn oracle/oracle
--建表
create table test(a int,b varchar2(64));
--插入数据
begin
for i in 1..1000000 loop
insert into test values(i,'ok');
commit;
end loop;
end;
/
--创建索引
create index idx_a1 on test(a);

准备工作做好了……
--更新其中一条记录，制造一个排他锁，不要提交
SQL>   update test set a=1000000 where a=103;
--打开另一个窗口，在线重建索引
SQL> alter index idx_a1 rebuild online;
……
发现此命令长时间不能执行完，没关系，大表的索引重建是需要大量时间的，查下等待和锁吧。

sys@ORCL_SQL> select sid,event,P1TEXT,state from v$session_wait where event not in ('SQL*Net message from client');

    SID EVENT                          P1TEXT                         STATE
------- ------------------------------ ------------------------------ --------------------------------------
    139 enq: TM - contention           name|mode                      WAITING
    149 Streams AQ: waiting for time m                                WAITING
        anagement or cleanup tasks

    151 Streams AQ: qmn coordinator id                                WAITING
        le wait

    155 rdbms ipc message              timeout                        WAITING


sys@ORCL_SQL> select * from v$lock where block>0;

ADDR     KADDR        SID TYPE      ID1      ID2  Lock Held  Lock Req.      CTIME      BLOCK
-------- -------- ------- ---- -------- -------- ---------- ---------- ---------- ----------
315C4134 315C414C     154 TM      52543        0 ########## ##########         24          1
看到木有，重建根本就没有进行，而是在等待sid154，这个sid就是我们开始的update的命令，看来rebuild online是不会阻碍dml操作，但是在它之前的dml操作它会去等待，知道资源释放，如果这时有个大事务一直不十分资源，那就恭喜了，你的重建就会hang在这里。慢着，这里hang住了对应用不会有影响吧，想当然是，马上测试
又打开个新连接
SQL> update test set a=1000000 where a=102;
……
1分钟过去了，此命令没有成功，真的hang住了，整个表被锁住了？
再次检查锁

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED               MODE_HELD                    LOCK_ID1          LOCK_ID2
----------------- ----------------- ---------------------------- ---------------------------- ----------------- -----------------
154               None
   139            DML               Share                        Row-X (SX)                   52543             0
   142            DML               Row-X (SX)                   Row-X (SX)                   52543             0
这里解释一下，154是我们最开始update的sid，139是rebuild online的sid，而142是我们刚才update的sid，看来它们都在等sid154，也就是说，这个时候我不能对此表做dml操作了，也就是对于此操作非常频繁的表来说，应用已经hang住了！！
看来rebuild online之前一定要检查下系统，其实是做所有操作之前都要这样做，确认系统没问题后再做操作。
在贴下锁的具体情况，大家看rebuild操作的139有几个行排他锁，我怀疑是它需要先把所有的数据都用排他锁锁住，然后其它dml的操作它会记入辅助日志，而我这里的情况是有个行锁一直存在，就造成了它把其他行都锁住了，但一直等待最开始的那个行锁释放，这个时候如果我开始那个锁不释放，系统也就挂在这里了！！
    SID SERIAL# USERNAME   Term   Resource Type        TAB        OWNER         ID1      ID2 Lock Held      Lock Req.           CTIME
------- ------- ---------- ------ -------------------- ---------- -------- -------- -------- -------------- -------------- ----------
    139      11 ORACLE     pts/1  DLSystem             TEST       ORACLE      52543        0 Row Exclusive                       2958
    139      11 ORACLE     pts/1  DLSystem             TEST       ORACLE      52543        0 Row Exclusive                       2958
    154       9 ORACLE     pts/2  TM - DML Enqueue     TEST       ORACLE      52543        0 Row Exclusive                       2967
    139      11 ORACLE     pts/1  TM - DML Enqueue     TEST       ORACLE      52543        0 Row Share      Share                2958
    139      11 ORACLE     pts/1  TM - DML Enqueue     SYS_JOURNA ORACLE      52558        0 Share                               2957
                                                       L_52544

    142      33 ORACLE     pts/4  TM - DML Enqueue     TEST       ORACLE      52543        0                Row Excl             2844


sys@ORCL_SQL> select sql_text from v$sqltext where hash_value=&hash_value order by piece;
Enter value for hash_value: 1016442092

SQL_TEXT
alter index idx_a1 rebuild online