Append与Direct-Path Insert（一）

来源：互联网发布：2016中国移动网络电视编辑：程序博客网时间：2024/05/17 22:54

Append提示在进行批量数据加载的时候，是很高效的一个手段。前段进行实验，发现这个hint在使用的时候，还是有一些需要注意的地方的。

1、基础知识

还是先看看基础知识吧。append是oracle提供给SQL开发者使用的hint之一。所谓hint的作用，简单说就是影响Oracle优化器生成特定的执行计划。SQL是一种数据描述语言。实际对数据进行操作是通过对SQL的解析，生成执行计划的过程。

Append提示的作用提示优化器使用直接路径插入（Direct-Path Insert）方式。下面简单说下Oracle的两种插入和两种模式。

两种工作模式，分别成为串行化模式（Serial Mode）和并行模式（Parallel Mode）。串行化模式就是我们通常的SQL工作模式，一个SQL操作内部顺序执行。并行模式就会将工作按照一定规则划分为多个工作线程同时执行，之后汇合结果。在进行大数据量操作的时候，可以考虑采用并行模式以提高效率。

插入方式上，Oralce有Conventional Insert和Direct-Path Insert两种。

传统插入（Conventional Insert）方式是我们通常使用的Insert方式。默认情况下，传统插入是使用串行化模式的。在串行化模式下，如果在insert中使用append提示，Oracle会对该条insert使用Direct-Path插入方式。

直接路径（Direct-Path）方式在是并行操作模式下的默认插入方式。在并行模式下，只有使用noappend提示，才能使用传统的插入方式。

所以，插入操作的时候，究竟是使用并行还是串行模式，与append提示无关。

在使用直接路径插入的时候，数据并不是先从数据表中找到随机的空位后插入，而是直接将数据插入到数据表的末尾，直接推高高水位线。这种操作方式，较传统的方式要快。

2、快速的直接路径插入

相对于传统模式，使用append提示之后，批量插入的速度可以得到显著的提升。

SQL> create table t as select * from dba_objects where 1=0;

Table created

SQL> select count(*) from t;

COUNT(*)

----------

Executed in 0.01 seconds

//一般插入方式

SQL> insert into t select * from dba_objects;

50367 rows inserted

Executed in 0.571 seconds

//还原环境，删除数据

SQL> truncate table t;

Table truncated

Executed in 0.431 seconds

//Append插入方式

SQL> insert /*+ append */into t select * from dba_objects;

50367 rows inserted

Executed in 0.45 seconds

通过上面的简单实验，可以看到：在相同的条件下，使用append进行数据表的插入，要显著的快于传统方式。这种特点在海量数据加载、迁移的过程中，可以显著的提高效率。要注意，这种方式因为直接提升高水位线，不会利用表中原有被删除的空间，在一些删除操作比较多的表中，会造成很大的空间浪费，要避免滥用引发空间膨胀问题。

3、 Append操作与锁

在使用Append操作时，要注意Append操作是一种特殊的插入操作，会阻止包括自身会话在内的插入操作。首先，我们先看一下普通插入的系统行为。

//数据准备，建立数据表

SQL> create table t as select owner, object_id, rownum num from dba_objects where 1=0;

Table created

//session1中，启动一个插入操作

SQL> insert into t select owner,object_id,rownum from dba_objects where rownum<10;

9 rows inserted

SQL> select * from v$mystat where rownum<2;

SID STATISTIC# VALUE

---------- ---------- ----------

140 0 1

//观察系统中锁的情况

SQL> select * from v$lock where sid=140;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 3 0

6BE3BE7C 6BE3BF98 140 TX 458760 692 6 0

说明：Session1（SID=140）首先在数据表对象加上一个TM锁，模式为3共享锁，同时对其插入的数据项加了事务TX锁，模式为6独占锁；

//启动另一个session2，执行插入操作

SQL> insert into t select owner, object_id, rownum from dba_objects where object_id=120;

1 row inserted

SQL> select sid from v$mystat where rownum<2;

SID

----------

142

SQL> select * from v$lock where sid in (140,142);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 3 0

