오라클 19c commit 기본 옵션 정리 및 테스트

2024. 5. 27. 22:01 내맘대로긍정

프린트 하기

OS 환경 : Oracle Linux 7.6 (64bit)

DB 환경 : Oracle Database 19.3.0.0

방법 : 오라클 19c commit 기본 옵션 정리 및 테스트

오라클 db 환경에서 일반적으로 commit을 입력할때 그냥 commit;만 입력함
하지만 commit 에도 옵션이 존재함
commit 뒤의 옵션은 모두 선택사항이고 옵션 없이 commit; 을 입력하면 기본값으로 commit work write wait immediate 라는 옵션으로 commit이 됨
pl/sql 에서는 commit; 을 입력하면 기본값으로 commit work write nowait batch 라는 옵션으로 commit이 됨
(pl/sql의 경우 비분산 트랜잭션 환경에서 commit_logging 및 commit_wait 파라미터가 설정되지 않은 경우)

commit 구문

1

commit [work] [write [immediate | batch] [wait | nowait]];

세부 옵션 설명
- work 옵션
commit 이나 commit work는 동일한 명령임, 표준 SQL을 준수하기 위해 존재하는 옵션이라고 함

- write 옵션
write 옵션을 사용하여 commit 작업으로 생성된 리두 정보가 리두 로그에 기록되는 우선 순위를 지정함
이 절은 대기 시간을 줄여서 리두 로그에 대한 I/O 대기 시간을 제거하여 성능을 향상시킬 수 있음
특히 아래 조건이 적용되는 환경에서 응답 시간을 개선하는 데 유용함
- 업데이트 트랜잭션의 양이 커서 리두 로그를 디스크에 자주 기록해야 하는 경우
- 애플리케이션은 비동기적으로 commit된 트랜잭션의 손실을 허용할 수 있는 경우
- 리두 로그 쓰기를 기다리면서 발생하는 대기 시간이 전체 응답 시간에 큰 영향을 미치는 경우

이 옵션이 더불어 wait과 nowait, immediate와 batch 옵션도 사용할수 있음
참고로 이 옵션을 생략하면 commit 은 commit_logging 또는 commit_wait 파라미터(설정된 경우)에 의해 제어됨

- wait, nowait 옵션
wait, nowait 옵션을 사용해서 제어권이 사용자에게 반환되는 시기를 지정함
wait, nowait 옵션을 생략하면 트랜잭션은 wait 동작으로 커밋됨

wait 옵션
wait 옵션은 redo buffer 가 온라인 redo log file 에 영구 저장된 후에만 커밋이 클라이언트에게 반환되도록 함
batch 모드나 immediate 모드 상관없이 이 commit 문에서 클라이언트가 성공적으로 응답을 받으면 트랜잭션이 영구적으로 커밋된 것을 뜻함
redo log file 에 성공적으로 기록된 후에 문제가 발생하면 성공 메시지가 클라이언트에게 전달되지 않을 수 있음
이 경우 클라이언트는 트랜잭션이 커밋되었는지 여부를 알 수 없음
wait 옵션을 사용하면 커밋 메시지를 받았을 때 데이터가 손실되지 않았음을 확신할 수 있음

nowait 옵션
nowait 옵션은 redo log file 에 기록이 완료되었는지 여부에 관계없이 커밋이 클라이언트에게 반환되도록 함
이 동작은 트랜잭션 처리량을 증가시킬 수 있음
wait 옵션을 사용하면 커밋 메시지를 받았을 때 데이터가 손실되지 않았음을 확신할 수 있지만
nowait 옵션을 사용할 경우 커밋 메시지를 받은 후 redo log file 에 기록되기 전에 문제가 발생하면
트랜잭션이 변경 사항이 영구 저장된 것으로 잘못 표시될 수 있음

- immediate, batch 옵션
immediate, batch 옵션을 사용하여 redo log buffer 가 redo log file 에 기록되는 시점을 지정할 수 있음
immediate, batch 옵션을 생략하면 트랜잭션은 immediate 동작으로 커밋됨

