ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle11新特性——在线操作功能增强(七)

Oracle11新特性——在线操作功能增强(七)

原创 Linux操作系统 作者:yangtingkun 时间:2007-10-08 00:00:00 0 删除 编辑

打算写一系列的文章介绍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操作。为了避免COMMITDELETE操作间隔时间太大,在文本编辑器中,将两个命令编辑好,拷贝到剪贴板,然后一起拷贝到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再次执行COMMITDELETE操作,发现会话2REBUILD操作已经完成。

索引已更改。

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 行。

这是会话2REBUILD操作完成:

索引已更改。

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 过程已成功完成。

确保会话1REBUILD 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 REBUILDDML的影响。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69451/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 清除auto.exe病毒
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10365839