用直接路径（direct-path）insert提升性能的两种方法

1、传统串行insert方式

    常见的insert方式有两种：

（1）      insert into table_name values(....)

（2）      insert into target_table select* from source_table

    上面这两种常规的插入式，默认都是在串行方式下的插入，会将insert的数据写入buffer_cache，插入前检查表中是否有block中存有空闲空间可以追加插入，并写入redo log。

2、直接路径（direct-path）insert优点与使用要点

2.1、Direct-path insert 方式的优点

（1）      可以将insert数据跳过buffer_cahce，省掉了buffer block的格式化与DBWR操作，直接从PGA写入磁盘

（2）      不检查表中现有的block是否有空闲空间，直接在表的高水位线（HWM）以上插入

（3）      如果在数据库处于非归档模式下，或者是数据就处于归档模式，表级处于nologging状态下，只有少量的空间信息redo写入、不写入数据undo（因为要回滚时可以直接回退到高水线即可，而不需要针对insert生成delete的回滚记录），所以在特定的情况下，直接路径（direct-path）的insert方式，在性能上远远快于常规的串行插入方式。

2.2、使用direct-path insert需要特别注意的要点

2.2.1 DB非force loggging模式下direct-path insert对redo与undo的写入影响

      如果在数据库处于归档模式，以及表处于logging模式下，直接路径（direct-path）性能提升会大打折扣，因为，虽然direct-path能生效，但是仍然会记录下完整的redo和undo。

      也就是说，在归档模式下，还需要将表改成nologging模式，才不会写数据的redo。

2.2.2 DB force logging模式下direct-pathinsert对redo的写入影响

Note: If the database or tablespace is in FORCE LOGGING mode, then direct-path INSERT always logs, regardless of the logging setting.

      如果数据库或表空间在forcelogging模式，则direct-path insert总是会写日志，无论logging如何设置。

3、使用直接路径（direct-path）insert的方法

3.1 方法一：使用 hint方式

     以下为ORACLE官方技术资料对APPENDhint的说明：

APPEND hint: Instructs the optimizer to use direct-path INSERT (data is appended to the end of the table, regardless of whether there is free space in blocks below the high watermark)

3.1.1 数据库非归档模式下使用 hint方式

      当数据库处于非归档模式下，不管表为logging模式还是nologging模式，使用 hint，既可以使用direct-path，还将不记录redo和undo

用法如下：

INSERT INTO new_object SELECT * FROM dba_objects;

3.1.2 数据库处于归模模式下使用 hint方式

      当数据库处于归模模式下，若表为logging模式，即便使用 hint，虽然direct-path可以起到作用，但是insert操作仍然会写redo记录，就算你在insert语句上加nologging也不会有效果，redo日志与undo照写不误。

      需要通修改表或修改索引，或修改表空间的no-logging模式来达到不写redo与undo的效果

以下为从metalink（文档ID166727.1）中找到的技术资料：

The APPEND hint is required for using serial direct-load INSERT. Direct-load INSERT operations can be done without logging of redo information in case the database is in ARCHIVELOG mode. Redo information generation is suppressed by setting no-logging mode for the table, partition, or index into which data will be inserted by using an ALTER TABLE, ALTER INDEX, or ALTER TABLESPACE command.

用法如下：

Alter table new_object nologging; INSERT INTO new_object SELECT * FROM dba_objects;

3.2  方法二：DML并行模式的方式

      DML并行模式下，direct-path插入方式是默认的，当然，在DML并行模式下如果想不使用direct-path插入，可以通过加noappendhint实现。以下是DML并行模式下的direct-path插入：

     并行DML的前提条件：

     （1）ORACLE版本为Oracle Enterprise Edition

     （2）操作的会话开启并行DML

     （3）下面三项要求必须满足一项：

         1）目标表上开启并行属性（DEGREE）

         2）插入语句中指定并行提示（）

         3）有设置PARALLEL_DEGREE_POLICY参数的值为AUTO

以数据库为非归档模式用法为例（注意归档模式，还需将表改成nologging模式）： （1）alter session enable parallel dml; 语句还有选项有：：ALTER SESSION { ENABLE | FORCE } PARALLEL DML; （2）alter table new_object_directpath parallel 8; （3）insert into new_object_directpathnologging select * from new_object_old;

4、归档模式下传统串行方式与direct-path方式insert性能对比

环境说明：

源表名	test_dba_objects
源表行数	1630104
源表segment大小	184MB

操作步骤与性能对比结果如下：

传统串行insert方式	APPEND hint的direct-path insert方式	DML并行的direct-path insert方式
（1）建表与修改设定 SQL>create table new_object_directpath as select * from test_dba_objects where 1=2 SQL>alter table new_object_directpath nologging SQL> SET TIMING ON Elapsed: 00:00:00.54 （2）insert耗时 SQL> insert into new_object_directpath nologgingselect * from test_dba_objects; 1630104 rows created. Elapsed: 00:00:12.43 未产生数据redo与undo	（1）建表与修改设定 SQL>create table new_object_directpath as select * from test_dba_objects where 1=2 SQL>alter table new_object_directpath nologging SQL> SET TIMING ON Elapsed: 00:00:00.54 （2）insert耗时 SQL> insert  into new_object_directpath select * from test_dba_objects; 1630104 rows created. Elapsed: 00:00:05.83 未产生数据redo与undo	SQL>create table new_object_directpath as select * from test_dba_objects where 1=2 SQL>alter table new_object_directpath nologging SQL> SET TIMING ON Elapsed: 00:00:00.54 （2）修改表的并行模式 SQL> alter table new_object_directpath parallel 8; （3）       insert耗时 SQL> insert into new_object_directpath select * from test_dba_objects; 1630104 rows created. Elapsed: 00:00:05.61 未产生数据redo与undo

本文作者：黎俊杰（网名：踩点），从事”系统架构、操作系统、存储设备、数据库、中间件、应用程序“六个层面系统性的性能优化工作

欢迎加入 系统性能优化专业群，共同探讨性能优化技术。群号：258187244