Oracle11g新特性：在线操作功能增强-Oracle11g在线重建索引功能增强 （转载）

Oracle 11g加强了ONLINE REBUILD索引功能，减少了ONLINE REBUILD索引过程中对DML操作的阻塞。下面看看10g中和11g在线重建索引的差别，首先登陆10g：
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已创建。
SQL> DELETE T WHERE ID = 1;
已删除 1 行。

    会话1中建立表和索引后，删除一条记录，且不提交。

SQL> SET SQLP 'SQL2> '
SQL2> ALTER INDEX IND_T_NAME REBUILD;
ALTER INDEX IND_T_NAME REBUILD
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
SQL2> ALTER INDEX IND_T_NAME REBUILD ONLINE;

    在会话2上 尝试重建索引。由于无法马上获取锁，直接REBUILD INDEX会报错，而REBUILD INDEX ONLINE则会处于等待状态，直到获取表的锁。

    这时在会话1上查询锁信息和会话2的等待情况：

SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;SID---------- 148SQL> SELECT SID FROM V$SESSION WHERE USERNAME = 'YANGTK';SID---------- 144148SQL> SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN (144, 148);SID TY ID1 CTIME BLOCK---------- -- ---------- ---------- ---------- 144 DL 52896 360 0144 DL 52896 360 0148 TM 52896 417 1144 TM 52896 360 0144 TM 52899 359 0148 TX 65575 417 0已选择6行。SQL> COL EVENT FORMAT A40SQL> COL P1TEXT FORMAT A20SQL> SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 144;EVENT P1TEXT P1 SECONDS_IN_WAIT---------------------------------------- -------------------- ---------- --------------- enq: TM - contention name|mode 1414332420 749 

    会话2处于等待锁的状态中，下面提交会话1的修改，随后马上执行一个DELETE操作。为了避免COMMIT和DELETE操作间隔时间太大，在文本编辑器中，将两个命令编辑好，拷贝到剪贴板，然后一起拷贝到SQLPLUS中：

SQL> COMMIT;提交完成。SQL> DELETE T WHERE ID = 2;已删除 1 行。SQL> SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN (144, 148);SID TY ID1 CTIME BLOCK---------- -- ---------- ---------- ---------- 144 DL 52896 828 0144 DL 52896 828 0148 TM 52896 26 1144 TM 52896 26 0144 TM 52899 827 0144 TS 7 26 0144 TX 262169 26 0148 TX 589847 26 0已选择8行。SQL> SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 144;EVENT P1TEXT P1 SECONDS_IN_WAIT---------------------------------------- -------------------- ---------- --------------- enq: TM - contention name|mode 1414332420 38

 

    再次查询，发现会话2仍然在等待锁信息，不过等待时间已经重新计算了。观察V$LOCK视图的信息可以发现会话2仍然是被锁住，但是状态已经和第一次不相同了。根据文档的描述，ONLINE REBUILD在开始和完成的时候都会去获取锁，现在应该是等待完成时的锁。

SQL> COMMIT;
提交完成。
SQL> DELETE T WHERE ID = 3;
已删除 1 行。

    在会话1再次执行COMMIT加DELETE操作，发现会话2的REBUILD操作已经完成。
索引已更改。

SQL2> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

    再来看11g中的情况：

SQL> CONN YANGTK/yangtk@ORA11G已连接。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已创建。
SQL> DELETE T WHERE ID = 1;
已删除 1 行。

    模仿上面的例子，在会话1中保持锁。会话2执行ONLINE REBUILD INDEX处于等待锁的状态。

SQL2> CONN YANGTK/yangtk@ORA11G已连接。SQL2> ALTER INDEX IND_T_NAME REBUILD ONLINE;在会话1中查询会话2的等待情况和锁占有情况：SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;SID---------- 131SQL> SELECT SID FROM V$SESSION WHERE USERNAME = 'YANGTK';SID---------- 131149SQL> SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN (131, 149);SID TY ID1 CTIME BLOCK---------- -- ---------- ---------- ---------- 149 AE 99 50 0149 OD 70713 47 0131 AE 99 78 0149 DL 70712 47 0149 DL 70712 47 0149 OD 70712 47 0149 TX 589851 47 0149 TM 70712 47 0149 TM 70715 47 0131 TM 70712 65 0149 TX 262171 47 0131 TX 589851 65 1已选择12行。SQL> SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 149;EVENT P1TEXT P1 SECONDS_IN_WAIT---------------------------------------- -------------------- ---------- --------------- enq: TX - row lock contention name|mode 1415053316 77 

    在11g中，会话2等待的是行级锁信息，尝试在会话1中提交并马上删除：

