OS 환경 : Oracle Linux 7.7 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 11gR2 CLOB 컬럼 STORAGE IN ROW 옵션별 용량 확인 테스트
본문에서는 각각 BASICFILE, SECUREFILE로 구성된 CLOB 컬럼에 1982byte 이하 데이터와 5120byte 데이터를 10만 건씩 삽입해보고
row 내부 저장과 out-of-line 저장 시 실제 용량이 어떻게 달라지는지 확인해봄
참고로 STORAGE IN ROW 옵션이 활성화된 경우(기본값) 문자열 길이가 1982byte 미만이면 일반 테이블 row 세그먼트에 저장되고 1982byte가 넘어가면 LOB 세그먼트에 저장됨
CLOB 컬럼은 입력한 문자열 길이만 보고 용량을 계산하면 실제 세그먼트 사용량과 차이가 발생할 수 있음
참고 : CLOB Is Stored Out-of-line When Insert CLOB Data Greater Than 1982 Bytes(KB139645)
*참고로 19c에서도 결과는 동일했음
테스트
1. BASICFILE 테스트(문자열 5120byte 길이 삽입)
2. SECUREFILE 테스트(문자열 5120byte 길이 삽입)
3. BASICFILE(문자열 4000byte 미만 길이 삽입)
4. SECUREFILE(문자열 4000byte 미만 길이 삽입)
테스트
1. BASICFILE 테스트(문자열 5120byte 길이 삽입)
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE clob_8k_test PURGE;
CREATE TABLE clob_8k_test
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS BASICFILE clob_8k_test_lob
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'CLOB_8K_TEST';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
CLOB_8K_TEST C_DATA CLOB_8K_TEST_LOB SYS_IL0000014836C00002$$ NO 8192 YES 10 NO YES
|
현재 SECUREFILE이 아니고 CHUNK 크기는 8192(8k)임
샘플 데이터 삽입(문자열 5120byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
INSERT INTO clob_8k_test (id, c_data)
WITH payload AS
(
SELECT TO_CLOB(RPAD('A', 4000, 'A')) ||
TO_CLOB(RPAD('B', 1120, 'B')) AS c
FROM dual
),
gen AS
(
SELECT ROWNUM AS rn
FROM (SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) a,
(SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) b
WHERE ROWNUM <= 100000
)
SELECT gen.rn,
payload.c
FROM gen,
payload;
COMMIT;
|
10만건 삽입됨
clob 컬럼 실제 길이 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
SELECT COUNT(*) AS row_cnt,
MIN(DBMS_LOB.GETLENGTH(c_data)) AS min_len,
ROUND(AVG(DBMS_LOB.GETLENGTH(c_data)), 2) AS avg_len,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS max_len,
MIN(
LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)) +
LENGTHB(DBMS_LOB.SUBSTR(c_data, 1120, 4001))
) AS min_lenb,
ROUND(AVG(
LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)) +
LENGTHB(DBMS_LOB.SUBSTR(c_data, 1120, 4001))
), 2) AS avg_lenb,
MAX(
LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)) +
LENGTHB(DBMS_LOB.SUBSTR(c_data, 1120, 4001))
) AS max_lenb
FROM clob_8k_test;
ROW_CNT MIN_LEN AVG_LEN MAX_LEN MIN_LENB AVG_LENB MAX_LENB
---------- ---------- ---------- ---------- ---------- ---------- ----------
100000 5120 5120 5120 5120 5120 5120
|
5120byte CLOB이고 DBMS_LOB.GETLENGTH와 DBMS_LOB.SUBSTR/LENGTHB 결과 모두 5120으로 확인됨
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024 / 1024 / 1024, 2) AS logical_gb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024 / 1024 / 1024, 2) AS chunk_est_gb
FROM clob_8k_test;
ROW_CNT LOGICAL_GB CHUNK_EST_GB
---------- ---------- ------------
100000 .48 .76
|
논리 용량은 0.48gb, CHUNK 기준 예상 용량은 0.76gb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'CLOB_8K_TEST'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS gb
FROM dba_segments s
WHERE s.segment_name = 'CLOB_8K_TEST'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE GB
------------------------------ ------------------ ----------
CLOB_8K_TEST_LOB LOBSEGMENT 1.56
CLOB_8K_TEST TABLE .01
SYS_IL0000014836C00002$$ LOBINDEX 0
|
테스트1 결론 :
현재 100,000개(10만개)의 clob 컬럼이 든 row가 존재할때 약 1.56gb임
LOBSEGMENT의 크기가 증가했으므로 CHUNK 단위 out-of-line 저장이 발생한것임(문자열 길이가 1982byte 초과했기 때문)
논리 데이터 대비 1.56 / 0.48 = 약 3.25배이고
단순 8KB CHUNK 예상 대비 1.56 / 0.76 = 약 2.05배임
만약 48,000,000개(4,800만개)가 존재하는 경우 대략 1.56*480=748.8gb임
참고로 위에서 row 당 크기가 논리적으로는 5120 byte라고 봤는데 이 값은 조회했을 때 보이는 문자열의 byte에 가깝고 LOB 세그먼트에 실제 저장되는 byte와는 다를 수 있음
2. SECUREFILE 테스트(문자열 5120byte 길이 삽입)
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE clob_8k_test_s PURGE;
CREATE TABLE clob_8k_test_s
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS SECUREFILE clob_8k_test_lob_s
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'CLOB_8K_TEST_S';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
CLOB_8K_TEST_S C_DATA CLOB_8K_TEST_LOB_S SYS_IL0000014839C00002$$ YES 8192 YES NO YES
|
현재 SECUREFILE이면서 CHUNK 크기는 8192(8k)임
샘플 데이터 삽입(문자열 5120byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
INSERT INTO clob_8k_test_s (id, c_data)
WITH payload AS
(
SELECT TO_CLOB(RPAD('A', 4000, 'A')) ||
TO_CLOB(RPAD('B', 1120, 'B')) AS c
FROM dual
),
gen AS
(
SELECT ROWNUM AS rn
FROM (SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) a,
(SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) b
WHERE ROWNUM <= 100000
)
SELECT gen.rn,
payload.c
FROM gen,
payload;
COMMIT;
|
10만건 삽입됨
실제 길이 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
SELECT COUNT(*) AS row_cnt,
MIN(DBMS_LOB.GETLENGTH(c_data)) AS min_len,
ROUND(AVG(DBMS_LOB.GETLENGTH(c_data)), 2) AS avg_len,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS max_len,
MIN(
LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)) +
LENGTHB(DBMS_LOB.SUBSTR(c_data, 1120, 4001))
) AS min_lenb,
ROUND(AVG(
LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)) +
LENGTHB(DBMS_LOB.SUBSTR(c_data, 1120, 4001))
), 2) AS avg_lenb,
MAX(
LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)) +
LENGTHB(DBMS_LOB.SUBSTR(c_data, 1120, 4001))
) AS max_lenb
FROM clob_8k_test_s;
ROW_CNT MIN_LEN AVG_LEN MAX_LEN MIN_LENB AVG_LENB MAX_LENB
---------- ---------- ---------- ---------- ---------- ---------- ----------
100000 5120 5120 5120 5120 5120 5120
|
5120byte CLOB이고 DBMS_LOB.GETLENGTH와 DBMS_LOB.SUBSTR/LENGTHB 결과 모두 5120으로 확인됨
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024 / 1024 / 1024, 2) AS logical_gb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024 / 1024 / 1024, 2) AS chunk_est_gb
FROM clob_8k_test_s;
ROW_CNT LOGICAL_GB CHUNK_EST_GB
---------- ---------- ------------
100000 .48 .76
|
논리 용량은 0.48gb, CHUNK 기준 예상 용량은 0.76gb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'CLOB_8K_TEST_S'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS gb
FROM dba_segments s
WHERE s.segment_name = 'CLOB_8K_TEST_S'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE GB
------------------------------ ------------------ ----------
CLOB_8K_TEST_LOB_S LOBSEGMENT 1.75
CLOB_8K_TEST_S TABLE .01
SYS_IL0000014839C00002$$ LOBINDEX 0
|
테스트2 결론 :
현재 100,000개(10만개)의 clob 컬럼이 든 row가 존재할때 약 1.75gb임
LOBSEGMENT의 크기가 증가했으므로 CHUNK 단위 out-of-line 저장이 발생한것임(문자열 길이가 1982byte 초과했기 때문)
논리 데이터 대비 1.75 / 0.48 = 약 3.6배이고
단순 8KB CHUNK 예상 대비 1.75 / 0.76 = 약 2.3배임
만약 48,000,000개(4,800만개)가 존재하는 경우 대략 1.75*480=840gb임
참고로 위에서 row 당 크기가 논리적으로는 5120 byte라고 봤는데 out-of-line 저장이 된 경우 이 값은 조회했을 때 보이는 문자열의 byte에 가깝고 LOB 세그먼트에 실제 저장되는 byte와는 다를 수 있음
3. BASICFILE(문자열 4000byte 미만 길이 삽입)
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE clob_8k_test2 PURGE;
CREATE TABLE clob_8k_test2
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS BASICFILE clob_8k_test2_lob
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'CLOB_8K_TEST2';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
CLOB_8K_TEST2 C_DATA CLOB_8K_TEST2_LOB SYS_IL0000014842C00002$$ NO 8192 YES 10 NO YES
|
현재 SECUREFILE이 아니고 CHUNK 크기는 8192(8k)임, IN_ROW가 Yes임
ENABLE STORAGE IN ROW 상태에서 LOB 값과 control information을 합친 크기가 약 4000 bytes 이하이면 테이블 row 내부에 저장될 수 있음
(정확히는 문자열 길이가 1982byte 이하인 경우)
샘플 데이터 삽입(문자열 1982byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
INSERT INTO clob_8k_test2 (id, c_data)
WITH payload AS
(
SELECT TO_CLOB(RPAD('A', 1982, 'A')) AS c
FROM dual
),
gen AS
(
SELECT ROWNUM AS rn
FROM (SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) a,
(SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) b
WHERE ROWNUM <= 100000
)
SELECT gen.rn,
payload.c
FROM gen,
payload;
COMMIT;
|
10만건 삽입됨
clob 컬럼 실제 길이 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
SELECT COUNT(*) AS row_cnt,
MIN(DBMS_LOB.GETLENGTH(c_data)) AS min_len,
ROUND(AVG(DBMS_LOB.GETLENGTH(c_data)), 2) AS avg_len,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS max_len,
MIN(NVL(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)), 0)) AS min_lenb2,
ROUND(AVG(NVL(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)), 0)), 2) AS avg_lenb2,
MAX(NVL(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)), 0)) AS max_lenb2
FROM clob_8k_test2;
ROW_CNT MIN_LEN AVG_LEN MAX_LEN MIN_LENB2 AVG_LENB2 MAX_LENB2
---------- ---------- ---------- ---------- ---------- ---------- ----------
100000 1982 1982 1982 1982 1982 1982
|
1982byte CLOB이고 DBMS_LOB.GETLENGTH와 DBMS_LOB.SUBSTR/LENGTHB 결과 모두 1982으로 확인됨
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024 / 1024 / 1024, 2) AS logical_gb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024 / 1024 / 1024, 2) AS chunk_est_gb
FROM clob_8k_test2;
ROW_CNT LOGICAL_GB CHUNK_EST_GB
---------- ---------- ------------
100000 .18 .76
|
논리 용량은 0.18gb, CHUNK 기준 예상 용량은 0.76gb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'CLOB_8K_TEST2'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS gb
FROM dba_segments s
WHERE s.segment_name = 'CLOB_8K_TEST2'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE GB
------------------------------ ------------------ ----------
CLOB_8K_TEST2 TABLE .77
CLOB_8K_TEST2_LOB LOBSEGMENT 0
SYS_IL0000014842C00002$$ LOBINDEX 0
|
테스트3 결론 :
현재 100,000개(10만개)의 clob 컬럼이 든 row가 존재할때 약 0.77gb임
LOBSEGMENT가 0이므로 CHUNK 단위 out-of-line 저장은 발생하지 않은 것임(문자열 길이가 1982byte 미만이기 때문)
논리 데이터 대비 0.77 / 0.18 = 약 4.2배이고
단순 8KB CHUNK 예상 대비 0.77 / 0.76 = 약 1.01배임(거의 동일함)
이는 LOB value 외에도 row overhead, LOB locator/control information, PCTFREE, block 여유 공간 등이 포함되기 때문임
만약 48,000,000개(4,800만개)가 존재하는 경우 대략 0.77*480=369.6gb임
참고로 위에서 row 당 크기가 논리적으로는 1982byte라고 봤는데 out-of-line 저장이 되지 않은 경우 이 값은 조회했을 때 보이는 문자열의 byte가 실제 크기에 가까움
4. SECUREFILE(문자열 4000byte 미만 길이 삽입)
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DROP TABLE clob_8k_test_s2 PURGE;
CREATE TABLE clob_8k_test_s2
(
id NUMBER,
c_data CLOB,
ins_date DATE DEFAULT SYSDATE
)
TABLESPACE USERS
LOB (c_data) STORE AS SECUREFILE clob_8k_test_s2_lob
(
TABLESPACE USERS
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
);
|
dba_lobs로 정보 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_name for a15
col column_name for a10
col segment_name for a20
col index_name for a25
col securefile for a5
SELECT table_name, column_name, segment_name, index_name, securefile, chunk, in_row, pctversion, retention, cache, logging
FROM dba_lobs
WHERE table_name = 'CLOB_8K_TEST_S2';
TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME SECUR CHUNK IN_ PCTVERSION RETENTION CACHE LOGGING
--------------- ---------- -------------------- ------------------------- ----- ---------- --- ---------- ---------- ---------- -------
CLOB_8K_TEST_S2 C_DATA CLOB_8K_TEST_S2_LOB SYS_IL0000014845C00002$$ YES 8192 YES NO YES
|
현재 SECUREFILE이 아니고 CHUNK 크기는 8192(8k)임, IN_ROW가 Yes임
ENABLE STORAGE IN ROW 상태에서 LOB 값과 control information을 합친 크기가 약 4000 bytes 이하이면 테이블 row 내부에 저장될 수 있음
(정확히는 문자열 길이가 1982byte 이하인 경우)
샘플 데이터 삽입(문자열 1982byte 길이 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
INSERT INTO clob_8k_test_s2 (id, c_data)
WITH payload AS
(
SELECT TO_CLOB(RPAD('A', 1982, 'A')) AS c
FROM dual
),
gen AS
(
SELECT ROWNUM AS rn
FROM (SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) a,
(SELECT 1 FROM dual CONNECT BY LEVEL <= 1200) b
WHERE ROWNUM <= 100000
)
SELECT gen.rn,
payload.c
FROM gen,
payload;
COMMIT;
|
10만건 삽입됨
clob 컬럼 실제 길이 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
SELECT COUNT(*) AS row_cnt,
MIN(DBMS_LOB.GETLENGTH(c_data)) AS min_len,
ROUND(AVG(DBMS_LOB.GETLENGTH(c_data)), 2) AS avg_len,
MAX(DBMS_LOB.GETLENGTH(c_data)) AS max_len,
MIN(NVL(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)), 0)) AS min_lenb2,
ROUND(AVG(NVL(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)), 0)), 2) AS avg_lenb2,
MAX(NVL(LENGTHB(DBMS_LOB.SUBSTR(c_data, 4000, 1)), 0)) AS max_lenb2
FROM clob_8k_test_s2;
ROW_CNT MIN_LEN AVG_LEN MAX_LEN MIN_LENB2 AVG_LENB2 MAX_LENB2
---------- ---------- ---------- ---------- ---------- ---------- ----------
100000 1982 1982 1982 1982 1982 1982
|
1982byte CLOB이고 DBMS_LOB.GETLENGTH와 DBMS_LOB.SUBSTR/LENGTHB 결과 모두 1982으로 확인됨
논리 용량 vs CHUNK 기준 예상 용량
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
SELECT COUNT(*) AS row_cnt,
ROUND(SUM(DBMS_LOB.GETLENGTH(c_data)) / 1024 / 1024 / 1024, 2) AS logical_gb,
ROUND(SUM(CEIL(DBMS_LOB.GETLENGTH(c_data) / 8192) * 8192)
/ 1024 / 1024 / 1024, 2) AS chunk_est_gb
FROM clob_8k_test_s2;
ROW_CNT LOGICAL_GB CHUNK_EST_GB
---------- ---------- ------------
100000 .18 .76
|
논리 용량은 0.18gb, CHUNK 기준 예상 용량은 0.76gb임
실제 세그먼트 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col segment_name for a30
WITH lob_info AS
(
SELECT segment_name,
index_name
FROM user_lobs
WHERE table_name = 'CLOB_8K_TEST_S2'
)
SELECT s.segment_name,
s.segment_type,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS gb
FROM dba_segments s
WHERE s.segment_name = 'CLOB_8K_TEST_S2'
OR s.segment_name IN (SELECT segment_name FROM lob_info)
OR s.segment_name IN (SELECT index_name FROM lob_info)
ORDER BY s.bytes DESC;
SEGMENT_NAME SEGMENT_TYPE GB
------------------------------ ------------------ ----------
CLOB_8K_TEST_S2 TABLE .77
CLOB_8K_TEST_S2_LOB LOBSEGMENT 0
SYS_IL0000014845C00002$$ LOBINDEX 0
|
테스트4 결론 :
현재 100,000개(10만개)의 clob 컬럼이 든 row가 존재할때 약 0.77gb임
LOBSEGMENT가 0이므로 CHUNK 단위 out-of-line 저장은 발생하지 않은 것임(문자열 길이가 1982byte 미만이기 때문)
논리 데이터 대비 0.77 / 0.18 = 약 4.2배이고
단순 8KB CHUNK 예상 대비 0.77 / 0.76 = 약 1.01배임(거의 동일함)
이는 LOB value 외에도 row overhead, LOB locator/control information, PCTFREE, block 여유 공간 등이 포함되기 때문임
만약 48,000,000개(4,800만개)가 존재하는 경우 대략 0.77*480=369.6gb임
참고로 위에서 row 당 크기가 논리적으로는 1982byte라고 봤는데 out-of-line 저장이 되지 않은 경우 이 값은 조회했을 때 보이는 문자열의 byte가 실제 크기에 가까움
종합 결론 :
본문 테스트 결과처럼 CLOB 컬럼의 실제 저장 공간은 단순히 입력한 문자열 길이만으로 계산하면 안됨(BASICFILE나 SECUREFILE 모두)
테스트1, 테스트2처럼 5120 길이의 CLOB 데이터를 100,000건 삽입했을 때 논리 데이터 크기는 약 0.48GB이고, 단순히 8KB CHUNK 1개씩 사용한다고 계산하면 약 0.76GB 정도가 예상됨
하지만 실제 LOBSEGMENT 크기는 BASICFILE에서 약 1.56GB, SECUREFILE에서 약 1.75GB로 확인됨
이 차이가 발생한 이유는 CLOB이 out-of-line 방식으로 저장될 경우 LOB 데이터가 CHUNK 단위로 할당되고 내부 저장 형식, LOB 관리 정보, CHUNK 내부 오버헤드 등이 함께 반영되기 때문임
본문 테스트에서는 5120 길이의 문자열이 실제 LOB 세그먼트 내부에서 8KB CHUNK 1개에 담기지 않고 2개 CHUNK를 사용하는 형태로 저장된 것으로 보임
테스트3, 테스트4처럼 1982 길이의 CLOB 데이터를 삽입한 테스트에서는 LOBSEGMENT 크기가 0으로 확인됨
이는 ENABLE STORAGE IN ROW 상태에서 LOB value와 control information을 포함한 크기가 row 내부 저장 가능 범위에 들어갔기 때문임
이 경우 별도의 LOBSEGMENT에 CHUNK 단위로 저장되지 않고 테이블 세그먼트 내부에 저장됨
따라서 CLOB 컬럼의 저장 공간을 산정할 때는 아래 사항을 고려해야 함
1. DBMS_LOB.GETLENGTH 결과는 논리적인 CLOB 길이 확인에는 유용하지만 실제 LOBSEGMENT 저장 크기와 동일하다고 볼 수 없음
2. out-of-line 저장이 발생하면 LOB 데이터는 CHUNK 단위로 공간을 사용하기 때문에 실제 사용량이 논리 데이터 크기보다 크게 증가할 수 있음
3. 작은 CLOB은 ENABLE STORAGE IN ROW 조건에서 테이블 row 내부에 저장될 수 있고 이 경우 LOBSEGMENT 크기는 증가하지 않음
4. CLOB 저장 공간을 대량 산정할 때는 논리 길이 기준 계산보다 실제 테스트 결과를 기준으로 보정하는 것이 안전함
이번 테스트 기준으로 5120 길이 CLOB 100,000건은 BASICFILE나 SECUREFILE에 따라 차이가 있지만 약 1.56GB에서 1.75GB 수준의 공간을 사용함
이를 48,000,000건으로 단순 환산하면 BASICFILE은 약 748.8GB, SECUREFILE은 약 840GB 수준까지 증가할 수 있음
반면 1982 길이 CLOB처럼 row 내부 저장이 가능한 경우에는 48,000,000건 기준 약 369.6GB 수준으로 예상됨
결론적으로 CLOB 컬럼은 "문자열 길이 * 건수"로만 용량을 계산하면 실제 사용량과 큰 차이가 발생할 수 있음
특히 row 내부 저장 가능 범위를 넘어서 out-of-line 저장이 발생하는 순간 CHUNK 단위 할당과 내부 저장 오버헤드 때문에 세그먼트 크기가 크게 증가할 수 있음
*참고로 19c에서도 결과는 동일했음
참조 :
How to Calculate Space Used by LOB Segments in the Database(KB114063)
CLOB Is Stored Out-of-line When Insert CLOB Data Greater Than 1982 Bytes(KB139645)
How to Compute the Size of a Table containing Outline CLOBs and BLOBs(KB149847)
How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?(KB164984)
CLOBs and NCLOBs character set storage in Oracle Release 8i, 9i, 10g and higher(KB772367)
https://hrjeong.tistory.com/294
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c CLOB 컬럼 STORAGE IN ROW 옵션별 저장 위치 확인 테스트 (0) | 2026.07.05 |
|---|---|
| 오라클 26ai 신기능 Select AI Agent 사용 테스트 (0) | 2026.06.27 |
| 오라클 19c 비파티션 테이블에서 파티션 테이블로 전환 및 성능 비교 (0) | 2026.06.25 |
| 오라클 19c Restart 환경 hostname 변경 가이드 (0) | 2026.06.21 |
| 오라클 19c Active Data Guard(ADG)와 Data Guard(DG) 차이점 및 DML Redirection 기능 (0) | 2026.06.16 |