프린트 하기

OS 환경 : Oracle Linux 8.1 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

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

오라클에서는 8k 블록을 사용하는 경우 데이터파일 크기를 32gb까지 확장시킬 수 있음
처음부터 수동으로 32gb 또는 수십gb로 설정해둘수도 있고, 초기에는 사이즈를 작게 할당한 다음 autoextend 를 on으로 설정해서 99%에서 자동으로 next extent 만큼 늘어나게 할수도 있음
본문에서는 1mb 크기인 데이터파일과 2gb 크기인 데이터파일을 가진 테이블스페이스를 각각 생성한뒤
점차 많은 데이터를 삽입하면서 autoextend로 인해 성능저하가 얼마나 발생하는지 확인해봄

 

 

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

 

 

샘플 테이블스페이스 생성

1
2
3
4
5
SQL>
drop tablespace testts1 including contents and datafiles;
drop tablespace testts2 including contents and datafiles;
create tablespace testts1 datafile '/oradata1/ORACLE19/testts01.dbf' size 1m autoextend on next 1m;
create tablespace testts2 datafile '/oradata1/ORACLE19/testts02.dbf' size 2g autoextend on next 100m;

testts1 테이블스페이스는 초기 사이즈가 1mb이고, 99%에서 1mb씩 늘어남
testts2 테이블스페이스는 초기 사이즈가 2gb이고, 99%에서 100mb씩 늘어남

 

 

초기 데이터 삽입

1
2
3
4
SQL>
set timing on
create table test_tbl1 tablespace testts1 as select * from dba_objects;
create table test_tbl2 tablespace testts2 as select * from dba_objects;

 

 

테스트 데이터 삽입

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
SQL>
-----1
insert into test_tbl1 select * from test_tbl1;
commit;
insert into test_tbl2 select * from test_tbl2;
commit;
 
-----2
insert into test_tbl1 select * from test_tbl1;
commit;
insert into test_tbl2 select * from test_tbl2;
commit;
 
-----3
insert into test_tbl1 select * from test_tbl1;
commit;
insert into test_tbl2 select * from test_tbl2;
commit;
 
-----4
insert into test_tbl1 select * from test_tbl1;
commit;
insert into test_tbl2 select * from test_tbl2;
commit;
 
-----5
insert into test_tbl1 select * from test_tbl1;
commit;
insert into test_tbl2 select * from test_tbl2;
commit;
 
-----6
insert into test_tbl1 select * from test_tbl1;
commit;
insert into test_tbl2 select * from test_tbl2;
commit;

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

 

 

insert시 alert log 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2025-01-25T20:32:35.209514+09:00
Resize operation completed for file# 2, old size 20480K, new size 21504K
2025-01-25T20:32:35.236165+09:00
Resize operation completed for file# 2, old size 21504K, new size 22528K
2025-01-25T20:32:35.240631+09:00
Resize operation completed for file# 2, old size 22528K, new size 23552K
2025-01-25T20:32:35.255933+09:00
Resize operation completed for file# 2, old size 23552K, new size 24576K
2025-01-25T20:32:35.260059+09:00
Resize operation completed for file# 2, old size 24576K, new size 25600K
2025-01-25T20:32:39.624877+09:00
Resize operation completed for file# 2, old size 25600K, new size 26624K
...

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

 

 

테이블스페이스 사용량 확인

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
SQL>
set lines 200 pages 1000
col "TOTAL SIZE" format 999,999,999,999
col "USED SIZE" format 999,999,999,999
col "FREE SIZE" format 999,999,999,999
col "MAX FREE" format 999,999,999,999
col "TS Name" for a20
col "Used rate" for a10
select t.tn  "TS Name",
       round(t.sizes,2) "TOTAL SIZE",
       (round(t.sizes,2) - round(f.sizes,2))"USED SIZE",
       round(f.sizes,2) "FREE SIZE",
       round(f.msizes,2) "Max Free",
       round(((t.sizes - f.sizes) /t.sizes) * 100,2)||'%' "Used rate"
 from ( select tablespace_name tn, SUM(bytes) Sizes
        from dba_data_files
        group by tablespace_name) t,
      (select tablespace_name tn, SUM(bytes) sizes,
              MAX(bytes) msizes
       from dba_free_space
       group by tablespace_name) f
      where t.tn=f.tn
  and t.tn like 'TESTTS%'
      order by "Used rate" desc;
 
TS Name                    TOTAL SIZE        USED SIZE        FREE SIZE         Max Free Used rate
-------------------- ---------------- ---------------- ---------------- ---------------- ----------
TESTTS1                   775,946,240      738,263,040       37,683,200       36,700,160 95.14%
TESTTS2                 2,147,483,648      739,246,080    1,408,237,568    1,408,237,568 34.42%

현재 testts1은 95%이고 testts2는 34%임

 

 

insert 시간 결과 표

insert 회차 insert 한 row 수 insert 소요 시간(testts1) insert 소요 시간(testts2)
1 72,387 0.16 0.07
2 144,774 0.23 0.14
3 289,548 0.41 0.28
4 579,096 0.76 0.55
5 1,158,192 1.32 1.13
6 2,316,384 4.02 3.85

testts1 테이블 insert시 autoextend로 인해 testts2 테이블 insert보다 느림

 

 

insert 시간 결과 그래프

 

 

결론 :
testts1 테이블 insert시 autoextend로 인해 testts2 테이블 insert보다 느림을 확인할 수 있음
성능에 민감하지 않고 사용량이 적은 테이블스페이스의 경우 99%에서 자동으로 32gb까지 확장가능하게 해놓아도 괜찮지만

성능도 중요한 테이블스페이스라면 테이블스페이스 사용량이 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

 

저작자표시

'ORACLE > Performance Tuning ' 카테고리의 다른 글

오라클 19c xplan(display_cursor) 실행계획 확인 방법  (0) 2025.01.20
오라클 데이터베이스 무료 분석 툴 SQLdb360 사용법  (0) 2024.12.17
오라클 19c 테이블 ctas 생성 및 append 삽입시, 인덱스 생성시 자동 통계정보 수집  (0) 2024.10.24
오라클 23ai append_values 힌트를 사용한 벌크 인서트 성능 비교  (0) 2024.07.09
오라클 19c hash 조인 과다 실행시 부하 테스트  (0) 2024.05.28

티스토리툴바