九乡新闻网袁腾飞讲元朝历史mp3:习10074事件，以及执行ddl时产生的锁
来源：百度文库 编辑：九乡新闻网 时间：2024/04/19 14:37:48
本文主要是学习10074事件，以及看看truncate table，create index，create index online时产生的锁
一、测试oracle9201环境下 create index onlineSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 17:10:34 2008Copyright (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 - ProductionSQL> 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: ora9Redo thread mounted by this instance: 1Oracle process number: 12Windows 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/testSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:17:07 2008Copyright (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 optionsSQL> 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
----------
51833SQL> select to_char(51833,'xxxxx')from dual;TO_CHA
------
ca79SQL> @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.trcSQL>在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/testSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:28:52 2008Copyright (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 optionsSQL> 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.trcSQL>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/testSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:38:15 2008Copyright (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 optionsSQL> 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.trcSQL>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 0mode=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 0mode=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 0mode=2的时间为243ms
*** 2008-03-05 18:38:25.175
ksqrcl: TM,ca79,0
ksqrcl: returns 0mode=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锁
所以，这个时候表锁都加不上，更不用行锁了，阻塞在表上面。通过测试验证了网友的解释。
一、测试oracle9201环境下 create index onlineSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 17:10:34 2008Copyright (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 - ProductionSQL> 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: ora9Redo thread mounted by this instance: 1Oracle process number: 12Windows 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/testSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:17:07 2008Copyright (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 optionsSQL> 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
----------
51833SQL> select to_char(51833,'xxxxx')from dual;TO_CHA
------
ca79SQL> @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.trcSQL>在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/testSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:28:52 2008Copyright (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 optionsSQL> 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.trcSQL>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/testSQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 5 18:38:15 2008Copyright (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 optionsSQL> 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.trcSQL>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 0mode=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 0mode=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 0mode=2的时间为243ms
*** 2008-03-05 18:38:25.175
ksqrcl: TM,ca79,0
ksqrcl: returns 0mode=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锁
所以，这个时候表锁都加不上，更不用行锁了，阻塞在表上面。通过测试验证了网友的解释。
习10074事件，以及执行ddl时产生的锁
直肠炎与结肠炎的区别以及产生原因
asp.net 的页面执行顺序 页面事件执行详解
现在厂家生产尿素的工艺是怎样的,以及产生的废水的
重机DDL-5550-4/-6系列平缝机控制箱的调整-资讯中心-中国缝制设备网
产生爱情时的九种感 觉
产生爱情时的九种感觉
产生爱情时的9 种感觉
产生爱情时的九种感觉
@ 产生爱情时的九种感觉
产生爱情时的九种感觉
4、暴君的产生
5、背叛的产生
闪光思想的产生
产生快感的经穴
性梦是怎样产生的
性梦是怎样产生的？
性梦是怎样产生的？
性梦是怎样产生的？
1文字的产生
性梦是如何产生的？
字母B的产生
字母C的产生
字母E的产生