Oracle11新特性——線上操作功能增強（七）

打算寫一系列的文章介紹11g的新特性和變化。

11g中線上處理功能得到了很大增強，其中包括線上修改表結構，線上建立或重建索引，建立不可見索引，表增加非空欄位，線上DDL以及物件依賴性細化等。

這一篇介紹Oracle11g線上重建索引功能增強。

Oracle11新特性——線上操作功能增強（一）：http://yangtingkun.itpub.net/post/468/400430

Oracle11新特性——線上操作功能增強（二）：http://yangtingkun.itpub.net/post/468/401293

Oracle11新特性——線上操作功能增強（三）：http://yangtingkun.itpub.net/post/468/401641

Oracle11新特性——線上操作功能增強（四）：http://yangtingkun.itpub.net/post/468/402220

Oracle11新特性——線上操作功能增強（五）：http://yangtingkun.itpub.net/post/468/402802

Oracle11新特性——線上操作功能增強（六）：http://yangtingkun.itpub.net/post/468/402977

11加強了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的影響。