프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

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