Two ways to improve performance with direct path (Direct-path) insert

1. Traditional Serial Insert Method

There are two common insert methods:

(1) INSERT INTO table_name values (...)

(2) INSERT into target_table select* from source_table

The above two general plug-in, the default is in the serial mode of insertion, the insert data will be written to Buffer_cache, inserted in the Pre-check table in the block where there is free space can be appended to insert, and write to redo log and undo data.

2. Direct path (DIRECT-PATH) Insert advantages and use Points 2.1, Direct-path Insert Method advantages

(1) The Insert data can be skipped over BUFFER_CAHCE, eliminating the format and DBWR operation of buffer block, writing to disk directly from the PGA

(2) Do not check whether the existing block in the table has free space, directly above the table's high watermark (HWM) inserted

(3) If the database is in non-archive mode, or the data is in the archive mode, the table level is in the nologging state, only a small amount of spatial information redo write, do not write data undo (because you can roll back to the high watermark directly, Instead of having to generate a rollback record of the Delete for insert, the direct path (Direct-path) is inserted in a way that is much faster than the regular serial insert in the specific case.

2.2. Key points needing special attention with Direct-path Insert 2.2.1 db non-force loggging mode Direct-path insert affects write to redo and undo

If the database is in archive mode and the table is in logging mode, the direct path (Direct-path) performance increase is compromised because, although direct-path can take effect, the full redo and undo are still recorded.

In other words, in the archive mode, you also need to change the table to nologging mode to not write the data redo and undo.

Direct-pathinsert write impact to redo in 2.2.2 DB Force logging mode

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

If the database or tablespace is in forcelogging mode, Direct-path Insert always writes the log, regardless of how logging is set.

3, using the direct path (Direct-path) Insert Method 3.1 method One: Using/*+ APPEND */Hint Way

The following is a description of Oracle's official technical data for Appendhint:

APPEND hint:instructs the optimizer to use Direct-path INSERT (data are appended to the End of the table, regardless of whether there is free space in blocks below the high Watermark

3.1.1 Database non-archive mode using/*+ append*/hint mode

When the database is in non-archive mode, whether the table is in logging mode or nologging mode, use the/*+append */hint, you can use Direct-path, and you will not record redo and undo

Use the following:

INSERT/*+ APPEND */into New_object SELECT * from Dba_objects;

3.1.2 Database is in return mode using/*+append */hint mode

When the database is in return mode, if the table is in logging mode, even though the/*+append */hint is used, although direct-path can play a role, the insert operation will still write Redo records. Even if you add nologging on the INSERT statement will not be effective, redo log and undo to write.

Need to modify the table or modify the index, or modify the tablespace's no-logging mode to achieve the effect of not writing redo and undo

The following is the technical information found in Metalink (document 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 was in ARCHIVELOG mode. Redo information generation is suppressed by setting no-logging Mode for the table, partition, or index into which data would be Inserted by using an ALTER TABLE, ALTER INDEX, or alter tablespace Command.

Use the following:

Alter table New_object nologging; INSERT/*+ APPEND */into New_object SELECT * from Dba_objects;

3.2 Method Two: The way of DML parallel mode

In DML parallel mode, Direct-path is the default, and of course, in DML parallel mode, if you want to not use Direct-path insertion, you can implement it by adding noappendhint. The following are Direct-path inserts in DML parallel mode:

Prerequisites for parallel DML:

(1) Oracle version for Oracle Enterprise Edition

(2) Operation session Open parallel DML

( 3 The following three requirements must satisfy one:

1 ) on the target table, open the parallel attribute ( degree )

2 ) to specify a parallel hint in the INSERT statement ( /*+ Parallel n * / )

3 ) There are settings Parallel_degree_policy the value of the parameter is AUTO

Take the database as an example of non-archival mode usage (note the archive mode and change the table to nologging mode): (1) Alter session enable parallel DML; The statement also has the following options: ALTER SESSION {ENABLE | Force} PARALLEL DML; (2) ALTER TABLE New_object_directpath parallel 8; (3)insert /*+parallel (New_object_directpath, 8) */ into New_object_directpath Nologging Select * from New_object_old;

4. Comparison of insert performance between traditional serial mode and Direct-path mode under archive

Environment Description:

source table Name	Test_dba_objects
Number of source table rows	1630104
source Table Segment Size	184MB

The operating procedure and performance comparison results are as follows:

Traditional Serial Insert Method	APPEND Hint's Direct-path insert method	Direct-path Insert method for DML parallelism
(1) Build table and modify settings 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 time sql> insert into New_object_directpath nologging select * from Test_dba_objects;   1630104 rows created.      elapsed:00:00: 12.43 data redo and undo not generated	(1) Build table and modify settings 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 time sql> insert  /*+append * / into New_object_directpath select * from Test_dba_objects;   1630104 rows created.      elapsed:00:00:05.83 No data redo and 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) Modifying the parallel mode of the table sql> ALTER TABLE New_object_directpath parallel 8; (3) Insert time-consuming sql> Insert/*+parallel (new_object_directpath,8) */into New_object_directpath select * from Test_dba_objects; 1630104 rows created. elapsed:00:00: 05.61 Data redo and undo not generated

This article Li Junjie (Network Name: casing), engaged in "system architecture, operating systems, storage devices, databases, middleware, applications" six levels of systematic performance optimization work

Welcome to the System performance Optimization Professional group, to discuss performance optimization technology together. Group number: 258187244

Two ways to improve performance with direct path (Direct-path) insert