immediate 옵션
Log Writer Process(LGWR)가 트랜잭션의 redo 정보를 redo log file 에 즉시 기록하도록 함
이 작업 옵션은 Disk I/O를 강제하므로 트랜잭션 처리량을 줄일 수 있음

batch 옵션
redo 를 다른 동시에 실행되는 트랜잭션들과 함께 redo log buffer 에 버퍼링(모음)함
충분한 redo 가 모이면 redo log file 에 내려씀
이 동작은 "group commit"이라고 불리며, 여러 트랜잭션의 redo가 Single I/O 작업으로 로그에 기록됨
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/COMMIT.html

본문에서는 일반 sql에서의 commit과 pl/sql에서 각 commit 동작별로 시간이 얼마나 소요 되는지를 테스트해봄
일반 sql 의 commit 기본 동작 : commit work write wait immediate
pl/sql 의 commit : commit work write nowait batch

테스트
1. 일반 sql 에서의 commit 옵션별 수행 시간 테스트
2. pl/sql에서의 commit 옵션별 수행 시간 테스트

1. 일반 sql 에서의 commit 옵션별 수행 시간 테스트
샘플테이블 및 인덱스 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SQL>
create table commit_test
(
  id           number(10),
  description  varchar2(50 char)
)
tablespace users nocompress;
 
create unique index commit_test_pk on commit_test (id) tablespace users;
 
alter table commit_test add (
  constraint commit_test_pk
  primary key
  (id)
  using index commit_test_pk
  enable validate);

commit 테스트용 sql 파일 생성
(sqlplus 접속 후 SQL> 이하 내용 모두 복사 붙여넣기 실행)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

$ sqlplus -s / as sysdba
SQL>
set lines 200 pages 10000
set feedback off verify off heading off echo off
 
-- Create Table Initialization SQL File
spool truncate_table.sql
select 'TRUNCATE TABLE commit_test;' truncate_tbl from dual;
spool off
 
-- Create DEFAULT COMMIT SQL File
spool commit_default.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'commit;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create WAIT COMMIT SQL File
spool commit_wait.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+2001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE WAIT;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create NOWAIT COMMIT SQL File
spool commit_nowait.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+4001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE NOWAIT;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create BATCH COMMIT SQL File
spool commit_batch.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+6001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE BATCH;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create IMMEDIATE COMMIT SQL File
spool commit_immediate.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+8001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE IMMEDIATE;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create BATCH, WAIT COMMIT SQL File
spool commit_batch_wait.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+10001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE BATCH WAIT;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create BATCH, NOWAIT COMMIT SQL File
spool commit_batch_nowait.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+12001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE BATCH NOWAIT;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create IMMEDIATE, WAIT COMMIT SQL File
spool commit_immediate_wait.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+14001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE IMMEDIATE WAIT;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off
 
-- Create IMMEDIATE, NOWAIT COMMIT SQL File
spool commit_immediate_nowait.sql
SELECT statement
FROM (
    SELECT LEVEL AS seq, 
           'INSERT INTO commit_test (id, description) VALUES (' || LEVEL || '+16001' || ', ''Description for '' || ' || LEVEL || ');' AS statement
    FROM dual
    CONNECT BY LEVEL <= 2000
    UNION ALL
    SELECT LEVEL + 0.5 AS seq, 'COMMIT WRITE IMMEDIATE NOWAIT;' AS statement
    FROM dual
CONNECT BY LEVEL <= 2000
)
ORDER BY seq;
spool off

생성된 sql 파일 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14

$ ls -al
total 7156
drwxr-xr-x 2 oracle oinstall   4096 May 26 10:32 .
drwxr-xr-x 6 oracle oinstall     88 May 23 16:21 ..
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_batch_nowait.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_batch.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_batch_wait.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_default.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_immediate_nowait.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_immediate.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_immediate_wait.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_nowait.sql
-rw-r--r-- 1 oracle oinstall 804001 May 26 09:50 commit_wait.sql
-rw-r--r-- 1 oracle oinstall    202 May 26 09:50 truncate_table.sql

