本文主要是学习10074事件，以及看看truncate table，create index，create index online时产生的锁
一、测试oracle9201环境下 create index online
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 17:10:34 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> conn test/test
已连接。
SQL> alter session set events '10704 trace name context forever, level 12';
会话已更改。
SQL> create index a on T (owner, object_name) online;
索引已创建。
SQL> alter session set events '10704 trace name context off';
会话已更改。
SQL>
Dump file d:\oracle\admin\ora9\udump\ora9_ora_3936.trc
Wed Mar 05 17:11:28 2008
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Instance name: ora9
Redo thread mounted by this instance: 1
Oracle process number: 12
Windows thread id: 3936, image: ORACLE.EXE
*** SESSION ID:(9.14) 2008-03-05 17:11:28.000
*** 2008-03-05 17:11:28.000
ksqcmi: TM,7663,0 mode=4 timeout=21474836
ksqcmi: returns 0
*** 2008-03-05 17:11:28.000
ksqcmi: TM,7663,0 mode=2 timeout=21474836
ksqcmi: returns 0
*** 2008-03-05 17:11:28.000
ksqcmi: TM,7663,0 mode=4 timeout=21474836
ksqcmi: returns 0
*** 2008-03-05 17:11:28.000
ksqcmi: TM,7667,0 mode=6 timeout=0
ksqcmi: returns 0
二、oracle 10201
测试truncate产生的锁
C:\Documents and Settings\olivenan>sqlplus test/test
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:17:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set events '10704 trace name context forever, level 12';
会话已更改。
SQL> truncate table t;
表已截掉。
SQL> alter session set events '10704 trace name context off';
会话已更改。
SQL> select object_id from user_objects where object_name ='T';
OBJECT_ID
----------
51833
SQL> select to_char(51833,'xxxxx')from dual;
TO_CHA
------
ca79
SQL> @e:\gettrcname
13 ;
D.VALUE||'/'||LOWER(RTRIM(I.INSTANCE,CHR(0)))||'_ORA_'||P.SPID||'.TRC'
-------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST\UDUMP/test_ora_1620.trc
SQL>
在trc文件中查找ca79，发现TM-0000ca79-00000000中包含ca79，我想应该不是巧合吧，具体解释还没有找到。
对ca79的锁是mode=6，也就是我们常说的6 - exclusive (X)
*** 2008-03-05 18:18:02.261
ksqgtl *** TM-0000ca79-00000000 mode=6 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x333ACD74, ktcdix=2147483647, topxcb=0x333ACD74
ktcipt(topxcb)=0x0
*** 2008-03-05 18:18:02.276
*** 2008-03-05 18:18:02.728
ksqrcl: TM,ca79,0
ksqrcl: returns 0
三、测试create index产生的锁
C:\Documents and Settings\olivenan>sqlplus test/test
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:28:52 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set events '10704 trace name context forever, level 12';
会话已更改。
SQL> create index a_t on T (owner, object_name);
索引已创建。
SQL> alter session set events '10704 trace name context off';
会话已更改。
SQL> @e:\gettrcname
13 ;
D.VALUE||'/'||LOWER(RTRIM(I.INSTANCE,CHR(0)))||'_ORA_'||P.SPID||'.TRC'
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST\UDUMP/test_ora_2640.trc
SQL>
DML锁：TM mode=4 share (S)
4 - share (S)
mode=4的会阻塞mode=3的请求，所以create index会阻塞dml操作
*** 2008-03-05 18:30:19.790
ksqgtl *** DL-0000ca79-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl: xcb=0x333ACD74, ktcdix=2147483647, topxcb=0x333ACD74
ktcipt(topxcb)=0x0
*** 2008-03-05 18:30:19.821
ksqcmi: DL,ca79,0 mode=3 timeout=0
ksqcmi: returns 0
ksqgtl: RETURNS 0
*** 2008-03-05 18:30:19.821
ksqgtl *** TM-0000ca79-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x333ACD74, ktcdix=2147483647, topxcb=0x333ACD74
ktcipt(topxcb)=0x0
从Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options中断开
四、测试create index online产生的锁
C:\Documents and Settings\olivenan>sqlplus test/test
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:38:15 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set events '10704 trace name context forever, level 12';
会话已更改。
SQL> create index a_t on T (owner, object_name) online;
索引已创建。
SQL> alter session set events '10704 trace name context off';
会话已更改。
SQL> @e:\gettrcname
13 ;
D.VALUE||'/'||LOWER(RTRIM(I.INSTANCE,CHR(0)))||'_ORA_'||P.SPID||'.TRC'
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST\UDUMP/test_ora_3884.trc
SQL>
TM锁有两种 mode=2，4
2 - row-S (SS)
4 - share (S)
ksqgtl *** DL-0000ca79-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl: xcb=0x333ACD74, ktcdix=2147483647, topxcb=0x333ACD74
ktcipt(topxcb)=0x0
*** 2008-03-05 18:38:24.772
*** 2008-03-05 18:38:24.772
ksqcmi: DL,ca79,0 mode=3 timeout=0
ksqcmi: returns 0
ksqgtl: RETURNS 0
*** 2008-03-05 18:38:24.772
ksqgtl *** TM-0000ca79-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x333ACD74, ktcdix=2147483647, topxcb=0x333ACD74
ktcipt(topxcb)=0x0
*** 2008-03-05 18:38:24.837
ksqcnv: TM-0000ca79,00000000 mode=4 timeout=21474836
*** 2008-03-05 18:38:24.837
ksqcmi: TM,ca79,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
mode=2的时间为65ms
*** 2008-03-05 18:38:24.837
ksqcnv: TM-0000ca79,00000000 mode=2 timeout=21474836
*** 2008-03-05 18:38:24.837
ksqcmi: TM,ca79,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
mode=4为0ms
*** 2008-03-05 18:38:25.078
ksqcnv: TM-0000ca79,00000000 mode=4 timeout=21474836
*** 2008-03-05 18:38:25.078
ksqcmi: TM,ca79,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
mode=2的时间为243ms
*** 2008-03-05 18:38:25.175
ksqrcl: TM,ca79,0
ksqrcl: returns 0
mode=4的时间为97ms
总计mode=2的时间为65+243=308ms
mode=4的时间为97ms
虽然create index online 会产生mode=2，4的锁，但是mode=2的锁占用时间要比mode=4的锁时间长，
所以create index online一般对dml操作影响不大。
五、对比create index和create index online的区别
网友的回答
加 online时 ：DDL锁 是有的，但是没有DML锁
不加 online ,DDL锁和DML锁都有
create index online
他们加的锁的级别不一样，是一个级别为2的RS锁
不阻塞级别为3的RX锁，也就是 dml时候加在表上的TM锁
但是，阻塞大部分的DDL加在表上的X类型的TM锁
create index：
如果没有online，加的则是级别为4的S类型TM锁
这个会阻塞DML语句加在表上面的类型为RX的TM锁
所以，这个时候表锁都加不上，更不用行锁了，阻塞在表上面。
通过测试验证了网友的解释。