6BDC4138 6BDC4150 142 TM 53083 0 3 0

6BE127B8 6BE128D4 142 TX 655371 658 6 0

6BE3BE7C 6BE3BF98 140 TX 458760 692 6 0

说明：session2（SID=142）进行插入操作，也尝试在对象表（ID1 53083）上加一个共享锁TM，LMODE=3。与Session1（SID=140）的TM锁可以共存（共享锁之间），同时将插入事务锁TX，类型为独占锁，与原有的session1独占锁加上的是不一样的对象。

说明：在传统的插入操作中，会话之间插入操作是不会相互影响的。同一个事务内部，进行插入操作之间，也没有互斥的情况。

下面我们开始实验append提示。（下面实验是使用相同的两个session（sid=140,142））。

//在session1（sid=140）中使用append关键字插入。

SQL> insert /*+ append */ into t select owner, object_id, rownum from dba_objects where rownum<10;

9 rows inserted

//同一个session内，进行相同的操作

SQL> insert /*+ append */ into t select owner, object_id, rownum from dba_objects where rownum<10;

ORA-12838: 无法在并行模式下修改之后读/修改对象

说明：即使在同一个session里，对一个表进行append插入之后，必须先commit之后，才能进行相同表的操作。这个特点可能与append采用的并行模式操作有关。

这个时候，我们查看一下当前sesson1（sid=140）的锁状态。

//查看session1的锁状况

SQL> select * from v$lock where sid=140;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 6 0

6BE3BE7C 6BE3BF98 140 TX 196611 652 6 0

请注意：当我们进行append操作的时候，oracle对表t加锁状况出现差异。变化点在数据表锁TM的模式LMODE为6（排他锁），这点与一般插入存在差异。TM模式6的现象说明，在进行append操作的时候，oracle对数据表级的锁操作不是一个共享锁（LMODE=3），而是一个排他锁（LMODE=6）。从锁知识角度，如果这时其他会话尝试插入操作（向数据表T加一个LMODE=3的共享锁），是会被hange住。

//此时session2（sid=142）

SQL> select sid from v$mystat where rownum<2;

SID

----------

142

//尝试以一个一般方式插入数据

SQL> insert into t select owner, object_id, rownum from dba_objects where object_id=120;

//session2被hange住

//此时查看两个会话的锁状况

SQL> select * from v$lock where sid in (140,142);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 6 0

6BDC4138 6BDC4150 142 TM 53083 0 0 3

6BE3BE7C 6BE3BF98 140 TX 196611 652 6 0

说明：此时session2尝试在表T上加入一个共享锁Lmode=3，因为已经被sid=140独占排他。所以被hange住。

//session1 commit之后。

SQL> commit;

Commit complete

//session2解除hange状态

结论：实验结果和我们预想的一样。共享锁和排他锁是不能兼容的。通常，当我们在一个会话中使用了append一批数据后，其他会话是不能插入操作。如果进行的话，会被hange住。进行append操作的会话，也不能在表中进行其他操作。除非提交或回滚事务。

作为这个结论的拓展实验，我们进行下列尝试。

//在session1已经进行append的操作情况下

//如果session2也要求进行append的操作时候

SQL> insert /*+ append */ into t select owner, object_id, rownum from dba_objects where rownum<10;

//session2也hange住

SQL> select * from v$lock where sid in (140,142);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 6 0

6BDC4138 6BDC4150 142 TM 53083 0 0 6

6BE3BE7C 6BE3BF98 140 TX 65571 638 6 0

//sesion2 被hange住

结论一致，区别在于session2在进行append的操作的时候，也会尝试获取排他的表锁，自然也会被hange住。

综上所述：append操作是一种有并行操作趋势的快速插入hint。在使用的时候，要注意及时的commit，以防止出现严重的并发操作问题。同时，在高并发的系统（OLTP）中，也要注意慎用。

那么，是不是append都是要获取排他锁呢？排他锁和共享锁之间关系如何协同？还有在同一个会话中，对锁控制有什么特点？我们在下一篇中继续探讨。