ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CREATE INDEX ......ONLINE分析

CREATE INDEX ......ONLINE分析

Linux操作系统 作者:kunlunzhiying 时间:2018-02-06 16:08:48 0 删除 编辑

这里我们讨论一下CREATE INDEX......ONLINE在线创建索引的情况:
数据库版本:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

创建模拟用户和数据表:
SQL> create user xiaoyang identified by xiaoyang default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to xiaoyang;

Grant succeeded.

SQL> grant select on "SYS"."V_$MYSTAT" to xiaoyang;

Grant succeeded.

SQL> connect xiaoyang/xiaoyang
Connected.

SQL> create table test(id number primary key,
  2  name varchar2(20));

Table created.

SQL> insert into test values (111,'aaa');

1 row created.

SQL> commit;

Commit complete.

会话1:
SQL> connect xiaoyang/xiaoyang
Connected.
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       136

SQL>
SQL> insert into test values (222,'bbb');

1 row created.
会话1的SID为136,向XIAOYANG.TEST表插入一条数据,但未提交。

会话2:
sqlplus xiaoyang/xiaoyang
......
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       147

SQL> create index idx_test_name on test(name);
create index idx_test_name on test(name)
                              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

不加ONLINE关键字直接报错。
SQL> create index idx_test_name on test(name) online;
加上ONLINE关键字之后发现执行该语句卡住了。

会话3:
sqlplus / as sysdba
......
SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

       SID BLOCKING_SESSION
---------- ----------------
       147              136
发现是136会话阻塞了147会话。

SQL> SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN(147,136) ORDER BY SID;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       136 TM     115640          0          3          0          1
       136 TX     393262      17362          6          0          0
       147 TM     115643          0          4          0          0
       147 TM     115640          0          2          4          0
       147 DL     115640          0          3          0          0
       147 DL     115640          0          3          0          0

6 rows selected.

查询锁发现,147会话对应的TM锁有两条记录,在请求模式为4的锁时一条是成功的,另外一条并未成功,只获得了模式为2的锁。因为是136会话阻塞了147会话,所以说136获得的模式为3的锁和模式为4的锁并不兼容。

  • 0 - none

  • 1 - null (NULL)

  • 2 - row-S (SS)

  • 3 - row-X (SX)

  • 4 - share (S)

  • 5 - S/Row-X (SSX)

  • 6 - exclusive (X)

SQL> SELECT owner,object_id,object_name,object_type FROM DBA_OBJECTS WHERE OBJECT_ID in (115640,115643);

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ---------- ------------------------------------------------ -------------------
XIAOYANG       115640 TEST                                                TABLE
XIAOYANG       115643 SYS_JOURNAL_115642             TABLE
115640是TEST表,而获取模式为4的锁成功的表为 SYS_JOURNAL_115642,它应该是执行ONLINE创建索引的中间表。

如果这个时候又有新的DML操作产生:
会话4:
sqlplus xiaoyang/xiaoyang
......
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       148

SQL> insert into test values(333,'ccc');
该事务同样被阻塞。

会话3:
SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

       SID BLOCKING_SESSION
---------- ----------------
       147              136
       148              147
发现是会话147阻塞了会话148。
SQL> SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN(147,136,148) ORDER BY SID;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       136 TM     115640          0          3          0          1
       136 TX     393262      17362          6          0          0
       147 TM     115643          0          4          0          0
       147 DL     115640          0          3          0          0
       147 DL     115640          0          3          0          0
       147 TM     115640          0          2          4          0
       148 TM     115640          0          0          3          0

会话148在执行DML语句前请求表模式为3的锁失败。这个应该是连锁反应造成的。

将会话1提交。
会话4执行成功,但是会话3依然被阻塞。

SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

       SID BLOCKING_SESSION
---------- ----------------
       147              148
这个时候显示会话2(sid=147)是被会话4(sid=148)阻塞。
提交会话4,会话2 online创建索引成功!所有的锁消失。


SQL> SELECT owner,object_id,object_name,object_type FROM DBA_OBJECTS WHERE OBJECT_ID in (115640,115643);

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ---------- ------------------------------ -------------------
XIAOYANG       115640 TEST                           TABLE

ONLINE创建索引时产生的中间表也消失了。

请注意:
执行ALTER INDEX ..... REBUILD ONLINE;同样会出现类似CREATE INDEX...... ONLINE的问题。
SQL> alter index idx_test_name rebuild;
alter index idx_test_name rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> alter index idx_test_name rebuild online;
卡住......

--end--

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

请登录后发表评论 登录
全部评论
IT搬砖

注册时间:2014-07-28

  • 博文量
    665
  • 访问量
    344657