合 Oracle新建或重建索引过程中的锁信息
- 简介
- 实验
- 创建或重建索引会阻塞DML操作
- Oracle 11g下ONLINE选项不会堵塞DML操作
- Oracle 10g下ONLINE选项会堵塞DML操作
- 实验10.2.0.1.0
- 实验11.2.0.3.0
- 利用10704和10046跟踪锁
- 10g
- create index
- alter index ... rebuild
- create index ... online
- alter index ... rebuild online
- 11g
- create index
- alter index ... rebuild
- create index ... online
- alter index ... rebuild online
- 实验SQL
- 查询锁用到的SQL语句
- 扩展
简介
可以利用10704和10046事件跟踪新建或重建索引过程中的锁信息,命令为:
1 2 | alter session set events '10704 trace name context forever,level 10'; alter session set events '10046 trace name context forever,level 12'; |
新建或重建索引的锁信息如下图所示:
不带ONLINE的新建或重建索引的SQL语句获取的是4级TM锁,它会阻塞任何DML操作。
在Oracle 10g中,带ONLINE的新建或重建索引的SQL语句在开始和结束的时候获取的是4级TM锁,而在读取表数据的过程中获取的是2级TM锁,所以,在Oracle 10g中,即使加上ONLINE也会阻塞其它会话的DML操作。
在Oracle 11g中,带ONLINE的新建或重建索引的SQL语句在整个执行过程中获取的是2级TM锁,并不会阻塞其它会话的DML操作,但是在创建或重建索引的过程中,其它的会话产生的事务会阻塞索引的创建或重建操作,所以必须结束其它会话的事务才能让创建或重建索引的操作完成。所以应该避免在业务高峰期创建索引。
在Oracle 11g带ONLINE的新建或重建索引的情况下:
① 过程中会持有OD(ONLINE DDL)、DL(Direct Loader Index Creation)两种类型的锁,在Oracle 10g下只有DL锁没有OD锁。
② 表级锁TM的持有模式为2级RS(Row Share)与3级RX(Row Exclusive)类型的锁互相兼容,因此不会在表级发生阻塞。
③ 阻塞发生在行级锁申请阶段,即请求的4级S(Share)类型的锁与执行DML的会话已经持有的6级X(Exclusive)锁之间存在不兼容的情况;相比非ONLINE方式的表级锁,锁的粒度上更加细化,副作用更小。
④ 新增以“SYSJOURNAL”为前缀的IOT表,记录与索引创建动作同时进行的其它DML操作修改过的记录,等到索引创建完成前将IOT表里的记录合并至索引中并删除IOT表。
实验
创建或重建索引会阻塞DML操作
版本:11.2.0.3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | 首先建表T_INDEX_161113并插入很多数据 SYS@oratest S1> CREATE TABLE T_INDEX_161113 AS SELECT * FROM DBA_OBJECTS; Table created. SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113; 75349 rows created. SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113; 150698 rows created. SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113; 301396 rows created. SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113; 602792 rows created. SYS@oratest S1> COMMIT; Commit complete. 接着再在该表上创建一个索引 SYS@oratest S1> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME); 在创建索引的同时,在会话2上插入一条记录: SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1; ====>>>>> 产生了阻塞 在创建索引的同时,查询相关锁的信息: SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT OBJECT_NAME FROM DBA_OBJECTS D WHERE D.OBJECT_ID = A.P2) OBJECT_NAME 11 FROM GV$SESSION A 12 WHERE A.SID=141; SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE OBJECT_NAME ---------- ---------------- ----------------------- ---------- ---------- ---------- ---- ---------- ---------------- 142 21 enq: TM - contention 1414332419 77629 0 TM 3 T_INDEX_161113 SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (21,142) AND A.TYPE IN ('TX','TM'); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F44001842E0 00007F4400184340 142 TM 77629 0 0 3 2 0 00007F44001842E0 00007F4400184340 21 TM 77629 0 4 0 3 1 00007F44001842E0 00007F4400184340 21 TM 18 0 3 0 3 0 0000000076273C58 0000000076273CD0 21 TX 65567 846 6 0 3 0 SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (21, 142); SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- -------------------- ------------- ------------- ------------ -------------------- 142 SYS T_INDEX_161113 None Row-X (SX) 2 Not Blocking 21 SYS T_INDEX_161113 Share None 3 Blocking 21 SYS OBJ$ Row-X (SX) None 3 Not Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (18, 77629); OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ---------- ---------------------- ---------- ------------------- SYS T_INDEX_161113 77629 TABLE SYS OBJ$ 18 TABLE SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (21, 142) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE'); SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU ---------- --------- ----------------- ----------- --------- --------- 21 SYS 73 Share None 21 SYS IDX_TEST_LHR Index Exclusive None |
可以发现在会话1中,在创建索引的过程中会生成2个TM锁,锁类别分别为4和3,根据查询结果发现lmode=4的object_id为77629的对象对应的是T_INDEX_161113这个表,对应的是TM的S锁。另一个lmode=3的锁对象是系统基表OBJ$表,允许其它会话对该表执行DML操作。可以得出这样一个结论:当对表进行创建索引操作时,会伴随出现LMODE=4的S锁。根据锁的兼容模式可以发现S锁和任何DML操作都是冲突的!所以,尤其是在生产上,当在一个很大的表上进行索引创建的时候,任何对该表的DML操作都会被夯住!!!
从DBA_DDL_LOCKS视图可以看到,建索引的同时有6级排它DDL锁。
Oracle 11g下ONLINE选项不会堵塞DML操作
版本:11.2.0.3
接着上面的实验,重建索引的时候加上ONLINE,由于会话断开了,重新开2个会话,会话1为22,会话2为142:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | SYS@oratest S1> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE; 在创建索引的同时,在会话2上插入一条记录: SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1; 1 row created. ====>>>>> 加上ONLINE后无阻塞产生 在创建索引的同时,查询相关锁的信息: SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22,141) AND A.TYPE IN ('TX','TM'); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774D9C08 00000000774D9C60 22 TX 327688 1122 0 4 761 0 00007FD883B38350 00007FD883B383B0 22 TM 77629 0 2 0 768 0 00007FD883B38350 00007FD883B383B0 22 TM 77643 0 4 0 767 0 0000000076274668 00000000762746E0 22 TX 196612 1119 6 0 768 0 0000000076236E38 0000000076236EB0 141 TX 327688 1122 6 0 763 1 00007FD883B38350 00007FD883B383B0 141 TM 77629 0 3 0 763 0 6 rows selected. SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (22,141); SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ----------- -------------------- ------------- ------------- ------------ ---------------------------------------- 141 SYS T_INDEX_161113 Row-X (SX) None 625 Not Blocking 22 SYS T_INDEX_161113 Row-S (SS) None 630 Not Blocking 22 SYS SYS_JOURNAL_77631 Share None 629 Not Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (77629, 77643); OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ---------- ------------------------- ---------- ------------------- SYS SYS_JOURNAL_77631 77643 TABLE SYS T_INDEX_161113 77629 TABLE SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (22,141) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE'); no rows selected SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (141,22); SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- ----------------------------------------------------------------------- 22 141 enq: TX - row lock contention 1415053316 327688 1122 TX 4 ALTER INDEX IDX_TEST_LHR REBUILD ONLINE 141 SQL*Net message from client 1650815232 1 0 be 28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1 |