파일들이 정상적으로 생성됨

각 commit 별 실행시간 측정용 sh 파일 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

$ cat measure_execution_times.sh
#!/bin/bash
 
# List of SQL files
sql_files=(
    "truncate_table.sql"
    "commit_default.sql"
    "commit_wait.sql"
    "commit_nowait.sql"
    "commit_batch.sql"
    "commit_immediate.sql"
    "commit_batch_wait.sql"
    "commit_batch_nowait.sql"
    "commit_immediate_wait.sql"
    "commit_immediate_nowait.sql"
)
 
# Output file for results
output_file="execution_times.txt"
 
# Function to execute SQL file and measure time
measure_time() {
    local sql_file=$1
    echo "Executing $sql_file..."
    result=$(sqlplus -s imsi/"dlatl1!" <<EOF
    set heading off
    set feedback off
    timing start all_operations
    @${sql_file}
    timing stop all_operations
    exit
EOF
)
    elapsed_time=$(echo "$result" | grep "Elapsed:" | awk '{print $2}')
    echo "$sql_file : $elapsed_time" >> $output_file
}
 
# Clear previous results
> $output_file
 
# Measure time for each SQL file
for sql_file in "${sql_files[@]}"; do
    measure_time $sql_file
done
 
echo ""
echo "Execution times saved to $output_file."
echo "Result : "
cat $output_file

sh 파일 권한 부여 후 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

$ chmod u+x measure_execution_times.sh
$ sh measure_execution_times.sh
Executing truncate_table.sql...
Executing commit_default.sql...
Executing commit_wait.sql...
Executing commit_nowait.sql...
Executing commit_batch.sql...
Executing commit_immediate.sql...
Executing commit_batch_wait.sql...
Executing commit_batch_nowait.sql...
Executing commit_immediate_wait.sql...
Executing commit_immediate_nowait.sql...
 
Execution times saved to execution_times.txt.
Result :
commit_default.sql : 00:00:01.54
commit_wait.sql : 00:00:01.63
commit_nowait.sql : 00:00:01.14
commit_batch.sql : 00:00:01.70
commit_immediate.sql : 00:00:01.66
commit_batch_wait.sql : 00:00:01.60
commit_batch_nowait.sql : 00:00:01.04
commit_immediate_wait.sql : 00:00:01.66
commit_immediate_nowait.sql : 00:00:01.17

각 commit 동작별 시간이 측정됨
일반 sql로 실행시 commit에 옵션을 주지 않았을때 01.54초 소요되었고,
일반 sql에서의 commit 기본값인 commit write wait immediate 의 시간도 01.66 초로 확인됨
일반 sql에서 nowait 옵션을 사용하면 속도가 빨라짐을 확인 가능함(commit_nowait.sql, commit_batch_nowait.sql, commit_immediate_nowait.sql등)

2. pl/sql에서의 commit 옵션별 수행 시간 테스트
샘플테이블 및 인덱스 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SQL>
drop table commit_test purge;
create table commit_test
(
  id           number(10),
  description  varchar2(50 char)
)
tablespace users
nocompress ;
 
create unique index commit_test_pk on commit_test
(id)
tablespace users;
 
alter table commit_test add (
  constraint commit_test_pk
  primary key
  (id)
  using index commit_test_pk
  enable validate);

pl/sql 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

SQL>
SET SERVEROUTPUT ON
 
DECLARE
  PROCEDURE do_loop (p_type  IN  VARCHAR2) AS
    l_start  NUMBER;
    l_loops  NUMBER := 2000;
