Insert statement plus/* + APPEND */impact analysis on the system of a single commit in a loop, insertappend

Source: Internet
Author: User

Insert statement plus/* + APPEND */impact analysis on the system of a single commit in a loop, insertappend
1./* + APPEND */usage of the prompt

/* + APPEND */indicates that an INSERT statement exclusive hint is used to increase the insert speed and the effect is very obvious, as for its working principle of speed improvement, I wrote another article titled direct-path) two Methods of Improving the Performance of insert are mentioned in this article (proposed in advantage mode ).

2. the huge impact of using/* + APPEND */in a single loop commit

Good things, good things, and bad things can help you get twice the result with half the effort.

/* + APPEND */because it is inserted at a high water level or above, and/* + APPEND */will add 6 levels of exclusion lock to the table, so imagine, if you want to insert 1 million rows of data in a loop or if loop, only one row of Qualified Data is inserted at a time in each loop, commit can only be placed within the loop (/* + APPEND */determines that commit cannot be placed outside the special ring), that is, it represents 10 thousand rows of data, with 10 million commit.

I have already seen this Application Scenario in multiple projects, so I will analyze the impact of this usage for reference by those who need it.

What are the serious consequences of such an operation on the ORACLE database? 3. Impact Analysis test 3.1 INSERT single commit using/* + APPEND */hint in loop

(1) create three test tables

Create table emp (empnonumber); -- cursor value reference table

Create table emp_inter (numbernonumber); -- intermediate table

Create table emp_append_test (empnonumber); -- target table

(2) Insert 10000 rows of data into each of the referenced and intermediate tables with the cursor Value

Set timing on;

Declare

I number: = 1;

Begin

Loop

Insert into emp (empno) values (I );

Insert into emp_inter (numberno) values (I );

Commit;

I: = I + 1;

Exit when I = 10001;

End loop;

End;

/

 

Output Time Value: Elapsed: 00:00:02. 8 -- insert 10000 rows of data to two tables at the same time, which takes 2.8 seconds

3.2 serious impact of space occupation test and analysis 3.2.1 pre-test records of the space occupied by the three tables

Select 'emp' as table_name, count (distinct dbms_rowid.rowid_block_number (rowid) as blocks from EMP

Union all

Select 'emp_inter 'as table_name, count (distinct dbms_rowid.rowid_block_number (rowid) as blocks from EMP_INTER

Union all

Select 'emp_append_test 'as table_name, count (distinct dbms_rowid.rowid_block_number (rowid) as blocks from EMP_APPEND_TEST

The output result is as follows:

Table_name

Blocks

EMP

16

EMP_INTER

16

EMP_APPEND_TEST

0

We can see that the EMP and EMP_INTER tables are inserted with 999 rows of data each, and 16 blocks are occupied. Currently, the EMP_APPEND_TEST tables with no data inserted occupy 0 blocks.

3.2.2 Add/* + APPEND */hint in the loop to insert data

Insert data to the target table emp_append_test

Set serveroutput on

Set timing on

Declare

N number: = 1;

Begin

For c in (select empnofrom emp)

Loop

Insert/* + APPEND */Into emp_append_test select * from emp_inter where numberno = c. empno;

N: = n + 1;

Commit;

End loop;

Dbms_output.put_line ('insert rows is: '| n );

End;

/

Output Value: insert rows is: 10000 -- insert 10000 rows of data

Elapsed: 00:00:11. 62 -- this time, 10000 rows of data are inserted into a table, which takes 11.62 seconds.

3.2.3 query the number of blocks occupied by the three tables again

Select 'emp' as table_name, count (distinct dbms_rowid.rowid_block_number (rowid) as blocks from EMP

Union all

Select 'emp_inter 'as table_name, count (distinct dbms_rowid.rowid_block_number (rowid) as blocks from EMP_INTER

Union all

Select 'emp_append_test 'as table_name, count (distinct dbms_rowid.rowid_block_number (rowid) as blocks from EMP_APPEND_TEST;

The output result is as follows:

Table_name

Blocks

EMP

16

EMP_INTER

16

EMP_APPEND_TEST

10000

From the above view, the result is terrible. 10 thousand rows of data are inserted, occupying 10 thousand blocks, 8 KB for each block, and 10 thousand rows of Data occupy about 10000 MB (8/1024 ).

3.3 query performance impact

(1) Test the EMP query performance of tables that are not in use/* + APPEND */with a single repeating commit.

SQL> set autotrace on statistics

SQL> select * from emp where empno = 1;

-------------

Statistics

----------------------------------------------------------

1 recursive cballs

0 db block gets

23Consistent gets

0 physical reads

0 redo size

523 bytes sent via SQL * Net to client

523 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

(2) Test the query performance of the table EMP_APPEND_TEST using/* + APPEND */repeating a single commit.

SQL> set autotrace on statistics

SQL> select * from EMP_APPEND_TEST where empno = 1;

---------------------------

Statistics

----------------------------------------------------------

1 recursive cballs

0 db block gets

20003Consistent gets

10000Physical reads

0 redo size

523 bytes sent via SQL * Net to client

523 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

(3) Comparison of query performance results:

Table_name

Consistent gets

Physical reads

EMP

23

21

EMP_APPEND_TEST

20003

10024

Consistent gets doubled by 869

Physical reads doubled by 477

4. Problem Summary

The Insert statement adds/* + APPEND */hint to a single commit in a loop. Because/* + APPEND */hint is a feature inserted above the high waterline, each commit is performed, A new block will be taken for storage, and a block will be pushed up at a high level, and/* + APPEND */hint will add level 6 exclusive locks to the table, as a result, new data can be inserted only after the commit operation. A large number of single/* + APPEND */Inserts will greatly increase the table, except for the performance impact on the insert operation, it will have a greater impact on future select, update, and delete operations.

 

 

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.