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 -- -------- 148 SQL > SELECT SID FROM V$SESSION WHERE USERNAME = ' YANGTK ' ; SID -- -------- 144 148 SQL > SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN ( 144 , 148 ); SID TY ID1 CTIME BLOCK -- -------- -- ---------- ---------- ---------- 144 DL 52896 360 0 144 DL 52896 360 0 148 TM 52896 417 1 144 TM 52896 360 0 144 TM 52899 359 0 148 TX 65575 417 0 已选择6行。 SQL > COL EVENT FORMAT A40 SQL > COL P1TEXT FORMAT A20 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 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 0 144 DL 52896 828 0 148 TM 52896 26 1 144 TM 52896 26 0 144 TM 52899 827 0 144 TS 7 26 0 144 TX 262169 26 0 148 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 -- -------- 131 SQL > SELECT SID FROM V$SESSION WHERE USERNAME = ' YANGTK ' ; SID -- -------- 131 149 SQL > SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN ( 131 , 149 ); SID TY ID1 CTIME BLOCK -- -------- -- ---------- ---------- ---------- 149 AE 99 50 0 149 OD 70713 47 0 131 AE 99 78 0 149 DL 70712 47 0 149 DL 70712 47 0 149 OD 70712 47 0 149 TX 589851 47 0 149 TM 70712 47 0 149 TM 70715 47 0 131 TM 70712 65 0 149 TX 262171 47 0 131 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 0 149 OD 70713 107 0 131 AE 99 138 0 149 DL 70712 107 0 149 DL 70712 107 0 149 OD 70712 107 0 149 TX 196637 10 0 149 TM 70712 107 0 149 TM 70715 107 0 131 TM 70712 10 0 149 TX 262171 107 0 131 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的影响。
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
