oracle online 和非online创建index

最近线上系统有个需求，就是一张大表需要创建索引，而且这张表有频繁的DML，开发人员认为，就是一条create index语句，没什么可担心的，直接执行就行，其实不然，首先任何线上的操作，哪怕你认为很简单的操作，都要心存敬畏，不能想当然，要有理论支撑，其次，对于create index，尤其是增删改频繁的大表，是有讲究的，否则很可能无法执行成功，甚至对性能造成影响。

 

之前写了一篇《创建索引的两种方式比对》，介绍了创建索引的两种方法，即online和非online，当时给出的结论，如下所示，

(1) online和非online方式创建索引，效果相同。 
(2) online方式创建索引，由于使用了一张临时表，以ROW SHARE锁表，不会阻塞原表DML的语句，非online方式创建索引，则会以SHARE NOWAIT锁表，阻塞原表DML语句。 
(3) 由于online方式创建索引，Oracle执行工作复杂，因此比非online方式创建索引用时要久。 
(4) 一句话“不能什么便宜均占着”，要么选择可以快速创建索引的非online方式但创建期间会锁表阻塞DML语句，要么选择不会阻塞原表DML语句的online方式创建索引但用时较久。从实际来看，我理解，若小表选择任何一种均可，大表，尤其是生产系统，找不着非高峰时间，选择online更合理一些，若不关注是否影响DML操作，则两种方式均可以了。

 

我们再做下实验，创建测试表，

SQL> create table tbl(id number, name varchar2(1));
Table created.

 

场景一：非online创建索引

session 1：插入一条数据，但不提交，

SQL> select sid from v$mystat where rownum = 1;
 SID
----------
  1

SQL> insert into tbl values(2, 'b');        
1 row created.

此时会出现一个表级锁，和一个行级锁，
SQL> select sid, type, id1, lmode, request, block from v$lock where sid in (1, 40)
   and type in ('TX', 'TM'); 
SID    TYPE    ID1    LMODE    REQUEST   BLOCK
----- -------- ---------- ---------- ---------- ----------
  1    TM    14338     3        0      0
  1    TX    131079    6        0      0

SQL> select object_name from dba_objects 
   where object_id = 14338;
OBJECT_NAME
--------------------------------------------------------------------------------
TBL

 

 

关于锁的模式，

0：none 
1：null 空 
2：Row-S 行共享(RS)：共享表锁 
3：Row-X 行专用(RX)：用于行的修改 
4：Share 共享锁(S)：阻止其他DML操作 
5：S/Row-X 共享行专用(SRX)：阻止其他事务操作 
6：exclusive 专用(X)：独立访问使用 

 

此时session 2创建索引，注意未加online关键字，提示ORA-00054错误，表示资源繁忙，

SQL> select sid from v$mystat where rownum = 1;     
 SID
--------
  40

SQL> create index idx_tbl on tbl(id);
create index idx_tbl on tbl(id)
                       *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

从现象上看，说明使用非online创建索引，如果表上存在未提交事务，则无法执行，提示错误ORA-00054，直到所有事务已经提交。

 

如果上述操作反向来，即先create index创建索引，创建过程中，执行DML操作，此时DML操作会被hang，相当于创建索引阻碍了DML。

 

从锁的情况看，此时create index的session会请求mode=4的TM锁，而insert未提交的session未释放锁，block=1，LMODE为2、3、4不影响DML(insert,delete,update,select)操作, 但DDL(alter、drop等)操作会提示ORA-00054错误，这和现象一致，

 SID   TY     ID1     LMODE   REQUEST   BLOCK
------ ------- ---------- --------- --------- ---------
 40    TM    21504     0      4      0
  1    TM    21504     3      0      1
  1    TX    524310    6      0      0

 

另外，我们从10046的trace(BISAL_ora_52965.trc，大小117K)，发现非online方式创建索引，一共SELECT数据字典共计23次，UPDATE了2次，INSERT了4次，总共对数据字典，执行31次操作，才完成了创建索引的工作，

grep 'select ' BISAL_ora_52965.trc | wc -l
23

grep 'update ' BISAL_ora_52965.trc | wc -l
2

grep 'delete ' BISAL_ora_52965.trc | wc -l
0

grep 'insert ' BISAL_ora_52965.trc | wc -l
4

 

其中所有的DML操作如下所示，使用SHARE MODE NOWAIT方式，LOCK表，对obj$、seg$、icol、ind$等数据字典，进行增删改操作，

LOCK TABLE 'TBL' IN SHARE MODE  NOWAIT
insert into obj$
insert into seg$
update seg$
insert into icol$
insert into ind$
update obj$

 

场景二：online创建索引

同样，session 1插入一条记录，但不提交，session 2执行create index ... online操作，不会像场景一抛出错误，而是等待状态，直到session 1提交，

SQL> create index idx_tbl on tbl(id) online;

 

从现象上看，说明使用online来创建索引，如果表上存在未提交事务，则无法执行，不会报错，但会等待，直到所有事务已经提交。

 

如果上述操作反向来，先执行了create index ... online，创建过程中，执行DML操作，此时这些DML能执行，不会影响。

 

