Oracle insert /*+ APPEND */原理解析

关于insert /*+ append */我们需要注意以下三点： a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。 b、insert /*+ append */时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统

关于insert /*+ append */我们需要注意以下三点：

a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。
b、insert /*+ append */ 时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。
c、insert /*+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。

1.环境介绍

数据库版本：

1 2 3 4 5 6 7 8

SQL>  select  *  from  v$version; BANNER --------------------------------------------------------------------- Oracle  Database  11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE    11.2.0.1.0      Production TNS  for  Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production

网上说测试时不要使用auto trace来查看redo size，这个值是有偏差的.建议建立一个视图：

1 2 3 4 5 6

SQL>  create  or  replace  view  redo_size  as select  value from  v$mystat, v$statname where  v$mystat.statistic# = v$statname.statistic# and  v$statname. name  =  'redo size' ; -- 视图已创建。

2.示例演示：

2.1 非归档模式

1 2 3 4 5 6

SQL> archive log list 数据库日志模式             非存档模式 自动存档             禁用 存档终点            USE_DB_RECOVERY_FILE_DEST 最早的联机日志序列     95 当前日志序列           97

2.1.1 nologging表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SQL>  create  table  test1 nologging  as  select  *  from  dba_objects  where  1=0; 表已创建。    SQL>  select  *  from  redo_size;       VALUE ----------    25714940    SQL>  insert  into  test1  select  *  from  dba_objects; 已创建72753行。    SQL>  select  *  from  redo_size;       VALUE ----------    34216916    SQL>  insert  /*+ APPEND */   into  test1  select  *  from  dba_objects; 已创建72753行。    SQL>  select  *  from  redo_size;       VALUE ----------    34231736    SQL>  select  (34231736-34216916) redo_append , (34216916-25714940) redo_normal from  dual; REDO_APPEND REDO_NORMAL ----------- -----------        14820     8501976

2.1.2 logging表：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

SQL>  create  table  test2  as  select  *  from  dba_objects  where  1=0;    表已创建。    SQL>  select  *  from  redo_size;       VALUE ----------    34273348    SQL>  insert  into  test2  select  *  from  dba_objects;    已创建72754行。    SQL>  select  *  from  redo_size;       VALUE ----------    42775336    SQL>  insert  /*+ APPEND */   into  test2  select  *  from  dba_objects; 已创建72754行。    SQL>  select  *  from  redo_size;       VALUE ----------    42790156    SQL>  select  (42790156-42775336) redo_append , (42775336-34273348) redo_normal from  dual; REDO_APPEND REDO_NORMAL ----------- -----------        14820     8501988

2.2归档模式下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE例程已经关闭。 SQL> startup mount ORACLE例程已经启动。    Total System  Global  Area  477073408 bytes Fixed  Size                   1337324 bytes Variable  Size              293603348 bytes Database  Buffers          176160768 bytes Redo Buffers                5971968 bytes 数据库装载完毕。 SQL>  alter  database  archivelog; 数据库已更改。    SQL>  alter  database  open ; 数据库已更改。    SQL> archive log list 数据库日志模式            存档模式 自动存档             启用 存档终点            USE_DB_RECOVERY_FILE_DEST 最早的联机日志序列     95 下一个存档日志序列   97 当前日志序列           97

2.2.1 nologging表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SQL>  select  *  from  redo_size;      VALUE ----------       17936    SQL>  insert  into  test1  select  *  from  dba_objects; 已创建72754行。    SQL>  select  *  from  redo_size;       VALUE ----------     8490972    SQL>  insert  /*+ APPEND */   into  test1  select  *  from  dba_objects; 已创建72754行。    SQL>  select  *  from  redo_size;       VALUE ----------     8506164    SQL>  select  (8506164-8490972) redo_append , (8490972-17936) redo_normal  from dual; REDO_APPEND REDO_NORMAL ----------- -----------        15192     8473036

2.2.2 logging表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SQL>  select  *  from  redo_size;       VALUE ----------     8506780    SQL>  insert  into  test2  select  *  from  dba_objects; 已创建72754行。    SQL>  select  *  from  redo_size;       VALUE ----------    16979516    SQL>  insert  /*+ APPEND */   into  test2  select  *  from  dba_objects; 已创建72754行。    SQL>  select  *  from  redo_size;       VALUE ----------    25518172    SQL>  select  (25518172-16979516) redo_append , (16979516-8506780) redo_normal from  dual; REDO_APPEND REDO_NORMAL ----------- -----------      8538656     8472736

在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的。

3.insert /*+ append */会阻塞除select以外的DML语句，direct-path insert操作是单独一个事务。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

SQL>  select  count (*)  from  test2;    COUNT (*) ----------      291016    SQL>  insert  into  test2  select  *  from  dba_objects; 已创建72754行。    SQL>  select  count (*)  from  test2;    COUNT (*) ----------      363770    SQL>  insert  /*+ APPEND */  into  test2  select  *  from  dba_objects; 已创建72754行    同一个session下：    SQL>  select  count (*)  from  test2; select  count (*)  from  test2 * 第 1 行出现错误: ORA-12838: 无法在并行模式下修改之后读/修改对象    SQL>  commit ; 提交完成。    SQL>  select  count (*)  from  test2;    COUNT (*) ----------      436524    SQL>  insert  /*+ APPEND */  into  test2  select  *  from  dba_objects; 已创建72754行。    SQL> shutdown immediate ORA-01097: 无法在事务处理过程中关闭 - 请首先提交或回退    SQL>  select   *  from  v$mystat  where  rownum<2;            SID STATISTIC#      VALUE    ---------- ---------- ----------            224          0          1    SQL>  select  KADDR,TYPE,LMODE  from  v$lock  where  sid=224;    KADDR            TY      LMODE ---------------- -- ---------- 0000000071BAE180 TM          6 0000000070CB11B8 TX          6

另外开启一个会话，就会发现只能select，其他DML全部阻塞。