프린트 하기 URL 복사

OS 환경 : Oracle Linux 9.4 (64bit)

 

DB 환경 : Oracle Database 19.31.0.0

 

방법 : 오라클 19c insert 시 undo 발생량 확인 및 감소 테스트

대량 insert 시 undo 에러가 발생할 수 있음(ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1')
이때의 일반적인 방안으로는 undo tablespace 크기를 늘리거나, insert 단위를 쪼개는 방법이 있음
참고 : ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' ( https://positivemh.tistory.com/377 )
하지만 이 방법 외에도 경우에 따라 본문에서 테스트한 방식을 사용하면 undo 사용량을 줄일 수 있음
본문에서는 대량 INSERT 시 방법에 따라 UNDO 사용량이 얼마나 달라지는지 확인해봄
각각 일반 insert시, append insert시, 인덱스 유지, 미유지시, 테이블 logging, nologging 경우 여러가지를 조합해서 테스트 해봄

 

 

테스트
테스트1. 일반 INSERT + 인덱스 유지
테스트2. 일반 INSERT + 인덱스 UNUSABLE
테스트3. 일반 INSERT + 인덱스 제거
테스트4. NOLOGGING + 일반 INSERT
테스트5. NOLOGGING + APPEND + 인덱스 유지
테스트6. NOLOGGING + APPEND + 인덱스 UNUSABLE

 

 

참고로 UNDO 사용량은 현재 세션의 트랜잭션 정보를 V$TRANSACTION에서 조회함
USED_UBLK : 현재 트랜잭션이 사용하는 UNDO 블록 수
USED_UREC : 현재 트랜잭션에서 생성된 UNDO 레코드 수

 

 

UNDO 정보는 트랜잭션이 종료되기 전에 확인해야 함
따라서 각 INSERT 실행 후 COMMIT 또는 ROLLBACK을 수행하기 전에 UNDO 사용량을 조회함

 

 

테스트
더미 테이블 생성 및 더미 데이터 삽입(10만건)

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
SQL>
drop table undo_insert_src purge;
create table undo_insert_src
nologging
as
select level as id,
       mod(level, 1000) as group_id,
       cast(
           'C1_' || lpad(level, 10'0'||
           rpad('A'80'A')
           as varchar2(100)
       ) as c1,
       cast(
           'C2_' || lpad(level, 10'0'||
           rpad('B'80'B')
           as varchar2(100)
       ) as c2,
       cast(
           'C3_' || lpad(level, 10'0'||
           rpad('C'80'C')
           as varchar2(100)
       ) as c3,
       date '2026-01-01' + mod(level, 365) as created_at
from dual
connect by level <= 100000;
 
SQL> select count(*from undo_insert_src;
 
  COUNT(*)
----------
    100000

100000건 생성됨

 

 

INSERT 대상 테이블 생성

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
drop table undo_insert_test purge;
create table undo_insert_test
(
    id          number,
    group_id    number,
    c1          varchar2(100),
    c2          varchar2(100),
    c3          varchar2(100),
    created_at  date
)
logging;

 

 

인덱스 갱신에 따른 UNDO 사용량 차이를 확인하기 위해 인덱스 3개 생성

1
2
3
4
SQL>
create index undo_insert_test_ix1 on undo_insert_test(id);
create index undo_insert_test_ix2 on undo_insert_test(group_id, created_at);
create index undo_insert_test_ix3 on undo_insert_test(c1);

 

 

테이블, 인덱스 상태 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL>
select table_name, logging
from dba_tables
where table_name = 'UNDO_INSERT_TEST';
 
TABLE_NAME                LOGGING
------------------------- ----------
UNDO_INSERT_TEST          YES
 
SQL>
col index_name for a30
select index_name, status
from dba_indexes
where table_name = 'UNDO_INSERT_TEST'
order by index_name;
 
INDEX_NAME                     STATUS
------------------------------ ----------
UNDO_INSERT_TEST_IX1           VALID
UNDO_INSERT_TEST_IX2           VALID
UNDO_INSERT_TEST_IX3           VALID

테이블은 logging 상태, 인덱스는 valid 상태임

 

 

현재 세션 UNDO 사용량 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL>
set lines 200 pages 1000
col xid for a20
col undo_mb for 999,999,990.00
select s.sid,
       s.serial#,
       rawtohex(t.xid) as xid,
       t.used_ublk,
       t.used_urec,
       round(
           t.used_ublk * to_number(p.value) / 1024 / 1024,
           2
       ) as undo_mb
from v$session s,
     v$transaction t,
     v$parameter p
where s.taddr = t.addr
and s.sid = sys_context('USERENV''SID')
and p.name = 'db_block_size';
 
no rows selected

현재는 undo를 사용하지 않고 있음
USED_UBLK : 현재 트랜잭션이 사용하는 UNDO 블록 수
USED_UREC : 현재 트랜잭션에서 생성된 UNDO 레코드 수
UNDO_MB : USED_UBLK와 DB_BLOCK_SIZE를 이용해 계산한 대략적인 UNDO 사용량

 

 

테스트1. 일반 INSERT + 인덱스 유지
인덱스가 정상 상태인 테이블에 일반 INSERT를 수행

1
2
3
4
5
6
SQL> 
insert /*+ noappend */ into undo_insert_test
select *
from undo_insert_src;
 
100000 rows created.

 

 

insert 수행 후 commit이나 rollback을 실행하지 않은 상태에서 undo 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>
select s.sid,
       s.serial#,
       rawtohex(t.xid) as xid,
       t.used_ublk,
       t.used_urec,
       round(
           t.used_ublk * to_number(p.value) / 1024 / 1024,
           2
       ) as undo_mb
from v$session s,
     v$transaction t,
     v$parameter p
where s.taddr = t.addr
and s.sid = sys_context('USERENV''SID')
and p.name = 'db_block_size';
 
       SID    SERIAL# XID                   USED_UBLK  USED_UREC         UNDO_MB
---------- ---------- -------------------- ---------- ---------- ---------------
       284      14279 08001400B5370000           2372      42748           18.53

 

 

rollback 수행

1
2
3
SQL> rollback;
 
Rollback complete.

 

 

테스트1. 결론 :
18.53mb의 undo를 사용함

 

 

테스트2. 인덱스 UNUSABLE + 일반 INSERT

인덱스 UNUSABLE 상태로 변경

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>
alter session set skip_unusable_indexes = true;
alter index undo_insert_test_ix1 unusable;
alter index undo_insert_test_ix2 unusable;
alter index undo_insert_test_ix3 unusable;
 
select index_name,
       status
from dba_indexes
where table_name = 'UNDO_INSERT_TEST'
order by index_name;
 
INDEX_NAME                     STATUS
------------------------------ ----------
UNDO_INSERT_TEST_IX1           UNUSABLE
UNDO_INSERT_TEST_IX2           UNUSABLE
UNDO_INSERT_TEST_IX3           UNUSABLE

 

 

인덱스를 UNUSABLE 상태로 변경한 후 일반 INSERT 수행

1
2
3
4
5
6
SQL>
insert /*+ noappend */ into undo_insert_test
select *
from undo_insert_src;
 
100000 rows created.

 

 

insert 수행 후 commit이나 rollback을 실행하지 않은 상태에서 undo 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>
select s.sid,
       s.serial#,
       rawtohex(t.xid) as xid,
       t.used_ublk,
       t.used_urec,
       round(
           t.used_ublk * to_number(p.value) / 1024 / 1024,
           2
       ) as undo_mb
from v$session s,
     v$transaction t,
     v$parameter p
where s.taddr = t.addr
and s.sid = sys_context('USERENV''SID')
and p.name = 'db_block_size';
 
       SID    SERIAL# XID                   USED_UBLK  USED_UREC         UNDO_MB
---------- ---------- -------------------- ---------- ---------- ---------------
       284      14279 05000400DE370000             92       7891            0.72

 

 

rollback 수행

1
2
3
SQL> rollback;
 
Rollback complete.

 

 

테스트2. 결론 :
0.72mb의 undo를 사용함

 

 

다음 테스트를 위해 인덱스 REBUILD

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL>
alter index undo_insert_test_ix1 rebuild;
alter index undo_insert_test_ix2 rebuild;
alter index undo_insert_test_ix3 rebuild;
 
select index_name,
       status
from dba_indexes
where table_name = 'UNDO_INSERT_TEST'
order by index_name;
 
INDEX_NAME                     STATUS
------------------------------ ----------
UNDO_INSERT_TEST_IX1           VALID
UNDO_INSERT_TEST_IX2           VALID
UNDO_INSERT_TEST_IX3           VALID

 

 

테스트 3 인덱스 제거 + 일반 INSERT
인덱스를 완전히 제거한 상태에서 일반 INSERT를 수행함

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
truncate table undo_insert_test;
drop index undo_insert_test_ix1;
drop index undo_insert_test_ix2;
drop index undo_insert_test_ix3;
 
select index_name,
       status
from dba_indexes
where table_name = 'UNDO_INSERT_TEST';
 
no rows selected

 

 

일반 INSERT 수행

1
2
3
4
5
6
SQL>
insert /*+ noappend */ into undo_insert_test
select *
from undo_insert_src;
 
100000 rows created.

 

 

insert 수행 후 commit이나 rollback을 실행하지 않은 상태에서 undo 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>
select s.sid,
       s.serial#,
       rawtohex(t.xid) as xid,
       t.used_ublk,
       t.used_urec,
       round(
           t.used_ublk * to_number(p.value) / 1024 / 1024,
           2
       ) as undo_mb
from v$session s,
     v$transaction t,
     v$parameter p
where s.taddr = t.addr
and s.sid = sys_context('USERENV''SID')
and p.name = 'db_block_size';
 
       SID    SERIAL# XID               USED_UBLK  USED_UREC    UNDO_MB
---------- ---------- ---------------- ---------- ---------- ----------
       285      39080 0A0014003F340000         92       7891        .72

 

 

rollback 수행

1
2
3
SQL> rollback;
 
Rollback complete.

 

 

테스트3. 결론 :
0.72mb의 undo를 사용함

 

 

다음 테스트를 위해 제거한 인덱스 재생성

1
2
3
4
SQL>
create index undo_insert_test_ix1 on undo_insert_test(id);
create index undo_insert_test_ix2 on undo_insert_test(group_id, created_at);
create index undo_insert_test_ix3 on undo_insert_test(c1);

 

 

테스트4. NOLOGGING + 일반 INSERT
테이블의 LOGGING 속성만 NOLOGGING으로 변경한 후 일반 INSERT를 수행
NOAPPEND 힌트를 사용해 Conventional Path INSERT로 실행함

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
truncate table undo_insert_test;
alter table undo_insert_test nologging;
 
select table_name,
       logging
from dba_tables
where table_name = 'UNDO_INSERT_TEST';
 
TABLE_NAME           LOG
-------------------- ---
UNDO_INSERT_TEST     NO

 

 

일반 INSERT 수행

1
2
3
4
5
6
SQL>
insert /*+ noappend */ into undo_insert_test
select *
from undo_insert_src;
 
100000 rows created.

 

 

insert 수행 후 commit이나 rollback을 실행하지 않은 상태에서 undo 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>
select s.sid,
       s.serial#,
       rawtohex(t.xid) as xid,
       t.used_ublk,
       t.used_urec,
       round(
           t.used_ublk * to_number(p.value) / 1024 / 1024,
           2
       ) as undo_mb
from v$session s,
     v$transaction t,
     v$parameter p
where s.taddr = t.addr
and s.sid = sys_context('USERENV''SID')
and p.name = 'db_block_size';
 
       SID    SERIAL# XID               USED_UBLK  USED_UREC    UNDO_MB
---------- ---------- ---------------- ---------- ---------- ----------
       285      39080 06001600DA390000       2372      42748      18.53

 

 

rollback 수행

1
2
3
SQL> rollback;
 
Rollback complete.

 

 

테스트4. 결론 :
18.53mb의 undo를 사용함
NOLOGGING은 주로 REDO 생성량을 줄이기 위한 속성임
일반 INSERT에서는 테이블을 NOLOGGING으로 변경해도 테이블 데이터와 인덱스 변경을 위한 UNDO가 생성됨
따라서 테스트 1과 비교했을 때 UNDO 사용량 차이가 나지 않음

 

 

테스트5. NOLOGGING + APPEND + 인덱스 유지
테이블을 NOLOGGING으로 변경하고 APPEND 힌트를 사용해 Direct Path INSERT를 수행함
인덱스는 정상 상태로 유지함

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL>
truncate table undo_insert_test;
alter table undo_insert_test nologging;
 
select index_name,
       status
from dba_indexes
where table_name = 'UNDO_INSERT_TEST'
order by index_name;
 
INDEX_NAME                     STATUS
------------------------------ ----------
UNDO_INSERT_TEST_IX1           VALID
UNDO_INSERT_TEST_IX2           VALID
UNDO_INSERT_TEST_IX3           VALID

 

 

APPEND 힌트를 사용해 INSERT

1
2
3
4
5
6
SQL>
insert /*+ append */ into undo_insert_test
select *
from undo_insert_src;
 
100000 rows created.

 

 

insert 수행 후 commit이나 rollback을 실행하지 않은 상태에서 undo 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>
select s.sid,
       s.serial#,
       rawtohex(t.xid) as xid,
       t.used_ublk,
       t.used_urec,
       round(
           t.used_ublk * to_number(p.value) / 1024 / 1024,
           2
       ) as undo_mb
from v$session s,
     v$transaction t,
     v$parameter p
where s.taddr = t.addr
and s.sid = sys_context('USERENV''SID')
and p.name = 'db_block_size';
 
       SID    SERIAL# XID               USED_UBLK  USED_UREC    UNDO_MB
---------- ---------- ---------------- ---------- ---------- ----------
       285      39080 0300060038370000       1803       4107      14.09

 

 

rollback 수행

1
2
3
SQL> rollback;
 
Rollback complete.

 

 

테스트5. 결론 :
14.09mb의 undo를 사용함
Direct Path INSERT는 기존 블록의 빈 공간을 찾아 데이터를 입력하지 않고 High Water Mark 이후의 새로운 블록에 데이터를 적재함
이 방식은 Conventional Path INSERT보다 테이블 데이터에 대한 UNDO 사용량을 줄일 수 있음
다만 사용 가능한 인덱스가 존재하면 인덱스 갱신 작업에 대한 UNDO는 계속 발생함

 

 

테스트6. NOLOGGING + APPEND + 인덱스 UNUSABLE
인덱스를 UNUSABLE 상태로 변경한 후 NOLOGGING과 APPEND를 적용함
테이블 데이터는 Direct Path 방식으로 적재하고 인덱스는 갱신하지 않음

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>
truncate table undo_insert_test;
alter table undo_insert_test nologging;
alter session set skip_unusable_indexes = true;
alter index undo_insert_test_ix1 unusable;
alter index undo_insert_test_ix2 unusable;
alter index undo_insert_test_ix3 unusable;
 
select index_name,
       status
from dba_indexes
where table_name = 'UNDO_INSERT_TEST'
order by index_name;
 
INDEX_NAME                     STATUS
------------------------------ --------
UNDO_INSERT_TEST_IX1           UNUSABLE
UNDO_INSERT_TEST_IX2           UNUSABLE
UNDO_INSERT_TEST_IX3           UNUSABLE

 

 

APPEND 힌트를 사용해 INSERT

1
2
3
4
5
6
SQL>
insert /*+ append */ into undo_insert_test
select *
from undo_insert_src;
 
100000 rows created.

 

 

insert 수행 후 commit이나 rollback을 실행하지 않은 상태에서 undo 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>
select s.sid,
       s.serial#,
       rawtohex(t.xid) as xid,
       t.used_ublk,
       t.used_urec,
       round(
           t.used_ublk * to_number(p.value) / 1024 / 1024,
           2
       ) as undo_mb
from v$session s,
     v$transaction t,
     v$parameter p
where s.taddr = t.addr
and s.sid = sys_context('USERENV''SID')
and p.name = 'db_block_size';
 
       SID    SERIAL# XID               USED_UBLK  USED_UREC    UNDO_MB
---------- ---------- ---------------- ---------- ---------- ----------
       277      27133 03000700BC370000          1          1        .01

 

 

rollback 수행

1
2
3
SQL> rollback;
 
Rollback complete.

 

 

테스트6. 결론 :
0.01mb의 undo를 사용함
테이블 데이터 적재에 필요한 UNDO가 감소하고 인덱스 갱신도 수행하지 않으므로 이번 테스트 중 UNDO 사용량이 가장 적음
Direct Path INSERT는 기존 블록의 빈 공간을 찾아 데이터를 입력하지 않고 High Water Mark 이후의 새로운 블록에 데이터를 적재함
이 방식은 Conventional Path INSERT보다 테이블 데이터에 대한 UNDO 사용량을 줄일 수 있음

 

 

테스트 끝. 인덱스를 다시 REBUILD

1
2
3
4
SQL>
alter index undo_insert_test_ix1 rebuild;
alter index undo_insert_test_ix2 rebuild;
alter index undo_insert_test_ix3 rebuild;

 

 

테이블을 LOGGING으로 변경

1
2
3
SQL> alter table undo_insert_test logging;
 
Table altered.

 

 

테스트 결과 정리 :
10만 건 INSERT를 기준으로 일반 INSERT, 인덱스 상태, NOLOGGING, APPEND 적용 여부에 따른 UNDO 사용량을 비교함

테스트1. 일반 INSERT + 인덱스 유지
USED_UBLK : 2,372
USED_UREC : 42,748
UNDO 사용량 : 18.53MB
인덱스가 정상 상태인 테이블에 일반 INSERT를 수행한 결과 18.53MB의 UNDO를 사용함
테이블 데이터 입력과 인덱스 3개에 대한 갱신이 함께 수행되어 테스트 중 두 번째로 많은 UNDO를 사용함

 

테스트2. 인덱스 UNUSABLE + 일반 INSERT
USED_UBLK : 92
USED_UREC : 7,891
UNDO 사용량 : 0.72MB
인덱스를 UNUSABLE 상태로 변경한 후 일반 INSERT를 수행한 결과 0.72MB의 UNDO를 사용함
인덱스를 유지한 테스트1과 비교하면 UNDO 사용량이 약 96.1% 감소함
테이블 데이터에 대한 UNDO는 생성되지만 인덱스 갱신이 수행되지 않아 UNDO 사용량이 크게 감소함

 

테스트3. 인덱스 제거 + 일반 INSERT
USED_UBLK : 92
USED_UREC : 7,891
UNDO 사용량 : 0.72MB
인덱스를 제거한 후 일반 INSERT를 수행한 결과 0.72MB의 UNDO를 사용함
인덱스를 UNUSABLE 상태로 변경한 테스트2와 동일한 결과가 확인됨
인덱스를 제거하거나 UNUSABLE 상태로 변경하는 두 방식 모두 INSERT 과정에서 인덱스 갱신을 수행하지 않으므로 UNDO 사용량 측면에서는 동일한 효과가 나타남

 

테스트4. NOLOGGING + 일반 INSERT
USED_UBLK : 2,372
USED_UREC : 42,748
UNDO 사용량 : 18.53MB
테이블을 NOLOGGING으로 변경한 후 일반 INSERT를 수행한 결과 18.53MB의 UNDO를 사용함
LOGGING 상태에서 수행한 테스트1과 동일한 결과가 확인됨
NOLOGGING은 REDO 생성량을 줄이기 위한 설정이고 일반 INSERT에서 발생하는 테이블과 인덱스 변경에 대한 UNDO 사용량은 줄이지 못함

 

테스트5. NOLOGGING + APPEND + 인덱스 유지
USED_UBLK : 1,803
USED_UREC : 4,107
UNDO 사용량 : 14.09MB
NOLOGGING 상태에서 APPEND 힌트를 사용하고 인덱스를 유지한 결과 14.09MB의 UNDO를 사용함
일반 INSERT를 수행한 테스트1과 비교하면 UNDO 사용량이 약 24.0% 감소함
APPEND를 사용하면서 테이블 데이터 적재에 필요한 UNDO는 감소했지만 인덱스가 VALID 상태로 유지되어 인덱스 갱신에 대한 UNDO가 계속 발생함
APPEND만 적용해도 UNDO 사용량이 일부 감소하지만 인덱스가 존재하는 경우 감소 효과는 제한적임

 

테스트6. NOLOGGING + APPEND + 인덱스 UNUSABLE
USED_UBLK : 1
USED_UREC : 1
UNDO 사용량 : 0.01MB
인덱스를 UNUSABLE 상태로 변경하고 NOLOGGING과 APPEND를 적용한 결과 0.01MB의 UNDO를 사용함
일반 INSERT를 수행한 테스트1과 비교하면 UNDO 사용량이 약 99.9% 감소함
APPEND를 통해 테이블 데이터 적재에 대한 UNDO를 최소화하고 인덱스를 UNUSABLE 상태로 변경해 인덱스 갱신도 수행하지 않았기 때문에 가장 적은 UNDO를 사용함

 

 

테스트 결과 비교

테스트 조건 UNDO 사용량 기준 대비 감소율
테스트 1 일반 INSERT + 인덱스 유지 18.53MB 기준값
테스트 2 인덱스 UNUSABLE + 일반 INSERT 0.72MB 약 96.1%
테스트 3 인덱스 제거 + 일반 INSERT 0.72MB 약 96.1%
테스트 4 NOLOGGING + 일반 INSERT 18.53MB 0%
테스트 5 NOLOGGING + APPEND + 인덱스 유지 14.09MB 약 24.0%
테스트 6 NOLOGGING + APPEND + 인덱스 UNUSABLE 0.01MB 약 99.9%

 

 

결론 :
본문 테스트와 같이 대량 INSERT 시 UNDO 사용량을 줄이기 위해선 테이블 nologging과 append 힌트, 그리고 index의 unusable 상태로 해놓는것이 좋음(테이블 nologging은 undo 사용량을 줄일순 없지만 redo 발생량을 감소시켜 성능에 도움이 됨)
만약 이관시 초기테이터를 넣거나 초기이관등을 할때 undo 에러가 발생한다면(ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1') 본문 방식을 적용해보면 도움이 될 수 있음
이후 데이터 적재가 완료된 후 인덱스를 REBUILD하는 방법이 가장 효과적임
다만 NOLOGGING을 사용한 작업은 미디어 복구와 백업에 영향을 줄 수 있으므로 운영 환경에서는 FORCE LOGGING 설정과 백업 정책을 함께 확인해야 함

 

 

참조 : 

오라클 19c insert append, append_values 힌트 속도 비교 테스트 ( https://positivemh.tistory.com/851 )
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' ( https://positivemh.tistory.com/377 )
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-E9EB5D85-49D8-4A29-9DF3-A7CBBA484EE3
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/logging_clause.html