INSERT..SELECT / INSERT..VALUES 성능 테스트

 

DBMS : oracle 19.3

 

- insert into values 로 한 건씩 처리하는 것과 insert into .. select 방식의 redo 와 buffer get 등 성능 차이 테스트 - insert into select 방식이 훨씬 좋은 성능을 보인다

---

 

100만건 insert 테스트

- insert into select 방식

SQL> alter SESSION SET STATISTICS_LEVEL = ALL ;
SET LINESIZE 2000
SET PAGES 10000
Session altered.

SQL> SQL> 
SQL> 
SQL> set timing on
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;

NAME								      VALUE
---------------------------------------------------------------- ----------
redo entries								  1
redo size								104

Elapsed: 00:00:00.00
SQL> 
SQL> insert into sk.tab2 select * from sk.tab1 ;

1000000 rows created.

Elapsed: 00:00:01.20

-------------------------------------------------------------------------------------------
| Id  | Operation		 | Name | Starts | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT	 |	|      1 |	0 |00:00:00.95 |   25980 |    691 |
|   1 |  LOAD TABLE CONVENTIONAL | TAB2 |      1 |	0 |00:00:00.95 |   25980 |    691 |
|   2 |   TABLE ACCESS FULL	 | TAB1 |      1 |   1000K|00:00:00.10 |    2880 |    689 |
-------------------------------------------------------------------------------------------


SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
; 

NAME								      VALUE
---------------------------------------------------------------- ----------
redo entries							      16087
redo size							   22499268

Elapsed: 00:00:00.00
SQL>

 

- insert into values 방식

SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;

NAME								      VALUE
---------------------------------------------------------------- ----------
redo entries								  0
redo size								  0

SQL> DECLARE
 vn_data1 VARCHAR2(100) := 'PSK';
BEGIN
	 FOR i IN 1..1000000
	 LOOP
	 INSERT INTO SK.TAB1
	 VALUES
	 (vn_data1, i);
	END LOOP;
    COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:56.54
SQL> SQL> 
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;

NAME								      VALUE
---------------------------------------------------------------- ----------
redo entries							    1011520
redo size							  293896628

Elapsed: 00:00:00.00
SQL> 


-- 여기부터 10046 trace
DECLARE
 vn_data1 VARCHAR2(100) := 'PSK';
BEGIN
         FOR i IN 1..1000000
         LOOP
         INSERT INTO SK.TAB1
         VALUES
         (vn_data1, i);
        END LOOP;
    COMMIT;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     10.96      12.64          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     10.96      12.64          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
********************************************************************************

SQL ID: fqc2wsvzsrrdw Plan Hash: 0

INSERT INTO SK.TAB1
VALUES
 (:B2 , :B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1000000     34.38      43.88          3       3177    1040479     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1000001     34.38      43.88          3       3177    1040479     1000000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  TAB1 (cr=7 pr=2 pw=0 time=467 us starts=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        1        0.00          0.00
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                         3        0.00          0.00
  log file switch (checkpoint incomplete)        12        2.35          9.38
  log file switch completion                      2        0.10          0.20
  log buffer space                                2        0.96          1.32