11g-你敢不敢Create or Rebuild index online?

不知道有没有朋友，这样干过，在事务繁忙的系统上Create index....online或者alter index.... rebuild online,我曾经在盲目的情况下，10g生产库搞了一把，结果非常惨淡。
下面我们探究一下，10g这个堵塞的过程是如何产生的，以及11g有那些改变。
当Create或者Rebuild进程开始和结束的时候，oracle要在base table上获得Table lock，因为在索引开始创建的时候要保证DD信息，结束的时候要对索引改变做merge，把索引放到最终的结构中。
1,当Create或者Rebuild操作开始和终止要求一个table lock，此时有事务正在操作base table。那么，Create或者Rebuild 索引就会处于Hang状态。当创建索引的session Hang之后，接下来的需要对base table操作的事务，都将处于Hang的状态，这个持续会在创建索引的session获得需要的Table lock后结束，那么在高并发的系统中，效果可想而知了.....

2,在11g中，有一些变化，其实和之前的版本一致，Create或者Rebuild online在开始和结束的时候要获得Table lock，如果在这之前有相关的active事务，那么也会Hang。
区别在于：当Create或者Rebuild online的进程被锁定，那么后续的活跃事务将不会被阻塞，其它的事务可以顺利的进行。所以，在11g中可能事务繁忙的时候Create or Rebuild online，风险降低了点。

下面做个小测试，观察这个过程：

~~~~~~~~~~~~~~~~~
10g：
~~~~~~~~~~~~~~~~~
Session1:

SQL> select sid from v$mystat where rownum=1;

       SID
----------
      1424
SQL> create table leo as select rownum id,'Oracle' name from dual
  2  connect by level<=100000;

Table created

SQL> create index ind_leo on leo(id);

Index created.

Session2:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
      1418
SQL> insert into leo values(100001,'Tt');

1 row created.
Session2里插入一行，我们不要提交，接下来切换到Session1里面执行：

SQL> alter index ind_leo rebuild online;
....
此时Session1处于Hang状态，我们看看它在等待什么？
SQL>@lock

Username   Kill String   Table Name                          Lock Held            Lock Requested    Seconds
---------- ------------- ----------------------------------- -------------------- -------------- ----------
LICHAO     1418,18       SYS.KOTTD                           Exclusive                0             709
                         LICHAO.LEO                          Row Exclusive            0             709
LICHAO     1424,5        LICHAO.LEO                          Row Exclusive            0             591
                         LICHAO.SYS_JOURNAL_52744            Share                    0             589
                         LICHAO.LEO                          Row Share                4             591
Session1在请求获得Shared lock，再打开一个Session3：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
      1410
SQL> insert into leo values(100002,'DBA');
...
Session3也处于Hang状态，
SQL>@lock

Username   Kill String   Table Name                          Lock Held            Lock Requested    Seconds
---------- ------------- ----------------------------------- -------------------- -------------- ----------
LICHAO     1418,18       SYS.KOTTD                           Exclusive                0             801
                         LICHAO.LEO                          Row Exclusive            0             801
LICHAO     1424,5        LICHAO.LEO                          Row Exclusive            0             603
                         LICHAO.SYS_JOURNAL_52744            Share                    0             601
                         LICHAO.LEO                          Row Share                4             591

我们查看相关Lock信息并没有看到Session3相关的blocking，或者Session3只是看起来被阻塞了,但实际上也是被阻塞了。
接下来，commit Session2，那么Session1将获得Lock，Session3也不会被阻塞。
那么我们如果没有commit session3，那么索引rebuild无法结束，因为在结束的时候任然要获得Table lock：

SQL>@lock
USERNAME                       KILL                 TAB                       LMODE                  REQUEST      CTIME
------------------------------ -------------------- ------------------------- ------------------- ---------- ----------
LICHAO                         1410,16              SYS.SUBPARTCOL$           Exclusive                    0        113
LICHAO                         1410,16              LICHAO.LEO                Row Exclusive                0        113
LICHAO                         1424,5               SYS.LIBRARY$              Exclusive                    0        113
LICHAO                         1424,5               LICHAO.LEO                Row Exclusive                0       1408
LICHAO                         1424,5               LICHAO.SYS_JOURNAL_52744  Share                        0       1406
LICHAO                         1424,5               LICHAO.LEO                Row Share                    4        113

当提交Session3，索引Rebuild执行成功。
~~~~~~~~~~~
11g：
~~~~~~~~~~~

SQL> select*from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Session1：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       191
SQL> create table leo as select rownum id,'Oracle' name from dual
  2  connect by level<=100000;

Table created.

SQL> create index ind_leo on leo(id);

Index created.

Session2：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        69
SQL> insert into leo values(100001,'Tt');

1 row created.

Session2里面任然插入一行，不要提交，切换到Session1里面执行：

SQL>alter index ind_leo rebuild online;
...

Rebuild也是被阻塞，我们主要来看一下Session1获得Lock后，之后开始的其它相关Session会不会堵塞。

Session3：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       196
SQL> insert into leo values(100002,'DBA');

1 row created.

SQL> commit;

Commit complete.

正如看到的，Session3没有处于Hang的状态，而是立即插入了行，我们去提交，也是正常的，我们来看看数据库现在的lock等待情况：

SQL>@lock
Username   Kill String   Table Name                          Lock Held            Lock Requested    Seconds
---------- -------------------- ------------------------- -------------------- ---------- ----------
LICHAO     191,5                LICHAO.IND_LEO            Exclusive                     0        385
LICHAO     191,5                SYS.ICOL$                 Exclusive                     0        385
LICHAO     191,5                LICHAO.LEO                Row Exclusive                 0        385
LICHAO     191,5                LICHAO.LEO                Row Share                     0        385
LICHAO     191,5                LICHAO.LEO                Share                         0        385
LICHAO     191,5                LICHAO.SYS_JOURNAL_76495  Share                         0        385
LICHAO     191,5                SYS.ORA$BASE              Share                         0        748
LICHAO     191,5                SYS.OBJ$                                                4        385
LICHAO     196,17               SYS.ORA$BASE              Share                         0        707
LICHAO     69,7                 SYS.OBJ$                  Exclusive                     0        487
LICHAO     69,7                 LICHAO.LEO                Row Exclusive                 0        487
LICHAO     69,7                 SYS.ORA$BASE              Share                         0        725

如上所述，11g中在Create或者Rebuild online的session被阻塞之后，其他在base table上的事务是不会被阻塞的，可以正常的执行，这就是10g和11g的区别，那么11g online rebuild index的风险就降低了。