Two ways to improve performance using direct-path insert: direct-pathinsert

Two ways to improve performance using direct-path insert: direct-pathinsert
1. Traditional serial insert

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-ins are inserted in the serial mode by default. The insert data will be written to buffer_cache. check whether there is free space in the block in the table before insertion, and write the redo log and the undo of the data.

2. Advantages of direct-path insert and key points of use 2.1. Advantages of Direct-path insert

(1) The insert data can be skipped from buffer_cahce, saving the formatting of the buffer block and DBWR operations, and directly writing data from the PGA to the disk.

(2) do not check whether the existing block in the table has free space. Insert the block directly above the table's high water level line (HWM ).

(3) If the database is in non-archive mode, or the data is in archive mode, and the table is in nologging state, only a small amount of space information is written to redo and data is not written to undo (because it can be rolled back directly to the high water line, rather than generating a delete rollback record for insert ), therefore, in specific circumstances, the direct-path insert method is much faster than the conventional serial insert method in terms of performance.

2.2 important points to note when using direct-path insert 2.2.1 Impact of direct-path insert on redo and undo writes in DB non-force loggging Mode

If the database is in archive mode and the table is in logging mode, the performance improvement of direct-path will be greatly reduced, because although direct-path can take effect, however, the complete redo and undo records are recorded.

That is to say, in archive mode, you also need to change the table to nologging mode to avoid writing redo and undo data.

2.2.2 Impact of direct-pathinsert on redo write in DB force logging Mode

Note:If the database or tablespace is in force logging mode, 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 logs regardless of logging settings.

3. Use the direct-path insert method. Method 1: Use the/* + APPEND */hint method.

The following table describes APPENDhint in ORACLE official technical documents:

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

3.1.1 use/* + APPEND */hint in non-archive database mode

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

 

The usage is as follows:

INSERT/* + APPEND */INTO new_object SELECT * FROM dba_objects;

3.1.2 use the/* + APPEND */hint mode when the database is in the modulo Mode

When the database is in the modulo mode and the table is in the logging mode, even if/* + APPEND */hint is used, although direct-path can be used, the insert operation will still write redo records, even if you add nologging to the insert statement, the redo log and the undo log are correct.

You need to modify the table or index, or modify the no-logging mode of the tablespace to achieve the effect of not writing redo and undo data.

The following are the technical materials 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 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.

The usage is as follows:

Alter table new_object nologging; INSERT/* + APPEND */INTO new_object SELECT * FROM dba_objects;

 

3.2 Method 2: DML Parallel Mode

In DML parallel mode, direct-path insertion is the default mode. Of course, if you want to disable direct-path insertion in DML parallel mode, you can add noappendhint. Direct-path insertion in DML parallel mode is as follows:

Prerequisites for parallel DML:

(1) ORACLE Enterprise Edition

(2) Enable parallel DML for Operation sessions

(3) The following three requirements must be met:

1) enable parallel attributes on the target table (DEGREE)

2) Specify parallel prompts In the insert Statement (/* + parallel n */)

3) The value of PARALLEL_DEGREE_POLICY is set to AUTO.

Take the database non-archive mode as an example (note the archive mode, you also need to 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_directpathnologging select * from new_object_old;

 

4. Comparison of insert performance in traditional serial mode and direct-path mode in archive Mode

Environment Description:

Source table name	Test_dba_objects
Source table row count	1630104
Source table segment size	184 MB

The operation steps and performance comparison results are as follows:

Traditional serial insert mode	Direct-path insert method of APPEND hint	DML parallel direct-path insert mode
(1) Table creation and modification 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 consumption SQL> insert into new_object_directpathNologgingSelect * from test_dba_objects;   1630104 rows created.   Elapsed: 00: 00:12.43 No data redo and undo are generated	(1) Table creation and modification 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 consumption 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 are generated	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) modify the parallel mode of a table SQL> alter table new_object_directpath parallel 8;   (3) insert time consumption SQL> insert/* + parallel (new_object_directpath, 8) */into new_object_directpath select * from test_dba_objects;   1630104 rows created.   Elapsed: 00: 00:05.61 No data redo and undo are generated

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244