用直接路徑（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