Oracle insert /+ APPEND /原理解析

最新推荐文章于 2025-05-29 15:37:13 发布

转载最新推荐文章于 2025-05-29 15:37:13 发布 · 5.5w 阅读

ORACLE 专栏收录该内容

45 篇文章

订阅专栏

本文深入探讨了在非归档和归档模式下，利用INSERT/*+APPEND*/语句进行数据插入时的性能优化策略。通过对比非归档模式下仅使用APPEND与增加NOLOGGING选项的性能提升，以及在归档模式下插入与追加操作对redo日志的影响。同时，阐述了直接路径加载的高效性及其对表级锁定的影响。最后，强调了插入与追加操作对并发事务的影响，并提供了一个实例演示了如何通过这些策略提高数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于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.环境介绍

数据库版本：

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，这个值是有偏差的.建议建立一个视图：

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 非归档模式

SQL>
 archive log list

数据库日志模式            
 非存档模式

自动存档            
 禁用

存档终点           
 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列    
 95

当前日志序列          
 97

2.1.1 nologging表

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_normalfrom dual;

REDO_APPEND
 REDO_NORMAL

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

      14820    
 8501976

2.1.2 logging表：

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_normalfrom dual;

REDO_APPEND
 REDO_NORMAL

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

      14820    
 8501988

2.2归档模式下：

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表

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 fromdual;

REDO_APPEND
 REDO_NORMAL

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

      15192    
 8473036

2.2.2 logging表

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_normalfrom dual;

REDO_APPEND
 REDO_NORMAL

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

    8538656    
 8472736

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

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

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