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锁