insert /*+append */
在有些情况下我们为了加快插入速度使用insert /*+append */
1、无论归档模式、还是非归档模式,append+nologing插入方式效果最好,生成最小的redo日志
2、非归档模式下,append插入模式效果不错
3、归档模式下,append插入模式没有效果
我们做个测试
session1
create table ljl as select * from dba_objects;
insert /*+append*/ into ljl select * from dba_objects;
session2
insert /*+append*/ into ljl select * from dba_objects;
session2卡住
session1
SQL> SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || SID sess,
2 decode(lmode,
3 0,
4 'none',
5 1,
6 'null',
7 2,
8 'row share',
9 3,
10 'row exclusive',
11 4,
12 'share',
13 5,
14 'share row exclusive',
15 6,
16 'exclusive') lmode,
17 ctime,
18 inst_id,
19 id1,
20 id2,
21 lmode,
22 request,
23 TYPE
24 FROM gV$LOCK
25 WHERE (id1, id2, TYPE) IN
26 (SELECT id1, id2, TYPE FROM gV$LOCK WHERE request > 0)
27 ORDER BY id1, request;
SESS LMODE CTIME
------------------------------------------------ ------------------- ----------
INST_ID ID1 ID2 LMODE REQUEST TY
---------- ---------- ---------- ---------- ---------- --
Holder: 25 exclusive 18
1 87380 0 6 0 TM
Waiter: 46 none 16
1 87380 0 0 6 TM
可以看到被堵住了
再做一个测试
session1
insert /*+append*/ into ljl select * from dba_objects;
session2
insert into ljl select * from dba_objects;
SQL> SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || SID sess,
2 decode(lmode,
3 0,
4 'none',
5 1,
6 'null',
7 2,
8 'row share',
9 3,
10 'row exclusive',
11 4,
12 'share',
13 5,
14 'share row exclusive',
15 6,
16 'exclusive') lmode,
17 ctime,
18 inst_id,
19 id1,
20 id2,
21 lmode,
22 request,
23 TYPE
24 FROM gV$LOCK
25 WHERE (id1, id2, TYPE) IN
26 (SELECT id1, id2, TYPE FROM gV$LOCK WHERE request > 0)
27 ORDER BY id1, request;
SESS LMODE CTIME
------------------------------------------------ ------------------- ----------
INST_ID ID1 ID2 LMODE REQUEST TY
---------- ---------- ---------- ---------- ---------- --
Holder: 25 row exclusive 67
1 87380 0 3 0 TM
Waiter: 46 none 45
1 87380 0 0 6 TM
也同样被锁住了
分析原因
insert /*+append */ 加的的是TM级锁,每一条语句执行后进行commit释放TM锁
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30068249/viewspace-1731599/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30068249/viewspace-1731599/