[제목]
[2025-11-06] 테이블/LOB NOLOGGING 설정 시 REDO 로그 최소화 효과 검증 (12cR2 RAC)
[테스트 개요]
목적: 테이블 및 LOB 컬럼에 대한 NOLOGGING/LOGGING 조합과 APPEND/PDML/CTAS 방식이 REDO 생성량에 미치는 영향을 정량 확인.
환경: Oracle Linux 7.9 + 12.2.0.1 RAC(2노드), ORA12RF1/ORA12RF2. 패치 수준은 12.2.0.1.220118.
측정방법: 작업 전후 gv$sysstat의 redo size(MB) 차이를 수집하는 스크립트(st_redo.sql)로 REDO 증분 비교.
<테스트 결과>
Direct Path일 때만 NOLOGGING 효과가 있다. (APPEND 또는 PDML이면 REDO 최소화)
APPEND 없이도 PDML만 제대로 걸리면 Direct Path로 REDO 최소화된다.
Conventional Path(비APPEND+비병렬)는 테이블이 NOLOGGING이어도 REDO가 많이 발생한다.
SELECT만 병렬이고 DML이 단일이면 여전히 Conventional Path라 REDO가 많이 발생한다.
LOB은 테이블과 별개로 LOB 컬럼에 NOLOGGING을 지정해야 REDO가 최소화된다. (LOB이 LOGGING이면 테이블 NOLOGGING이어도 LOB REDO가 남는다)
CTAS NOLOGGING은 일반 컬럼 위주일 때 REDO가 거의 없고, CLOB 포함 CTAS도 REDO가 일부만 발생해 전체적으로 크게 줄어든다.
[테스트
환경]
OS : Oracle Linux Server 7.9 (grep ^PRETTY_NAME=
/etc/os-release | cut -d= -f2- | tr -d '"')
OS
Kernal : 5.4.17-2102.201.3.el7uek.x86_64 (uname -r)
Oracle Version : Oracle
Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (echo -e
"set pages 0 lines 200 feedback off heading off\nselect banner from v\$version
where banner like 'Oracle Database%';" | sqlplus -s / as sysdba)
Oracle
Configuration
DB명 : ORA12RF (echo -e 'set pages 0 feedback
off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' |
sqlplus -s / as sysdba) '
RAC Node
1
Hostname : ol7ora12rf1
(hostname)
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12RF1 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname :
ol7ora12rf2
Public IP : 192.168.240.12 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12RF2 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
33610989;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:RELEASE)
(33610989)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913)
(26839277)
33116894;ACFS JUL 2021 RELEASE UPDATE 12.2.0.1.210720
(33116894)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
Oracle
(opatch lspatches) (oracle os user)
33678030;OCW JAN 2022 RELEASE
UPDATE 12.2.0.1.220118 (33678030)
33587128;Database Jan 2022
Release Update : 12.2.0.1.220118 (33587128)
[내용]
1. 테이블 NOLOGGING 시 REDO LOG 최소화 여부 테스트
1-1. 테스트 테이블 생성
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 5 23:35:06 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$
DROP TABLE
TUNER.TB_CUST_REDO_TEST PURGE;
CREATE TABLE
TUNER.TB_CUST_REDO_TEST
(
CUST_NO VARCHAR2(10) NOT NULL
,
CUST_ID VARCHAR2(20) NOT NULL
, CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY
VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
, JOIN_DT VARCHAR2(14) NOT
NULL
, CUST_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT
NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
TABLESPACE TUNER_DATA1;
--redo size를 측정하는 sql 스크립트
생성
[ol7ora12rf1]<SYS@ORA12RF1>$ host vi
st_redo.sql
[ol7ora12rf1]<SYS@ORA12RF1>$ host cat st_redo.sql
SET LINESIZE
150
SET PAGESIZE 50
SET VERIFY OFF
SET FEEDBACK ON
SET NUMWIDTH
12
COLUMN inst_id HEADING
'INST' FORMAT 999
COLUMN
name HEADING 'STAT_NAME' FORMAT
A25
COLUMN s.name HEADING 'INSTANCE'
FORMAT A15
COLUMN redo_mb HEADING 'REDO SIZE(MB)' FORMAT
999,999,990.00
SELECT s.inst_id
,
n.name
,
s.name
, round(s.value/1024/1024, 2) as
redo_mb
FROM gv$sysstat s
JOIN v$statname
n
ON n.statistic# = s.statistic#
WHERE n.name =
'redo size'
ORDER BY s.inst_id;
1-2. TABLE NOLOGGING + APPEND INSERT + NO PARALLEL DML
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
359.55
2 redo
size
redo
size
922.20
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER TABLE TUNER.TB_CUST_REDO_TEST
NOLOGGING;
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT /*+ APPEND */
INTO
TUNER.TB_CUST_REDO_TEST
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS
CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
1000000;
*************************[Explain Plan Time: 2025/11/06
18:32:29]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 LOAD AS SELECT OF
'TB_CUST_REDO_TEST'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=1000000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ commit;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
359.84
2 redo
size
redo
size
922.22
--> redo
log가 거의 남지 않음
--> table nolgging 설정의 효과를 본 것임, append만 쓰면 paralel dml이 아니여도 redo log 최소화 가능한 것을 증명한 것임!
1-3.
TABLE NOLOGGING + APPEND INSERT + PARALLEL
DML
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
359.87
2 redo
size
redo
size
922.22
[ol7ora12rf1]<SYS@ORA12RF1>$ alter table TUNER.TB_CUST_REDO_TEST nologging;
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT /*+
ENABLE_PARALLEL_DML APPEND PARALLEL(4) */
INTO
TUNER.TB_CUST_REDO_TEST
SELECT /*+ PARALLEL(4) */ 'C' || LPAD(ROWNUM,
9, '0') AS CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
1000000;
;
*************************[Explain Plan
Time: 2025/11/06 18:39:57]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 PX
COORDINATOR
2 1 PX
SEND (QC (RANDOM)) OF 'SYS.:TQ10001' (Cost=2 Card=1) (PARALLEL_TO_SERIAL) (QC
(RANDOM))
3
2 LOAD AS SELECT ((HYBRID TSM/HWMB)) OF
'TB_CUST_REDO_TEST' (PARALLEL_COMBINED_WITH_PARENT) --> PARALLEL DML이 제대로 되는
상황임
4
3 PX RECEIVE (Cost=2 Card=1)
(PARALLEL_COMBINED_WITH_PARENT)
5
4 PX SEND
(ROUND-ROBIN) OF 'SYS.:TQ10000' (Cost=2 Card=1) (PARALLEL_FROM_SERIAL)
(ROUND-ROBIN)
6
5
COUNT
7
6
CONNECT BY (WITHOUT FILTERING)
8
7
FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
7 -
filter(LEVEL<=1000000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ COMMIT;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
363.13
2 redo
size
redo
size
925.30
--> redo
log가 거의 남지 않음
--> nolgging 설정의 효과를 본 것임, APPEND 힌트도 기재했고 PARALLEL DML도 동작함
--> DIRECT PATH LOAD를 하기 때문에 REDO LOG가 최소화 되는 것을 확인함! (이건 당연한 결과임)
1-4. TABLE NOLOGGING + NOAPPEND INSERT + PARALLEL DML
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
363.16
2 redo
size
redo
size
925.31
[ol7ora12rf1]<SYS@ORA12RF1>$ alter table TUNER.TB_CUST_REDO_TEST
nologging;
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT /*+ ENABLE_PARALLEL_DML
PARALLEL(4) */
INTO TUNER.TB_CUST_REDO_TEST
SELECT /*+ PARALLEL(4)
*/ 'C' || LPAD(ROWNUM, 9, '0') AS CUST_NO
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
CUST_ID
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS CUST_NM
,
TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80),
'YYYYMMDD') AS BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2),
4, '0') AS SEX_CD
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
1000000;
;
*************************[Explain Plan Time: 2025/11/06
18:43:03]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 PX
COORDINATOR
2 1 PX
SEND (QC (RANDOM)) OF 'SYS.:TQ10001' (Cost=2 Card=1) (PARALLEL_TO_SERIAL) (QC
(RANDOM))
3
2 LOAD AS SELECT ((HYBRID TSM/HWMB)) OF
'TB_CUST_REDO_TEST' (PARALLEL_COMBINED_WITH_PARENT) --> PARALLEL DML이 제대로 되는
상황임
4
3 PX RECEIVE (Cost=2 Card=1)
(PARALLEL_COMBINED_WITH_PARENT)
5
4 PX SEND
(ROUND-ROBIN) OF 'SYS.:TQ10000' (Cost=2 Card=1) (PARALLEL_FROM_SERIAL)
(ROUND-ROBIN)
6
5
COUNT
7
6
CONNECT BY (WITHOUT FILTERING)
8
7
FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
7 -
filter(LEVEL<=1000000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ COMMIT;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
363.33
2 redo
size
redo
size
925.33
--> redo
log가 거의 남지 않음
--> APPEND 힌트를 기재하지 않아도 PARALLEL DML을 통한 DIRECT PATH LOAD만 유도된다면 REDO LOG를 최소화 시키는 것을 확인함!
1-5. TABLE NOLOGGING + NOAPPEND INSERT + NO PARALLEL DML
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
363.83
2 redo
size
redo
size
925.33
[ol7ora12rf1]<SYS@ORA12RF1>$ alter table TUNER.TB_CUST_REDO_TEST nologging;
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT
INTO TUNER.TB_CUST_REDO_TEST
SELECT 'C' ||
LPAD(ROWNUM, 9, '0') AS CUST_NO
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
CUST_ID
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS CUST_NM
,
TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80),
'YYYYMMDD') AS BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2),
4, '0') AS SEX_CD
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
1000000;
;
*************************[Explain Plan
Time: 2025/11/06 18:46:07]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 LOAD TABLE
CONVENTIONAL OF 'TB_CUST_REDO_TEST'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=1000000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ COMMIT;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
506.70 <-- redo log
발생
2 redo
size
redo
size
925.39
--> redo
log가 발생함
--> DIRECT PATH LOAD가 아닌 CONVENTIONAL INSERT를 하고 있음
--> REDO LOG 엄청 발생함 (TABLE NOLOGGING이어도 REDO LOG발생함)
--> 추후 해볼 테스트 (과연 이렇게 남은 REDO LOG로 백업/복구가 되는가???)
1-6. TABLE NOLOGGING + NOAPPEND INSERT + NO PARALLEL DML + SELECT는 PARALLEL
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
1,210.82
2 redo
size
redo
size
1,005.31
--> 테스트 시점이 달라서 갑자기 REDO SIZE가 커진것일분 해당 1-6 테스트 전의 REDO SIZE임
[ol7ora12rf1]<SYS@ORA12RF1>$ alter table TUNER.TB_CUST_REDO_TEST nologging;
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT
INTO
TUNER.TB_CUST_REDO_TEST
SELECT /*+ parallel(4) */ 'C' || LPAD(ROWNUM,
9, '0') AS CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
a.TIMESTAMP AS UPDT_ID
, a.STATUS ||
a.TEMPORARY || a.GENERATED || a.SECONDARY AS UPDT_DT
FROM DBA_OBJECTS a , (select level from dual CONNECT BY LEVEL <=
100)
;
*************************[Explain Plan Time: 2025/11/06
22:56:16]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=56
Card=184 Bytes=4K)
1 0
LOAD TABLE CONVENTIONAL OF
'TB_CUST_REDO_TEST'
2 1 COUNT
3 2 PX
COORDINATOR
4
3 PX SEND (QC (RANDOM)) OF
'SYS.:TQ70003' (Cost=56 Card=184 Bytes=4K) (PARALLEL_TO_SERIAL) (QC
(RANDOM))
5
4 MERGE JOIN
(CARTESIAN) (Cost=56 Card=184 Bytes=4K)
(PARALLEL_COMBINED_WITH_PARENT)
6
5 BUFFER
(SORT) (PARALLEL_COMBINED_WITH_CHILD)
7
6
PX RECEIVE (Cost=2 Card=1) (PARALLEL_COMBINED_WITH_PARENT)
8
7
PX SEND (BROADCAST) OF 'SYS.:TQ70000' (Cost=2 Card=1) (PARALLEL_FROM_SERIAL)
(BROADCAST)
9
8
VIEW (Cost=2 Card=1)
10
9
CONNECT BY (WITHOUT FILTERING)
11
10
FAST DUAL (Cost=2 Card=1)
12
5 BUFFER
(SORT) (Cost=56 Card=184 Bytes=4K) (PARALLEL_COMBINED_WITH_PARENT)
13
12
VIEW OF 'SYS.DBA_OBJECTS' (VIEW) (Card=184 Bytes=4K)
(PARALLEL_COMBINED_WITH_PARENT)
14
13
UNION-ALL (PARALLEL_COMBINED_WITH_PARENT)
15
14
BUFFER (SORT) (PARALLEL_COMBINED_WITH_CHILD)
16
15
PX RECEIVE (PARALLEL_COMBINED_WITH_PARENT)
17
16
PX SEND (ROUND-ROBIN) OF 'SYS.:TQ70001' (PARALLEL_FROM_SERIAL)
(ROUND-ROBIN)
18
17
FILTER
19
18
PX COORDINATOR
20
19
PX SEND (QC (RANDOM)) OF 'SYS.:TQ60003' (Cost=27 Card=198 Bytes=26K)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
21
20
HASH JOIN (BUFFERED) (Cost=27 Card=198 Bytes=26K)
(PARALLEL_COMBINED_WITH_PARENT)
22
21
HASH JOIN (Cost=26 Card=198 Bytes=25K) (PARALLEL_COMBINED_WITH_PARENT)
23
22
PX RECEIVE (Cost=1 Card=128 Bytes=3K) (PARALLEL_COMBINED_WITH_PARENT)
24
23
PX SEND (HASH) OF 'SYS.:TQ60000' (Cost=1 Card=128 Bytes=3K)
(PARALLEL_FROM_SERIAL) (HASH)
25
24
PX SELECTOR (SINGLE_COMBINED_WITH_CHILD)
26
25
INDEX (FULL SCAN) OF 'SYS.I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=128 Bytes=3K)
(SINGLE_COMBINED_WITH_PARENT)
27
22
PX RECEIVE (Cost=25 Card=198 Bytes=21K)
(PARALLEL_COMBINED_WITH_PARENT)
28
27
PX SEND (HASH) OF 'SYS.:TQ60001' (Cost=25 Card=198 Bytes=21K)
(PARALLEL_TO_PARALLEL) (HASH)
29
28
PX BLOCK (ITERATOR) (Cost=25 Card=198 Bytes=21K)
(PARALLEL_COMBINED_WITH_CHILD)
30
29
TABLE ACCESS (FULL) OF 'SYS.OBJ$' (TABLE) (Cost=25 Card=198 Bytes=21K)
(PARALLEL_COMBINED_WITH_PARENT)
31
21
PX RECEIVE (Cost=1 Card=128 Bytes=512) (PARALLEL_COMBINED_WITH_PARENT)
32
31
PX SEND (BROADCAST) OF 'SYS.:TQ60002' (Cost=1 Card=128 Bytes=512)
(PARALLEL_FROM_SERIAL) (BROADCAST)
33
32
PX SELECTOR (SINGLE_COMBINED_WITH_CHILD)
34
33
INDEX (FULL SCAN) OF 'SYS.I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=128 Bytes=512)
(SINGLE_COMBINED_WITH_PARENT)
35
18
PX COORDINATOR
36
35
PX SEND (QC (RANDOM)) OF 'SYS.:TQ10001' (Cost=3 Card=1 Bytes=31)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
37
36
NESTED LOOPS (Cost=3 Card=1 Bytes=31) (PARALLEL_COMBINED_WITH_PARENT)
38
37
NESTED LOOPS (Cost=2 Card=1 Bytes=19) (PARALLEL_COMBINED_WITH_PARENT)
39
38
TABLE ACCESS (BY INDEX ROWID) OF 'SYS.IND$' (CLUSTER) (Cost=2 Card=1 Bytes=10)
(PARALLEL_COMBINED_WITH_PARENT)
40
39
BUFFER (SORT) (PARALLEL_COMBINED_WITH_CHILD)
41
40
PX RECEIVE (Cost=1 Card=1) (PARALLEL_COMBINED_WITH_PARENT)
42
41
PX SEND (HASH (BLOCK ADDRESS)) OF 'SYS.:TQ10000' (Cost=1 Card=1)
(PARALLEL_FROM_SERIAL) (HASH (BLOCK ADDRESS))
43
42
PX SELECTOR (SINGLE_COMBINED_WITH_CHILD)
44
43
INDEX (UNIQUE SCAN) OF 'SYS.I_IND1' (INDEX (UNIQUE)) (Cost=1 Card=1)
(SINGLE_COMBINED_WITH_PARENT)
45
38
INDEX (RANGE SCAN) OF 'SYS.I_OBJ1' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=9)
(PARALLEL_COMBINED_WITH_PARENT)
46
37
TABLE ACCESS (CLUSTER) OF 'SYS.TAB$' (CLUSTER) (Cost=1 Card=1 Bytes=12)
(PARALLEL_COMBINED_WITH_PARENT)
47
18
TABLE ACCESS (CLUSTER) OF 'SYS.TAB$' (CLUSTER) (Cost=2 Card=1
Bytes=12)
48
47
INDEX (UNIQUE SCAN) OF 'SYS.I_OBJ#' (INDEX (CLUSTER)) (Cost=1 Card=1)
49
18
PX COORDINATOR
50
49
PX SEND (QC (RANDOM)) OF 'SYS.:TQ20001' (Cost=1 Card=1 Bytes=8)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
51
50
TABLE ACCESS (BY INDEX ROWID) OF 'SYS.SEQ$' (TABLE) (Cost=1 Card=1 Bytes=8)
(PARALLEL_COMBINED_WITH_PARENT)
52
51
BUFFER (SORT) (PARALLEL_COMBINED_WITH_CHILD)
53
52
PX RECEIVE (Cost=0 Card=1) (PARALLEL_COMBINED_WITH_PARENT)
54
53
PX SEND (HASH (BLOCK ADDRESS)) OF 'SYS.:TQ20000' (Cost=0 Card=1)
(PARALLEL_FROM_SERIAL) (HASH (BLOCK ADDRESS))
55
54
PX SELECTOR (SINGLE_COMBINED_WITH_CHILD)
56
55
INDEX (UNIQUE SCAN) OF 'SYS.I_SEQ1' (INDEX (UNIQUE)) (Cost=0 Card=1)
(SINGLE_COMBINED_WITH_PARENT)
57
18
PX COORDINATOR
58
57
PX SEND (QC (RANDOM)) OF 'SYS.:TQ30000' (Cost=2 Card=1 Bytes=6)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
59
58
PX BLOCK (ITERATOR) (Cost=2 Card=1 Bytes=6)
(PARALLEL_COMBINED_WITH_CHILD)
60
59
TABLE ACCESS (FULL) OF 'SYS.USER_EDITIONING$' (TABLE) (Cost=2 Card=1 Bytes=6)
(PARALLEL_COMBINED_WITH_PARENT)
61
18
PX COORDINATOR
62
61
PX SEND (QC (RANDOM)) OF 'SYS.:TQ40000' (Cost=2 Card=1 Bytes=6)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
63
62
PX BLOCK (ITERATOR) (Cost=2 Card=1 Bytes=6)
(PARALLEL_COMBINED_WITH_CHILD)
64
63
TABLE ACCESS (FULL) OF 'SYS.USER_EDITIONING$' (TABLE) (Cost=2 Card=1 Bytes=6)
(PARALLEL_COMBINED_WITH_PARENT)
65
18
PX COORDINATOR
66
65
PX SEND (QC (RANDOM)) OF 'SYS.:TQ50001' (Cost=1 Card=1 Bytes=29)
(PARALLEL_TO_SERIAL) (QC (RANDOM))
67
66
BUFFER (SORT) (Card=1 Bytes=29) (PARALLEL_COMBINED_WITH_PARENT)
68
67
NESTED LOOPS (Cost=1 Card=1 Bytes=29) (PARALLEL_COMBINED_WITH_PARENT)
69
68
PX RECEIVE (PARALLEL_COMBINED_WITH_PARENT)
70
69
PX SEND (ROUND-ROBIN) OF 'SYS.:TQ50000' (PARALLEL_FROM_SERIAL)
(ROUND-ROBIN)
71
70
PX SELECTOR (SINGLE_COMBINED_WITH_CHILD)
72
71
INDEX (SKIP SCAN) OF 'SYS.I_USER2' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=20)
(SINGLE_COMBINED_WITH_PARENT)
73
68
INDEX (RANGE SCAN) OF 'SYS.I_OBJ4' (INDEX) (Cost=0 Card=1 Bytes=9)
(PARALLEL_COMBINED_WITH_PARENT)
74
14
NESTED LOOPS (Cost=1 Card=1 Bytes=6) (PARALLEL_COMBINED_WITH_PARENT)
75
74
PX RECEIVE (PARALLEL_COMBINED_WITH_PARENT)
76
75
PX SEND (ROUND-ROBIN) OF 'SYS.:TQ70002' (PARALLEL_FROM_SERIAL)
(ROUND-ROBIN)
77
76
PX SELECTOR (SINGLE_COMBINED_WITH_CHILD)
78
77
INDEX (FULL SCAN) OF 'SYS.I_LINK1' (INDEX) (Cost=1 Card=1 Bytes=2)
(SINGLE_COMBINED_WITH_PARENT)
79
74
INDEX (RANGE SCAN) OF 'SYS.I_USER2' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=4)
(PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
10
- filter(LEVEL<=100)
18 - filter(("O"."TYPE#"<>1 AND
"O"."TYPE#"<>2 AND "O"."TYPE#"<>6 OR "O"."TYPE#"=1 AND NOT
EXISTS (SELECT 0 FROM "SYS"."OBJ$" "IO","SYS"."TAB$" "T","SYS"."IND$" "I" WHERE
"I"."OBJ#"=:B1 AND "I"."BO#"="T"."OBJ#" AND
BITAND("T"."PROPERTY",36893488147419103232)=36893488147419103232 AND
"IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2) OR "O"."TYPE#"=2 AND (SELECT 1
FROM "SYS"."TAB$" "T" WHERE "T"."OBJ#"= :B2
ANDBITAND("T"."PROPERTY",36893488147419103232)=0)=1 OR "O"."TYPE#"=6 AND
(SELECT 1 FROM "SYS"."SEQ$" "S" WHERE "S"."OBJ#"=:B3 AND
(BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS NULL))=1) AND
(BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR
"O"."TYPE#"<>88 AND NOT EXISTS (SELECT 0 FROM
"SYS"."USER_EDITIONING$" "UE" WHERE "TYPE#"=:B4 AND "UE"."USER#"=:B5) OR
EXISTS (SELECT 0 FROM "SYS"."USER_EDITIONING$" "UE" WHERE "UE"."TYPE#"=:B6 AND
"UE"."USER#"=:B7) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE'
AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
EXISTS (SELECT 0 FROM "SYS"."USER$" "U2","SYS"."OBJ$" "O2" WHERE
"O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B8 AND
"U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
21 - access("O"."SPARE3"="U"."USER#")
22 -
access("O"."OWNER#"="U"."USER#")
30 - filter(BITAND("O"."FLAGS",128)=0
AND "O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS
NULL)
44 - access("I"."OBJ#"=:B1)
45 -
access("IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2)
45 -
filter("IO"."TYPE#"=2)
46 - filter("I"."BO#"="T"."OBJ#" AND
BITAND("T"."PROPERTY",36893488147419103232)=36893488147419103232)
47 -
filter(BITAND("T"."PROPERTY",36893488147419103232)=0)
48 -
access("T"."OBJ#"=:B1)
51 - filter(BITAND("S"."FLAGS",1024)=0 OR
"S"."FLAGS" IS NULL)
56 - access("S"."OBJ#"=:B1)
60 -
filter("TYPE#"=:B1 AND "UE"."USER#"=:B2)
64 - filter("UE"."TYPE#"=:B1
AND "UE"."USER#"=:B2)
72 - access("U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
72 - filter("U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
73 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
79 -
access("L"."OWNER#"="U"."USER#")
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ commit;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
2,498.81
2 redo
size
redo
size
1,005.39
--> redo log가 발생함
--> DIRECT PATH LOAD가 아닌 CONVENTIONAL INSERT를 하고 있음
--> REDO LOG 엄청 발생함 (TABLE NOLOGGING이어도 REDO LOG발생함)
--> 결국 SELECT만 PARALLEL로 주는것은 REDO LOG 최소화와는 아무런 관련이 없음
--> 추후 해볼 테스트 (과연 이렇게 남은 REDO LOG로 백업/복구가 되는가???)
2. 테이블 NOLOGGING 시 REDO LOG 최소화 여부 테스트 (CLOB 칼럼에 NOGGING)
2-1. 테스트 준비
DROP TABLE
TUNER.TB_CUST_REDO_TEST_LOB PURGE;
CREATE TABLE
TUNER.TB_CUST_REDO_TEST_LOB
(
CUST_NO VARCHAR2(10) NOT NULL
,
CUST_ID VARCHAR2(20) NOT NULL
, CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY
VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
, JOIN_DT VARCHAR2(14) NOT
NULL
, CUST_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT
NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
TABLESPACE TUNER_DATA1;
ALTER TABLE
TUNER.TB_CUST_REDO_TEST_LOB
ADD
( CONTS_CLOB CLOB
)
LOB (CONTS_CLOB) STORE AS SECUREFILE sf_tb_cust_redo_clob
( TABLESPACE TUNER_IDX1 );
2-2. TABLE LOGGING + CLOB 칼럼 LOGGING + NO APPEND
--> REDO LOG를 일부러 정상적으로 남겨볼 것임
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER TABLE TUNER.TB_CUST_REDO_TEST_LOB LOGGING;
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER TABLE TUNER.TB_CUST_REDO_TEST_LOB MODIFY LOB (CONTS_CLOB) (NOCACHE LOGGING);
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
663.87
2 redo
size
redo
size
941.80
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT
INTO
TUNER.TB_CUST_REDO_TEST_LOB
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS
CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS
UPDT_DT
, DBMS_RANDOM.STRING('A', 10000)
FROM DUAL CONNECT BY LEVEL <= 10000;
;
*************************[Explain Plan Time: 2025/11/06
19:44:36]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 LOAD TABLE
CONVENTIONAL OF 'TB_CUST_REDO_TEST_LOB'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=10000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ COMMIT;
[ol7ora12rf1]<SYS@ORA12RF1>$ SELECT COUNT(*) AS CNT FROM
TUNER.TB_CUST_REDO_TEST_LOB;
CNT
------------
10000
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
764.81
2 redo
size
redo
size
944.91
--> 당연히
redo log가 발생하는 상황임 (약 100메가 발생함)
--> 테이블 REDO LOG + CLOB 칼럼 REDO LOG 합쳐서 약 100메가임
2-3. TABLE NOLOGGING + CLOB 칼럼 NOLOGGING + NO APPEND
--테이블이 NOLOGGING인데 APPEND 힌트가 없기 때문에 테이블 REDO LOG가 남아야 하는 상황
--이 상태에서 CLOB 칼럼은 NOLOGGING으로 했을때 redo로그가 남는지 확인할 것임
alter table TUNER.TB_CUST_REDO_TEST_LOB NOLOGGING;
ALTER TABLE TUNER.TB_CUST_REDO_TEST_LOB MODIFY LOB (CONTS_CLOB) (NOCACHE NOLOGGING);
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
765.46
2 redo
size
redo
size
944.98
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT
INTO
TUNER.TB_CUST_REDO_TEST_LOB
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS
CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS
UPDT_DT
, DBMS_RANDOM.STRING('A', 10000)
FROM DUAL CONNECT BY LEVEL <= 10000;
;
*************************[Explain Plan Time: 2025/11/06
19:47:44]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 LOAD TABLE
CONVENTIONAL OF 'TB_CUST_REDO_TEST_LOB'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=10000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ commit;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
783.05
2 redo
size
redo
size
945.16
--약 20메가가
늘어남 CLOB 칼럼에 대한 REDO LOG는 최소화 된것으로 판단됨
2-4. TABLE NOLOGGING + CLOB 칼럼 NOLOGGING +
APPEND
--테이블이 NOLOGGING인데
APPEND INSERT를 하기 때문에 테이블 REDO 로그가 최소화 되어야 하는
상황
--이 상태에서 CLOB 칼럼은
NOLOGGING으로 할 경우 REDO 로그가 최소화되는 것으로 판단되므로 REDO LOG 최소화가 될것으로
판단됨
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
ALTER
TABLE TUNER.TB_CUST_REDO_TEST_LOB MODIFY LOB (CONTS_CLOB) (NOCACHE
NOLOGGING);
alter table TUNER.TB_CUST_REDO_TEST_LOB NOLOGGING;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
803.18
2 redo
size
redo
size
948.19
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT /*+ append */
INTO
TUNER.TB_CUST_REDO_TEST_LOB
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS
CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS
UPDT_DT
, DBMS_RANDOM.STRING('A', 10000)
FROM DUAL CONNECT BY LEVEL <= 10000;
*************************[Explain Plan Time: 2025/11/06
19:52:49]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 LOAD AS SELECT OF
'TUNER.TB_CUST_REDO_TEST_LOB'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=10000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ commit;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
815.34
2 redo
size
redo
size
948.21
--> redo
log가 최소화 된것을 확인
--> CLOB 칼럼의 REDO LOG는 해당 CLOB 칼럼에 대한 LOGGING/NOLOGGING 설정에 달렸음을 확인함
2-5.
TABLE NOLOGGING + CLOB 칼럼 LOGGING + APPEND
--테이블이 NOLOGGING이고 APPEND
INSERT를 하기 때문에 REDO 로그가 최소화 되어야 하는 상황
--이 상태에서 CLOB 칼럼은 LOGGING으로 했을때 REDO 로그가 남는걸로
판단되므로 CLOB 칼럼에 대한 REDO LOG가 왕창 남을 것임
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
862.77
2 redo
size
redo
size
992.34
alter table
TUNER.TB_CUST_REDO_TEST_LOB NOLOGGING;
ALTER TABLE
TUNER.TB_CUST_REDO_TEST_LOB MODIFY LOB (CONTS_CLOB) (NOCACHE
LOGGING);
[ol7ora12rf1]<SYS@ORA12RF1>$
INSERT /*+
append */
INTO TUNER.TB_CUST_REDO_TEST_LOB
SELECT 'C' ||
LPAD(ROWNUM, 9, '0') AS CUST_NO
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
CUST_ID
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS CUST_NM
,
TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80),
'YYYYMMDD') AS BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2),
4, '0') AS SEX_CD
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS
UPDT_DT
, DBMS_RANDOM.STRING('A', 10000)
FROM DUAL CONNECT BY LEVEL <= 10000;
*************************[Explain Plan Time: 2025/11/06
19:52:49]*************************
Execution
Plan
-----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2
Card=1)
1 0 LOAD AS SELECT OF
'TUNER.TB_CUST_REDO_TEST_LOB'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=10000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ commit;
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
951.62
2 redo
size
redo
size
992.36
--> TABLE REDO LOG는 최소화되고 CLOB 칼럼의 REDO LOG는 남은 것을 확인
3. CREATE TABLE 테이블명 NOLOGGING AS SELECT 테스트
3-1. 일반 테이블인 경우 (CLOB CLOB이 없는 경우)
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
951.64
2 redo
size
redo
size
992.37
create
table TUNER.TB_CUST_REDO_TEST_2 nologging as
SELECT 'C' || LPAD(ROWNUM, 9,
'0') AS CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
-- ,
NULL AS UPDT_ID
-- , NULL AS UPDT_DT
FROM
DUAL CONNECT BY LEVEL <= 1000000
;
*************************[Explain Plan Time: 2025/11/06
22:16:24]*************************
Execution
Plan
-----------------------------------------------------------
0 CREATE TABLE STATEMENT Optimizer=ALL_ROWS
(Cost=3 Card=1)
1 0 LOAD AS SELECT
OF 'TB_CUST_REDO_TEST_2'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=1000000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
952.23
2 redo
size
redo
size
992.43
-->
예상대로 REDO LOG가 최소화되는 것을 확인
3-2. CLOB 칼럼이 존재하는 테이블인 경우
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
955.45
2 redo
size
redo
size
995.60
drop table
TUNER.TB_CUST_REDO_TEST_3 purge;
CREATE TABLE
TUNER.TB_CUST_REDO_TEST_3
NOLOGGING
AS
SELECT 'C' || LPAD(ROWNUM, 9,
'0') AS CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31','YYYY-MM-DD')
- DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31','YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
RPAD(TO_CLOB(DBMS_RANDOM.STRING('A', 1000)), 10000, 'X') AS CONTS_CLOB
FROM DUAL
CONNECT BY LEVEL <= 100000
;
*************************[Explain Plan Time: 2025/11/06
22:21:22]*************************
Execution
Plan
-----------------------------------------------------------
0 CREATE TABLE STATEMENT Optimizer=ALL_ROWS
(Cost=3 Card=1)
1 0 LOAD AS SELECT
OF 'TB_CUST_REDO_TEST_3'
2
1 COUNT
3
2 CONNECT BY (WITHOUT
FILTERING)
4
3 FAST DUAL (Cost=2
Card=1)
-----------------------------------------------------------
Predicate
information (identified by operation
id):
-----------------------------------------------------------
3 -
filter(LEVEL<=100000)
-----------------------------------------------------------
[ol7ora12rf1]<SYS@ORA12RF1>$ @st_redo
INST
STAT_NAME
STAT_NAME
REDO SIZE(MB)
---- ------------------------- -------------------------
---------------
1 redo
size
redo
size
1,104.02
2 redo
size
redo
size
995.65
--> CLOB 칼럼에 대한 REDO 로그가 최소화되는 것을
확인함
--아래와 같이 CLOB 칼럼의 세그먼트 크기를 구해서 CREATE TABLE NOLOGGING AS SELECT 시 CLOB 칼럼의 REDO LOG가 최소화된것을 확인함
SELECT *
FROM (SELECT
GUBUN
,
OWNER
,
LEVEL1
,
LEVEL2
, ROUND(SUM(BYTES)/1024/1024/1024, 4) AS
SUM_GB
FROM (SELECT
'TABLE(SEGMENT_NAME & PARTITION_NAME)' AS
GUBUN
, A.OWNER AS
OWNER
, A.SEGMENT_NAME AS
LEVEL1
, A.PARTITION_NAME AS
LEVEL2
, A.BYTES AS
BYTES
FROM DBA_SEGMENTS
A
WHERE A.SEGMENT_TYPE IN
('TABLE'
, 'TABLE
PARTITION'
, 'TABLE
SUBPARTITION')
UNION
ALL
SELECT 'LOB(TABLE_NAME & INDEX_NAME)' AS
GUBUN
, L.OWNER AS
OWNER
, L.TABLE_NAME AS
LEVEL1
, L.COLUMN_NAME AS
LEVEL2
, S.BYTES AS
BYTES
FROM DBA_LOBS
L
, DBA_SEGMENTS
S
WHERE S.SEGMENT_NAME =
L.SEGMENT_NAME
AND S.OWNER =
L.OWNER
AND S.SEGMENT_TYPE IN
('LOBSEGMENT'
, 'LOB PARTITION') ) A
GROUP
BY GUBUN, OWNER, LEVEL1,
LEVEL2
ORDER BY 5 DESC
)
WHERE LEVEL1 =
'TB_CUST_REDO_TEST_3'
GUBUN
OWNER
LEVEL1
LEVEL2
SUM_GB
------------------------------------ ------
----------------------------------- ------------
LOB(TABLE_NAME &
INDEX_NAME) TUNER
TB_CUST_REDO_TEST_3
CONTS_CLOB 2.4377
TABLE(SEGMENT_NAME & PARTITION_NAME) TUNER
TB_CUST_REDO_TEST_3
0.0186
--> CREATE TABLE NOLOGGING AS SELECT로 생성한 CLOB 칼럼의 크기가 2.4기가인데 REDO LOG는 약 150메가만 생성됐으므로
--> CLOB CLOB의 REDO LOG가 최소화 된것을 확인함