SQL> COMMIT;提交完成。SQL> DELETE T WHERE ID = 2;已删除 1 行。SQL> SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN (131, 149);SID TY ID1 CTIME BLOCK---------- -- ---------- ---------- ---------- 149 AE 99 110 0149 OD 70713 107 0131 AE 99 138 0149 DL 70712 107 0149 DL 70712 107 0149 OD 70712 107 0149 TX 196637 10 0149 TM 70712 107 0149 TM 70715 107 0131 TM 70712 10 0149 TX 262171 107 0131 TX 196637 10 1已选择12行。SQL> SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 149;EVENT P1TEXT P1 SECONDS_IN_WAIT---------------------------------------- -------------------- ---------- --------------- enq: TX - row lock contention name|mode 1415053316 15 

    最后在会话1再次提交：

SQL> COMMIT;
提交完成。
SQL> DELETE T WHERE ID = 3;
已删除 1 行。

    这是会话2的REBUILD操作完成：

索引已更改。
SQL2> SELECT * FROM V$VERSION;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

    看上去，10g和11g的ONLINE REBUILD操作并没有什么不同，在创建索引开始和结束的时候都要获取到表的锁。不过二者获取的锁信息不同，也就是说二者对于后续DML的影响并不相同。

    在11g中，ONLINE REBUILD操作获取的锁不会阻塞后续DML操作，而11g以前的版本，在ONLINE REBUILD索引过程中，获取到锁的一个短暂时刻会阻止DML操作。

    下面演示一下二者的区别，首先在11g中，一个会话运行ONLINE REBUILD，另一个会话同时运行对表的DML操作，第三个会话查询V$LOCK视图，检查ONLINE REBUILD操作是否会阻塞DML操作。
    会话1：

SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;
SID
----------
131
SQL> CREATE INDEX IND_T_ID ON T(ID);
索引已创建。
SQL> ALTER INDEX IND_T_NAME REBUILD ONLINE;
索引已更改。

    会话2：

SQL2> BEGIN
2 FOR I IN 1..100000 LOOP
3 UPDATE T SET ID = ID WHERE ID = 1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。

    会话3：

SQL> CONN YANGTK/yangtk@ORA11G已连接。
SQL> SET SQLP 'SQL3> '
SQL3> SET SERVEROUT ON
SQL3> DECLARE
2 V_NUM NUMBER;
3 V_TOTAL NUMBER DEFAULT 0;
4 BEGIN
5 FOR I IN 1..1000 LOOP
6 SELECT SUM(BLOCK) INTO V_NUM FROM V$LOCK WHERE SID = 154;
7 V_TOTAL := V_TOTAL + NVL(V_NUM, 0);
8 END LOOP;
9 DBMS_OUTPUT.PUT_LINE(V_TOTAL);
10 END;
11 /
0
PL/SQL 过程已成功完成。

     确保会话1的REBUILD ONLINE与会话2、会话3的存储过程几乎同时运行。可以看到在整个REBUILD索引过程中，没有阻塞DML操作。

    下面看看同样的操作在10g下执行的结果。

    会话1：

SQL> CONN YANGTK/YANGTK@YTK102已连接。
SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;
SID
----------
144
SQL> CREATE INDEX IND_T_ID ON T(ID);
索引已创建。
SQL> ALTER INDEX IND_T_NAME REBUILD ONLINE;
索引已更改。

    会话2：

SQL2> CONN YANGTK/YANGTK@YTK102已连接。
SQL2> BEGIN
2 FOR I IN 1..100000 LOOP
3 UPDATE T SET ID = ID WHERE ID = 1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。

    会话3：

SQL3> CONN YANGTK/YANGTK@YTK102已连接。
SQL3> SET SERVEROUT ON
SQL3> DECLARE
2 V_NUM NUMBER;
3 V_TOTAL NUMBER DEFAULT 0;
4 BEGIN
5 FOR I IN 1..1000 LOOP
6 SELECT SUM(BLOCK) INTO V_NUM FROM V$LOCK WHERE SID = 144;
7 V_TOTAL := V_TOTAL + NVL(V_NUM, 0);
8 END LOOP;
9 DBMS_OUTPUT.PUT_LINE(V_TOTAL);
10 END;
11 /
6
PL/SQL 过程已成功完成。
SQL3> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

    从上面的结果不难看出，11g以前版本在ONLINE REBUILD的时候会对DML操作短暂的阻塞，而11g彻底消除了ONLINE REBUILD对DML的影响。