一直以来经验告诉我们，创建索引需要加online？ 为啥？
总结：
1.创建索引不加online, 表有dml操作未结束事务，创建索引失败；
2.创建索引不加online,在建索引的过程中，业务对表有dml操作，业务将被阻塞tm锁无法申请这个资源【大表建索引影响更大】；
3.创建索引使用online,表有dml操作未结束事务，创建索引无法成功,会话处于tx行锁争用，表不存在相关事务后，建索引自动成功；
4.创建索引使用online,在建索引的过程中,业务对表dml操作,业务dml操作将顺利执行，直到表无任何事务后，索引建成功；
5.创建索引使用Online,在建索引的过程中，使用ctrl+c或其他原因导致索引创建失败，数据字典将残留对象需要调用存储过程进行清理。
创建索引测试锁 1.对表dml操作,事务未结束 session1 SQL> delete d1 where rownum=1; 1 row deleted. session2 SQL> 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 锁 session2 SQL> alter session set ddl_lock_timeout=600; SQL> create index c_id on d1(object_id); hang session3 SQL> delete d1 where object_id>9000; hang 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 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 1495 7 rows selected. SQL> select object_id from dba_objects where owner='SYS' and object_name='D1'; OBJECT_ID ---------- 73029 SQL> 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 88 SQL>SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='7ddkb1x0zhpp5'; SQL_TEXT ---------------------------------------- delete d1 where object_id>9000 SQL>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参数 session1 SQL> delete d1 where rownum=1; 1 row deleted. session2 SQL> create index c_id on d1(object_id) online; session3 SQL> 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 67 SQL>SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='0g5b4q13v6gp4'; SQL_TEXT ----------------------------------------- create index c_id on d1(object_id) online 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) 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=1 delete d1 where rownum=1 * ERROR at line 1: ORA-03135: connection lost contact Process ID: 37874 Session ID: 17 Serial number: 41003 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) 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 33 Session3 SQL> roll; Rollback complete. Session2 SQL> create index c_id on d1(object_id) online; Index created. SQL> set autotrace on SQL> 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操作 Session1 SQL> set timing on SQL> create index c_id on d1(object_id); Index created. Elapsed: 00:00:03.12 Session2 SQL> delete d1 where object_id>9000; Session3 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 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 3 7 rows selected. SQL>SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='7ddkb1x0zhpp5'; SQL_TEXT -------------------------------- delete d1 where object_id>9000