等待状态期间，检索锁的信息，除了ID1=14338对应TBL表存在TM锁的持有和等待，还对ID1=14341对应SYS_JOURNAL_14340表有TM锁，同时这两个会话，对TBL表的行，存在TX锁的持有和等待，另外一个TX就是对SYS_JOURNAL_14340的行持锁，

SQL> select sid, type, id1, lmode, request, block from v$lock where sid in (1, 40)
   and type in ('TX', 'TM');
 SID   TYPE   ID1    LMODE    REQUEST   BLOCK
------ ------- -------- ---------- ---------- ----------
 40    TX   131072    0        4      0
 40    TM   14338     2        0      0
 40    TM   14341     4        0      0
  1    TM   14338     3        0      0
 40    TX   327682    6        0      0
  1    TX   131072    6        0      1

SQL> select object_name from dba_objects where object_id = 14341;
OBJECT_NAME
--------------------------------------------------------------------------------
SYS_JOURNAL_14340

SQL> select object_name from dba_objects where object_id = 14338;
OBJECT_NAME
--------------------------------------------------------------------------------
TBL

 

注：SYS_JOURNAL_14340相当于中间表，之所以online不影响正常DML，就是因为他用了中间表，类似物化视图的机制，对中间表进行的操作，

 

从10046的trace(BISAL_ora_53117.trc，大小371K)来看，发现使用online方式创建索引，一共SELECT数据字典共计54次，INSERT了12次，UPDATE了5次，DELETE了46次，总共对数据字典，执行117次操作，才完成了创建索引的工作，相比非online，onilne方式创建索引，做了更多的操作，

grep 'select ' BISAL_ora_53117.trc | wc -l
54

grep 'insert ' BISAL_ora_53117.trc | wc -l
12

grep 'update ' BISAL_ora_53117.trc | wc -l
5

grep 'delete ' BISAL_ora_53117.trc | wc -l
46

 

其中所有DML操作的调用栈，如下所示，第一步操作是LOCK TABLE，模式ROW SHARE，接下来创建了中间表，并创建唯一索引，以及各种DML数据字典的操作，

LOCK TABLE 'TBL' IN ROW SHARE MODE
create table 'BISAL'.'SYS
update obj$
update con$
CREATE UNIQUE INDEX 'BISAL'.'SYS_IOT_TOP_14344' on 'BISAL'.'SYS_JOURNAL_14343'('C0','RID') INDEX ONLY TOPLEVEL TABLESPACE 'TB' STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOPARALLEL
insert into seg$
insert into icol$
insert into ind$
insert into tab$
insert into col$
insert into ccol$
insert into cdef$
insert into icol$
insert into ind_online$
update seg$
delete from ind_stats$ where obj#=:1
update ind$
UPDATE ind_online$ SET flags=:2 WHERE obj#=:1
insert into sys.wri$_optstat_ind_history
delete from ind_stats$ where obj#=:1
delete from object_usage
delete from sys.cache_stats_1$
delete com$
delete from hist_head$
delete from compression$
DELETE FROM sys.sumdelta$
DELETE FROM sys.sumpartlog$
DELETE FROM sys.snap_loadertime$
BEGIN aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
delete from ind_online$
delete from ind_stats$
delete from idl_ub1$
delete from idl_ub2$
delete from idl_sb4$
delete from ncomp_dll$
delete from idl_char$
delete from idl_ub2$
delete from idl_sb4$
delete from ncomp_dll$
delete from idl_ub1$
delete from idl_char$
delete from idl_ub2$
delete from idl_sb4$
delete from ncomp_dll$
delete from objauth$
delete from col$
delete from icol$
delete from icoldep$
delete from jijoin$
delete from jirefreshsql$
delete from ccol$
delete from ind$
delete from cdef$
delete ecol$  -- 语句缺少from关键字
delete from tab$
delete coltype$  -- 语句缺少from关键字
delete from subcoltype$
delete ntab$
delete lob$  -- 语句缺少from关键字
delete from opqtype$
delete from cdef$
delete from objauth$
delete from obj$
update seg$
delete from con$
delete from seg$

 

总结来讲，

1. 非online方式创建索引，不能存在正在执行的DML，否则create index就会抛出ORA-00054。如果非online创建索引过程中，有正在执行的DML，则这些操作会hang，直到create index执行完成。换句话说，对于频繁DML的表，因为并发操作，有可能一直无法创建索引，也有可能创建索引的过程中，影响其他DML操作。

2. online方式创建的索引，不能存在正在执行的DML，否则create index ... online不会抛出错误，但会一直等待，直到所有DML完成。如果使用online创建索引过程中，有正在执行的DML，则不会影响。换句话说，对于频繁DML的表，因为并发操作，有可能一直无法创建索引，但是在创建索引的过程中，不会影响其他DML操作。

3. 无论online还是非online，如果创建的时候，有其他的DML操作，则都不会成功，不同的是，online是等待，非online是抛出错误。

4. 对于非online，创建索引过程中，会阻碍其他的DML，对于online，创建过程中，不会阻碍其他DML。

5. 相对来说，online比非online对现有DML的影响较小，但是二者，都存在无法创建的可能。

6. 针对无法创建的可能，一种方法，就是暂停业务，暂停所有DML操作，这样可以顺利地创建完成，此时选择非online，相比使用online，可以更快创建

转：http://www.360doc.com/content/18/0920/08/13123233_788134288.shtml