OS환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c DML 시 REDO 발생량 확인(nologging, append, parallel 등)
오라클에서 dml을 실행할 때 어떤 경우에 redo 발생량이 줄어드는지 확인해봄
테이블 logging 모드와 아카이브모드, append 힌트, parallel 힌트 등을 사용해 테스트해봄
테스트(1~6 노아카이브모드, 7 아카이브모드)
1. nologging 테이블 insert 테스트
insert 시 append 힌트 미사용
insert 시 append 힌트 사용
2. nologging 테이블 update 테스트
update 시 parallel 힌트 미사용
update 시 parallel 힌트 사용
3. nologging 테이블 delete 테스트
delete 시 parallel 힌트 미사용
delete 시 parallel 힌트 사용
4. logging 테이블 insert 테스트
insert 시 append 힌트 미사용
insert 시 append 힌트 사용
5. logging 테이블 update 테스트
update 시 parallel 힌트 미사용
update 시 parallel 힌트 사용
6. logging 테이블 delete 테스트
delete 시 parallel 힌트 미사용
delete 시 parallel 힌트 사용
7. 아카이브모드에서 dml 테스트(결과만)
8. 결론
테스트
샘플 테이블 생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
테이블 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from big_emp;
COUNT(*)
----------
41370
|
테이블 삭제 및 생성 스크립트를 re.sql로 저장(테스트 시 사용예정)
|
1
2
3
4
|
$ cat re.sql
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
select count(*) from big_emp;
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
1. nologging 테이블 insert 테스트
insert 시 append 힌트 미사용
|
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
|
SQL>
set autot on
insert into big_emp select * from big_emp;
41370 rows created.
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 41370 | 1535K| 72 (2)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | BIG_EMP | | | | |
| 2 | TABLE ACCESS FULL | BIG_EMP | 41370 | 1535K| 72 (2)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
27 recursive calls
2021 db block gets
340 consistent gets
0 physical reads
2130868 redo size
195 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
41370 rows processed
|
Statistics 의 redo size 부분을 보면 redo가 약 2MB(2130868 byte) 발생했음을 확인할 수 있음
리두 생성량 확인(redo.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
2171.37 2.12
|
2.12MB 발생함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
insert 시 append 힌트 사용
|
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
|
SQL>
set autot on
insert /*+ append */ into big_emp select * from big_emp;
41370 rows created.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
147 recursive calls
352 db block gets
486 consistent gets
0 physical reads
13124 redo size
179 bytes sent via SQL*Net to client
383 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
41370 rows processed
|
append 힌트 사용으로 인해 autotrace 실행계획은 제대로 나오지 않지만
Statistics 의 redo size 부분을 보면 redo가 약 13KB(13124 byte) 발생했음을 확인할 수 있음
리두 생성량 확인(redo.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
13.48 .01
|
13KB 발생함
nologging 테이블 insert 테스트 결론 :
append 미사용 시 redo 발생량 : 약 2MB
append 사용 시 redo 발생량 : 13KB
nologging 테이블에 append 힌트를 사용해 insert 를 하는 경우 redo 발생량이 많이 줄어듬
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
2. nologging 테이블 update 테스트
update 시 parallel 힌트 미사용
|
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
|
SQL>
set autot on
update big_emp set empno = 9999;
41370 rows updated.
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 41370 | 161K| 72 (2)| 00:00:01 |
| 1 | UPDATE | BIG_EMP | | | | |
| 2 | TABLE ACCESS FULL| BIG_EMP | 41370 | 161K| 72 (2)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
11 recursive calls
3799 db block gets
254 consistent gets
0 physical reads
4811820 redo size
195 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
41370 rows processed
|
Statistics 의 redo size 부분을 보면 redo가 약 4MB(4811820 byte) 발생했음을 확인할 수 있음
리두 생성량 확인(redo.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
4702.79 4.59
|
4.59MB 발생함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
update 시 parallel 힌트 사용
|
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
|
SQL>
alter session enable parallel dml;
set autot on
update /*+ parallel(big_emp) */ big_emp set empno = 9999;
41370 rows updated.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
11 recursive calls
13 db block gets
15 consistent gets
0 physical reads
1344 redo size
195 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
41370 rows processed
|
parallel 힌트 사용으로 인해 autotrace 실행계획은 제대로 나오지 않지만
Statistics 의 redo size 부분을 보면 redo가 1344 byte 발생했다고 표시됨
리두 생성량 확인(redo.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
1.96 0
|
1.96KB 발생함, 이 값에서도 redo가 적게 발생한것으로 나옴
이 값만 보고 redo 발생량을 판단하면 안됨
parallel 명령 수행시 세션이 여러개 만들어지면서 stat 정보도 각 세션별로 발생하기때문에
v$mystat이 아닌 v$px_sesstat를 확인해야함
리두 생성량 재확인(parallel 세션 전체)(redop.sql)
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select round(sum(vps.value)/1024, 2) "REDO_KB", round(sum(vps.value)/1024/1024, 2) "REDO_MB"
from v$statname vs, v$px_sesstat vps
where vs.statistic# = vps.statistic#
and vs.name = 'redo size'
and vps.qcsid = (select sid from v$mystat where rownum <=1);
REDO_KB REDO_MB
---------- ----------
4705.59 4.6
|
4.6MB 발생함
parallel 세션들의 redo를 모두 합해보니 parallel을 미사용 했을때와 redo 발생량이 비슷함(거의동일)
nologging 테이블 update 테스트 결론 :
parallel 미사용 시 redo 발생량 : 약 4.6MB
parallel 사용 시 redo 발생량 : 약 4.6MB
nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 update 를 하는 경우 redo 발생량이 동일함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
3. nologging 테이블 delete 테스트
delete 시 parallel 힌트 미사용
|
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
|
SQL>
set autot on
delete big_emp;
41370 rows deleted.
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 41370 | 72 (2)| 00:00:01 |
| 1 | DELETE | BIG_EMP | | | |
| 2 | TABLE ACCESS FULL| BIG_EMP | 41370 | 72 (2)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
3 recursive calls
986 db block gets
251 consistent gets
0 physical reads
2023416 redo size
195 bytes sent via SQL*Net to client
342 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
41370 rows processed
|
Statistics 의 redo size 부분을 보면 redo가 약 2MB(2023416 byte) 발생했음을 확인할 수 있음
리두 생성량 확인(redo.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
1979.7 1.93
|
1.93MB 발생함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
delete 시 parallel 힌트 사용
|
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
|
SQL>
alter session enable parallel dml;
set autot on
delete /*+ parallel(big_emp) */ big_emp;
41370 rows deleted.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
3 recursive calls
6 db block gets
12 consistent gets
0 physical reads
344 redo size
195 bytes sent via SQL*Net to client
367 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
41370 rows processed
|
parallel 힌트 사용으로 인해 autotrace 실행계획은 제대로 나오지 않지만
Statistics 의 redo size 부분을 보면 redo가 약 344 byte 발생했다고 표시됨
리두 생성량 확인(parallel 세션 전체)(redop.sql)
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select round(sum(vps.value)/1024, 2) "REDO_KB", round(sum(vps.value)/1024/1024, 2) "REDO_MB"
from v$statname vs, v$px_sesstat vps
where vs.statistic# = vps.statistic#
and vs.name = 'redo size'
and vps.qcsid = (select sid from v$mystat where rownum <=1);
REDO_KB REDO_MB
---------- ----------
12771.34 12.47
|
12.47MB 발생함
parallel 세션들의 redo를 모두 더해보니 parallel을 미사용 했을때보다 redo가 더 많이 발생함
nologging 테이블 delete 테스트 결론 :
parallel 미사용 시 redo 발생량 : 약 2MB
parallel 사용 시 redo 발생량 : 약 12MB
nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 delete 를 하는 경우 redo 발생량이 더 증가함
nologging 테이블 dml 종합 결론 :
insert : nologging 테이블에 append 힌트를 사용해 insert 를 하는 경우 redo 발생량이 많이 줄어듬(미사용시:2MB, 사용시:13KB)
update : nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 update 를 하는 경우 redo 발생량이 동일함(미사용시:4.6MB, 사용시:4.6MB)
delete : nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 delete 를 하는 경우 redo 발생량이 더 증가함(미사용시:2MB, 사용시:12MB)
nologging 여부가 관계 있는지 테이블을 logging 로 변경한 뒤(또는 logging 으로 재생성) 재 테스트
테이블 삭제 및 생성 스크립트의 nologging을 logging로 수정
|
1
2
3
4
|
$ cat re.sql
drop table big_emp purge;
create table big_emp logging as select * from emp connect by level <= 4;
select count(*) from big_emp;
|
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
4. logging 테이블 insert 테스트
insert 시 append 힌트 미사용
|
1
2
3
4
5
6
7
|
SQL>
insert into big_emp select * from big_emp;
SQL> @redo
REDO_KB REDO_MB
---------- ----------
2081.63 2.03
|
2.03MB 발생함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
insert 시 append 힌트 사용
|
1
2
3
4
5
6
|
SQL> insert /*+ append */ into big_emp select * from big_emp;
SQL> @redo
REDO_KB REDO_MB
---------- ----------
13.39 .01
|
13KB 발생함
logging 테이블 insert 테스트 결론 :
append 미사용 시 redo 발생량 : 2MB
append 사용 시 redo 발생량 : 13KB
logging 테이블에 append 힌트를 사용해 insert 를 하는 경우 redo 발생량이 많이 줄어듬
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
5. logging 테이블 update 테스트
update 시 parallel 힌트 미사용
|
1
2
3
4
5
6
|
SQL> update big_emp set empno = 9999;
SQL> @redo
REDO_KB REDO_MB
---------- ----------
4699.7 4.59
|
4.59MB 발생함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
update 시 parallel 힌트 사용
|
1
2
3
4
5
6
7
8
|
SQL>
alter session enable parallel dml;
update /*+ parallel(big_emp) */ big_emp set empno = 9999;
SQL> @redop
REDO_KB REDO_MB
---------- ----------
4701.71 4.59
|
4.59MB 발생함
logging 테이블 update 테스트 결론 :
parallel 미사용 시 redo 발생량 : 4.59MB
parallel 사용 시 redo 발생량 : 4.59MB
logging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 update 를 하는 경우 redo 발생량이 동일함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
6. logging 테이블 delete 테스트
delete 시 parallel 힌트 미사용
|
1
2
3
4
5
6
|
SQL> delete big_emp;
SQL> @redo
REDO_KB REDO_MB
---------- ----------
1976.5 1.93
|
1.93MB 발생함
테이블 재생성 스크립트 실행
|
1
|
SQL> re.sql
|
정확한 리두 생성량 측정을 위해 sqlplus 재접속
delete 시 parallel 힌트 사용
|
1
2
3
4
5
6
7
8
|
SQL>
alter session enable parallel dml;
delete /*+ parallel(big_emp) */ big_emp;
SQL> @redop
REDO_KB REDO_MB
---------- ----------
12771.98 12.47
|
12.47MB 발생함
logging 테이블 delete 테스트 결론 :
parallel 미사용 시 redo 발생량 : 2MB
parallel 사용 시 redo 발생량 : 12MB
logging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 delete 를 하는 경우 redo 발생량이 더 증가함
logging 테이블 dml 종합 결론 :
insert : logging 테이블에 append 힌트를 사용해 insert 를 하는 경우 redo 발생량이 많이 줄어듬(미사용시:2MB, 사용시:13KB)
update : logging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 update 를 하는 경우 redo 발생량이 동일함(미사용시:4.59MB, 사용시:4.59MB)
delete : logging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 delete 를 하는 경우 redo 발생량이 더 증가함(미사용시:2MB, 사용시:12MB)
참고용
nologging 테이블 dml 종합 결론 :
insert : nologging 테이블에 append 힌트를 사용해 insert 를 하는 경우 redo 발생량이 많이 줄어듬(미사용시:2MB, 사용시:13KB)
update : nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 update 를 하는 경우 redo 발생량이 동일함(미사용시:4.6MB, 사용시:4.6MB)
delete : nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 delete 를 하는 경우 redo 발생량이 더 증가함(미사용시:2MB, 사용시:12MB)
추가로 현재는 노아카이브 모드인데 아카이브 모드에서도 동일한 테스트를 진행해봄
이부분은 결과만 작성하겠음
아카이브모드에서 nologging 테이블 dml 종합 결론 :
insert : nologging 테이블에 append 힌트를 사용해 insert 를 하는 경우 redo 발생량이 많이 줄어듬(미사용시:2MB, 사용시:13KB)
update : nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 update 를 하는 경우 redo 발생량이 동일함(미사용시:4.6MB, 사용시:4.6MB)
delete : nologging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 delete 를 하는 경우 redo 발생량이 더 증가함(미사용시:2MB, 사용시:12MB)
아카이브모드에서 logging 테이블 dml 종합 결론 :
insert : logging 테이블에 append 힌트를 사용해 insert 를 하는 경우 redo 발생량이 비슷함(미사용시:2MB, 사용시:1.93MB)
update : logging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 update 를 하는 경우 redo 발생량이 동일함(미사용시:4.59MB, 사용시:4.59MB)
delete : logging 테이블에 parallel 힌트를 사용해 parallel + direct path 방식으로 delete 를 하는 경우 redo 발생량이 더 증가함(미사용시:2MB, 사용시:12MB)
결론 :
조금 복잡하지만 요약을 해보자면 아래와 같음
1. 노아카이브모드에서 테이블 logging 옵션 관계 없이 append 힌트를 사용해 insert 시 redo 발생량이 줄어듬
2. 아카이브모드에서는 테이블 nologging 옵션 사용중인 경우에만 append 힌트를 사용해 insert 시 redo 발생량이 줄어듬
3. 아카이브모드나 테이블 logging 옵션, parallel 힌트 사용 유무 관계 없이 update 시 redo 발생량이 동일함
4. 아카이브모드나 테이블 logging 옵션 관계 없이 parallel 힌트를 사용해 delete 시 redo 발생량이 증가함
오라클 공식 문서에서 요약 1번에 대한 내용을 찾을수 있었음(노아카이브모드 direct path insert)
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html
Direct-path INSERT generates both redo and undo for metadata changes, because these are needed for operation recovery. For data changes, undo and redo are generated as follows:
direct path INSERT는 메타데이터 변경 사항에 대해 redo 및 undo를 모두 생성합니다. 이는 작업 복구에 필요하기 때문입니다. 데이터 변경의 경우 다음과 같이 redo 및 undo가 생성됩니다.
If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table.
데이터베이스가 ARCHIVELOG 또는 FORCE LOGGING 모드가 아닌 경우 테이블의 로깅 설정에 관계없이 데이터 변경에 대한 리두가 생성되지 않습니다.
오라클 공식 문서에서 요약 2번에 대한 내용을 찾을수 있었음(아카이브모드 direct path insert)
If the database is in ARCHIVELOG mode (but not in FORCE LOGGING mode), then direct-path INSERT generates data redo for LOGGING tables but not for NOLOGGING tables.
데이터베이스가 ARCHIVELOG 모드인 경우(FORCE LOGGING 모드는 아님) direct path INSERT는 LOGGING 테이블에 대해서는 데이터 리두를 생성하지만 NOLOGGING 테이블에 대해서는 데이터 리두를 생성하지 않습니다.
요약 4번에 대한 내용은 따로 찾을수 없었음
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/parallel-exec-tips.html#GUID-4EE9408B-C03A-4842-9F24-C24EA2A3E704
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-AE2F93BE-FC46-43F7-A29B-173FE219DD24
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/monitor-parallel-performance.html#GUID-2BBBDAFF-7AB8-41E0-B570-73741E4D4B90
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 체크포인트 정리 (0) | 2024.04.07 |
|---|---|
| 오라클 클라우드(OCI) 무료 계정 생성 가이드 (2) | 2024.04.06 |
| 오라클 19c 스탠다드 에디션, 엔터프라이즈 에디션 기능 차이 테스트 (0) | 2024.03.28 |
| 오라클 19c PL/SQL 의 Xplan 실행계획 확인 (0) | 2024.03.25 |
| 오라클 19c DBMS_APPLICATION_INFO 클라이언트 정보 설정 (0) | 2024.03.25 |