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

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