오라클 19c Insert 시 Undo TS Autoextend 옵션에 따른 성능 차이 분석

OS 환경 : Oracle Linux 8.1 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c Insert 시 Undo TS Autoextend 옵션에 따른 성능 차이 분석

오라클에서는 8k 블록을 사용하는 경우 데이터파일 크기를 32gb까지 확장시킬 수 있음
처음부터 수동으로 32gb 또는 수십gb로 설정해둘수도 있고, 초기에는 사이즈를 작게 할당한 다음 autoextend 를 on으로 설정해서 99%에서 자동으로 next extent 만큼 늘어나게 할수도 있음

 

 

지난 게시글에서 일반 테이블스페이스에 autoextend 옵션을 각각 다르게 하여 insert 하는 성능테스트를 해보았었음

참고 : 오라클 19c Insert 시 Autoextend 옵션에 따른 성능 차이 분석 ( https://positivemh.tistory.com/1193 )

 

이번 게시글에서는 일반 테이블스페이스는 용량을 많이 할당해놓고, undo가 부족해서 autoextend될때 성능차이가 얼마나 나는지 확인해봄

본문에서는 1mb 크기인 undo 데이터파일로 성능테스트를 해보고, 동일한 테스트를 3gb 크기인 undo  데이터파일에서도 테스트해봄
점차 많은 데이터를 삽입하면서 autoextend로 인해 성능저하가 얼마나 발생하는지 확인해봄

 

 

테스트

case1. undo 크기가 작아서 insert 작업시 autoextend 될때(test_tbl1 테이블)

case2. undo 크기가 적당해서 insert 작업시 가용공간을 사용할때 (test_tbl2 테이블)

 

 

테스트

먼저 redo의 영향을 받지 않게 하기 위해 redo log 크기를 2gb로 크게 변경함
참고 : 오라클 19c redo log 크기 자동 변경 스크립트 ( https://positivemh.tistory.com/1047 )

 

 

case1. undo 크기가 작아서 insert 작업시 autoextend 될때

undo 테이블스페이스 재생성을 통한 크기 감소

SQL> create temporary tablespace temp2 tempfile '/oradata1/ORACLE19/temp02.dbf' size 10m autoextend on next 1m;
SQL> alter database default temporary tablespace temp2;
SQL> create undo tablespace undotbs2 datafile '/oradata1/ORACLE19/undotbs02.dbf' size 10m autoextend on next 1m;
SQL> alter system set undo_tablespace='UNDOTBS2';

undo 테이블스페이스는 초기 사이즈가 10mb이고, 99%에서 1mb씩 늘어남
참고 : 오라클 11g R2 Temp Tablespace 재생성 ( https://positivemh.tistory.com/668 )

참고 : 오라클 undo tablespace 재생성 ( https://positivemh.tistory.com/495 )

 

 

데이터파일들 확인

SQL>
set lines 200 pages 1000col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files
where tablespace_name = 'UNDOTBS2'
union all
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, null from dba_temp_files 
where tablespace_name = 'TEMP2'
order by 2;
 
TABLESPACE_NAME         FILE_ID FILE_NAME                                                                      GB     MAX_GB AUT STATUS    ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
TEMP2                         2 /oradata1/ORACLE19/temp02.dbf                                                 .01         32 YES ONLINE
UNDOTBS2                      8 /oradata1/ORACLE19/undotbs02.dbf                                              .01         32 YES AVAILABLE ONLINE

정상적으로 생성됨

 

 

초기 데이터 삽입

SQL>
set timing on
create table test_tbl1 as select * from dba_objects;

 

 

테스트 데이터 삽입

SQL>
-----1
insert into test_tbl1 select * from test_tbl1;
 
-----2
insert into test_tbl1 select * from test_tbl1;
 
-----3
insert into test_tbl1 select * from test_tbl1;
 
-----4
insert into test_tbl1 select * from test_tbl1;
 
-----5
insert into test_tbl1 select * from test_tbl1;
 
-----6
insert into test_tbl1 select * from test_tbl1;
 
-----7
insert into test_tbl1 select * from test_tbl1;

데이터를 모두 삽입함
본문처럼 테스트를 해보고 싶은경우 순서대로 하나씩 복사 붙여넣기 해서 실행해보는걸 추천함

 

 

insert시 alert log 확인

$ tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2025-03-02T17:02:07.399944+09:00
Resize operation completed for file# 8, old size 28672K, new size 29696K
Resize operation completed for file# 8, old size 29696K, new size 30720K
Resize operation completed for file# 8, old size 30720K, new size 31744K
2025-03-02T17:02:08.851886+09:00
Resize operation completed for file# 8, old size 31744K, new size 32768K
Resize operation completed for file# 8, old size 32768K, new size 33792K
..

undo 테이블스페이스의 데이터파일인 8번 파일이 계속 자동으로 resize 되고 있음

 

 

case2. undo 크기가 적당해서 insert 작업시 가용공간을 사용할때

기존 테이블 삭제

SQL> drop table test_tbl1 purge;
 
Table dropped.

 

 

undo 크기를 3gb로 resize

SQL> alter database datafile 8 resize 3g;
 
Database altered.

 

 

초기 데이터 삽입

SQL>
set timing on
create table test_tbl2 as select * from dba_objects;

 

 

테스트 데이터 삽입

SQL>
-----1
insert into test_tbl2 select * from test_tbl2;
 
-----2
insert into test_tbl2 select * from test_tbl2;
 
-----3
insert into test_tbl2 select * from test_tbl2;
 
-----4
insert into test_tbl2 select * from test_tbl2;
 
-----5
insert into test_tbl2 select * from test_tbl2;
 
-----6
insert into test_tbl2 select * from test_tbl2;
 
-----7
insert into test_tbl2 select * from test_tbl2;

데이터를 모두 삽입함
본문처럼 테스트를 해보고 싶은경우 순서대로 하나씩 복사 붙여넣기 해서 실행해보는걸 추천함

 

 

insert시 alert log 에 resize 로그 발생하지 않음

 

 

insert 시간 결과 표

insert 회차	insert 한 row 수	insert 소요 시간(test_tbl1)	insert 소요 시간(test_tbl2)
1	72,813	0.08	0.09
2	145,626	0.14	0.14
3	291,252	0.27	0.27
4	582,504	0.53	0.53
5	1,165,008	1.11	1.09
6	2,330,016	2.23	2.30
7	4,660,032	6.39	4.69

test_tbl2 테이블 insert시 undo 데이터파일 autoextend로 인해 test_tbl1 테이블 insert보다 느림

 

 

insert 시간 결과 그래프

 

 

결론 :
test_tbl2 테이블 insert시 undo 데이터파일의 autoextend로 인해 test_tbl1 테이블 insert보다 느림을 확인할 수 있음
undo 테이블스페이스가 자동으로 resize 되면서 성능에 영향을 주기 때문에 충분한 크기로 설정해두는것이 좋음

성능도 중요한 테이블스페이스라면 테이블스페이스 사용량이 99%가 될때까지 계속 두는것보다는 80%이하로 표시될수 있게끔 데이터파일을 수동으로 resize 해줘서 99%가 되지 않게 해주는게 좋음

 

 

참조 : 

https://cafe.naver.com/prodba/56051

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_DATA_FILES.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-data-files-and-temp-files.html#GUID-C633A6CC-F1A9-415A-B153-5C2053BBB7E6

https://positivemh.tistory.com/495

https://positivemh.tistory.com/668