l_elapsed NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';
 
    l_start := DBMS_UTILITY.get_time;
    FOR i IN 1 .. l_loops LOOP
      INSERT INTO commit_test (id, description)
      VALUES (i, 'Description for ' || i);
     
      CASE p_type
        WHEN 'DEFAULT'          THEN COMMIT;
        WHEN 'WAIT'             THEN COMMIT WRITE WAIT;
        WHEN 'NOWAIT'           THEN COMMIT WRITE NOWAIT;
        WHEN 'BATCH'            THEN COMMIT WRITE BATCH;
        WHEN 'IMMEDIATE'        THEN COMMIT WRITE IMMEDIATE;
        WHEN 'BATCH_WAIT'       THEN COMMIT WRITE BATCH WAIT;
        WHEN 'BATCH_NOWAIT'     THEN COMMIT WRITE BATCH NOWAIT;
        WHEN 'IMMEDIATE_WAIT'   THEN COMMIT WRITE IMMEDIATE WAIT;
        WHEN 'IMMEDIATE_NOWAIT' THEN COMMIT WRITE IMMEDIATE NOWAIT;
      END CASE;
    END LOOP;
l_elapsed := (DBMS_UTILITY.get_time - l_start) / 100;
    DBMS_OUTPUT.put_line(RPAD('COMMIT WRITE ' || p_type, 30) || ': ' || l_elapsed || ' sec');
  END;
BEGIN
  do_loop('DEFAULT');
  do_loop('WAIT');
  do_loop('NOWAIT');
  do_loop('BATCH');
  do_loop('IMMEDIATE');
  do_loop('BATCH_WAIT');
  do_loop('BATCH_NOWAIT');
  do_loop('IMMEDIATE_WAIT');
  do_loop('IMMEDIATE_NOWAIT');
END;
/
 
COMMIT WRITE DEFAULT          : 0.18 sec
COMMIT WRITE WAIT             : 0.84 sec
COMMIT WRITE NOWAIT           : 0.1 sec
COMMIT WRITE BATCH            : 0.67 sec
COMMIT WRITE IMMEDIATE        : 1 sec
COMMIT WRITE BATCH_WAIT       : 1.05 sec
COMMIT WRITE BATCH_NOWAIT     : 0.15 sec
COMMIT WRITE IMMEDIATE_WAIT   : 0.53 sec
COMMIT WRITE IMMEDIATE_NOWAIT : 0.06 sec
 
PL/SQL procedure successfully completed.

각 commit 동작별 시간이 측정됨
pl/sql로 실행시 commit에 옵션을 주지 않았을때 0.18초 소요되었고,
pl/sql에서 commit 기본값인 commit write batch nowait 의 시간도 0.15 초로 확인됨
pl/sql에서 wait 옵션을 사용하면 속도가 느려짐을 확인 가능함(COMMIT WRITE WAIT, COMMIT WRITE BATCH, COMMIT WRITE BATCH_WAIT, COMMIT WRITE IMMEDIATE_WAIT 등)

결론 :
오라클에서의 commit 명령은 다양한 옵션을 통해 트랜잭션 커밋 방식을 세밀하게 제어할 수 있음
이를 통해 시스템의 성능을 최적화하고, 응답 시간 및 데이터 안전성 요구사항에 맞춘 커밋 방식을 선택할 수 있음
일반 sql에서는 commit 기본 옵션으로 commit write wait immediate를 사용하고, pl/sql에서는 commit write nowait batch를 기본값으로 사용함

참조 :

336219.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/COMMIT.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-C3FE1082-94AA-4BE8-98F2-480205D99DCA
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/COMMIT_WAIT.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/COMMIT_LOGGING.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/static-sql.html#GUID-56EC1B31-CA06-4460-A098-49ABD4706B9C
https://community.oracle.com/mosc/discussion/3646688/mistery-about-commit-statement
https://cafe.naver.com/dbian/3411

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 19c 테이블 initrans 값에 따른 lock 테스트 (0)	2024.06.13
오라클 23ai 신기능 일반 에러 메세지 개선 (0)	2024.06.02
오라클 19c sqlplus 의 history 명령어 (0)	2024.05.15
오라클 23ai 신기능 테이블 최대 컬럼 갯수 증가 (0)	2024.05.11
오라클 23ai 신기능 정리 (0)	2024.05.10

내맘대로긍정

박영민 ympark.pro@gmail.com

Category

Menu

Comment

Link