本文共 9854 字，大约阅读时间需要 32 分钟。
一直以来经验告诉我们，创建索引需要加online？ 为啥？
总结：
1.创建索引不加online, 表有dml操作未结束事务，创建索引失败；
2.创建索引不加online,在建索引的过程中，业务对表有dml操作，业务将被阻塞tm锁无法申请这个资源【大表建索引影响更大】；
3.创建索引使用online,表有dml操作未结束事务，创建索引无法成功,会话处于tx行锁争用，表不存在相关事务后，建索引自动成功；
4.创建索引使用online,在建索引的过程中,业务对表dml操作,业务dml操作将顺利执行，直到表无任何事务后，索引建成功；
5.创建索引使用Online,在建索引的过程中，使用ctrl+c或其他原因导致索引创建失败，数据字典将残留对象需要调用存储过程进行清理。
创建索引测试锁1.对表dml操作,事务未结束session1SQL> delete d1 where rownum=1;1 row deleted.session2SQL> create index c_id on d1(object_id);create index c_id on d1(object_id) *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired分析create index 锁session2SQL> alter session set ddl_lock_timeout=600;SQL> create index c_id on d1(object_id);hang session3SQL> delete d1 where object_id>9000;hangSQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where status='ACTIVE' and username is not null;INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------ 2 1 4711 SYS ACTIVE d2 2ymxxw3mapxd9 PX Deq: Execution Msg 0 0 2 19 12269 SYS ACTIVE d2 2k397xzajspfu enq: TM - contention 290 54 2 30 424 SYS ACTIVE d2 7ddkb1x0zhpp5 enq: TM - contention 49 22 2 43 42485 SYS ACTIVE d2 2ymxxw3mapxd9 PX Deq: Execute Reply 16 0 2 402 5717 SYS ACTIVE d2 OFS idle 1130 1130 1 3 30306 SYS ACTIVE d2 2ymxxw3mapxd9 PX Deq: Execution Msg 0 0 1 403 22969 SYS ACTIVE d1 OFS idle 1494 14957 rows selected.SQL> select object_id from dba_objects where owner='SYS' and object_name='D1'; OBJECT_ID---------- 73029SQL> select inst_id,sid,type,id1,lmode,request,block from gv$lock where type in('TX','TM');INST_ID SID TY ID1 LMODE REQUEST BLOCK------- ------ -- ---------- ---------- ---------- ---------- 31 TM 73029 3 0 1 31 TX 524300 6 0 0 29 TM 73029 0 3 0 28 TM 73029 0 4 0 Session 1 SQL> commit; Session 2 OK index create complete Session 3 OK delete complete. SQL> select INST_ID,sid,serial#,USERNAME,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET,BLOCKING_INSTANCE,BLOCKING_SESSION,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION from gv$session where SID IN(19,30) and inst_id=2;INST_ID SID SERIAL# USERN SQL_ID EVENT s LAST_CALL_ET BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION------- ------ ------- ----- -------------------- -------------------- ------- ------------ ----------------- ---------------- ----------------------- ---------------------- 2 19 12269 SYS 2k397xzajspfu enq: TM - contention 954 91 1 17 1 17 2 30 424 SYS 7ddkb1x0zhpp5 enq: TM - contention 713 88SQL>SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='7ddkb1x0zhpp5';SQL_TEXT----------------------------------------delete d1 where object_id>9000SQL>SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='2k397xzajspfu';这点很奇怪,说明事物未结束,索引将无法创建成功，并且会阻塞后续的dml操作。 但是申请表锁的级别无法得到。上述出现锁是由于对创建索引的session ddl锁申请资源参数进行调整,默认是0，并不会出现创建索引不成功，反而阻塞后续业务DML操作。SQL> alter session set ddl_lock_timeout=0;2.使用online参数session1SQL> delete d1 where rownum=1;1 row deleted.session2SQL> create index c_id on d1(object_id) online;session3SQL> delete d1 where object_id>9000;63382 rows deleted. --可以发现使用online创建索引，但是在索引创建session并未结束时, dml不影响！！！SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where status='ACTIVE' and username is not null;INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------ 2 19 12269 SYS ACTIVE d2 0g5b4q13v6gp4 enq: TX - row lock contention 1264 67SQL>SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='0g5b4q13v6gp4';SQL_TEXT-----------------------------------------create index c_id on d1(object_id) onlineSQL> select INST_ID,sid,serial#,USERNAME,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET,BLOCKING_INSTANCE,BLOCKING_SESSION,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION from gv$session where SID IN(19) and inst_id=2;INST_ID SID SERIAL# USERNAME SQL_ID EVENT s LAST_CALL_ET BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION------- ------ ------- -------- --------------- ------------------------------ ------- ------------ ----------------- ---------------- ----------------------- ---------------------- 2 19 12269 SYS 0g5b4q13v6gp4 enq: TX - row lock contention 1311 113 1 17 1 17也就是说使用online创建索引,存在历史记录的dml操作，将导致创建索引的session遭遇行锁等待！SQL> select inst_id,sid,serial#,username,status from gv$session where inst_id=1 and sid=17;INST_ID SID SERIAL# USERNAME STATUS------- ------ ------- -------------------- ---------- 1 17 41003 SYS INACTIVE SQL> alter system kill session '17,41003,@1' immediate;System altered.Session1 dml回滚！SQL> r 1* delete d1 where rownum=1delete d1 where rownum=1 *ERROR at line 1:ORA-03135: connection lost contactProcess ID: 37874Session ID: 17 Serial number: 41003SQL> select INST_ID,sid,serial#,USERNAME,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET,BLOCKING_INSTANCE,BLOCKING_SESSION,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION from gv$session where SID IN(19) and inst_id=2;INST_ID SID SERIAL# USERNAME SQL_ID EVENT s LAST_CALL_ET BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION------- ------ ------- -------------------- -------------------- ------------------------------ ------- ------------ ----------------- ---------------- ----------------------- ---------------------- 2 19 12269 SYS 0g5b4q13v6gp4 enq: TX - row lock contention 1637 439 2 30 2 33Session3 SQL> roll;Rollback complete.Session2SQL> create index c_id on d1(object_id) online;Index created. SQL> set autotrace onSQL> select count(*) from d1 where object_id=2;--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | ||* 2 | INDEX RANGE SCAN| C_ID | 1 | 13 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------问题 创建索引的过程中,是否会阻塞后续的dml操作Session1SQL> set timing onSQL> create index c_id on d1(object_id);Index created.Elapsed: 00:00:03.12Session2SQL> delete d1 where object_id>9000;Session3SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where status='ACTIVE' and username is not nullINST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------ 2 19 12269 SYS ACTIVE d2 7ddkb1x0zhpp5 enq: TM - contention 2316 2 --存在阻塞 2 37 3783 SYS ACTIVE d2 2ymxxw3mapxd9 PX Deq: Execution Msg 0 0 2 43 42485 SYS ACTIVE d2 2ymxxw3mapxd9 PX Deq: Execute Reply 2042 0 2 402 5717 SYS ACTIVE d2 OFS idle 3156 3156 1 23 53420 SYS ACTIVE d2 2ymxxw3mapxd9 PX Deq: Execution Msg 0 0 1 403 22969 SYS ACTIVE d1 OFS idle 3520 3520 1 468 60995 SYS ACTIVE d1 2k397xzajspfu db file single write 322 37 rows selected.SQL>SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='7ddkb1x0zhpp5';SQL_TEXT--------------------------------delete d1 where object_id>9000
转载地址：http://rvlyz.baihongyu.com/