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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.