Oracle: /* LOB ENABLE STORAGE IN ROW 성능 비교 테스트 */

2026-09-15T03:05:34Z

LOB ENABLE STORAGE IN ROW 성능 비교 테스트

Oracle: 새 문서: === LOB ENABLE STORAGE IN ROW 성능 비교 테스트 === -- ============================================================ -- LOB ENABLE STORAGE IN ROW 성능 비교 테스트 -- 목적: 4000byte 이하(인라인) vs 4000byte 초과(아웃오브라인) 데이터를 -- 삽입/조회할 때 성능(시간, 논리/물리 I/O, 공간 사용량) 차이를 측정 -- 구성: 하나의 테이블에 LOB 컬럼 2개(DOC_A, DOC_B)를 두어, -- 컬럼별로 다...

2026-09-15T00:35:54Z

새 문서: === LOB ENABLE STORAGE IN ROW 성능 비교 테스트 === <source lang=sql> -- ============================================================ -- LOB ENABLE STORAGE IN ROW 성능 비교 테스트 -- 목적: 4000byte 이하(인라인) vs 4000byte 초과(아웃오브라인) 데이터를 -- 삽입/조회할 때 성능(시간, 논리/물리 I/O, 공간 사용량) 차이를 측정 -- 구성: 하나의 테이블에 LOB 컬럼 2개(DOC_A, DOC_B)를 두어, -- 컬럼별로 다...

새 문서

=== LOB ENABLE STORAGE IN ROW 성능 비교 테스트 ===
<source lang=sql>
-- ============================================================
-- LOB ENABLE STORAGE IN ROW 성능 비교 테스트
-- 목적: 4000byte 이하(인라인) vs 4000byte 초과(아웃오브라인) 데이터를
-- 삽입/조회할 때 성능(시간, 논리/물리 I/O, 공간 사용량) 차이를 측정
-- 구성: 하나의 테이블에 LOB 컬럼 2개(DOC_A, DOC_B)를 두어,
-- 컬럼별로 다른 CHUNK/FREEPOOLS 설정을 줘도 비교할 수 있게 함
-- 실행: sqlplus 계정/비번@dsn @lob_inrow_perf_test.sql
-- ============================================================

SET SERVEROUTPUT ON SIZE UNLIMITED
SET TIMING ON
SET LINESIZE 200
SET PAGESIZE 100

-- ------------------------------------------------------------
-- 0. 기존 오브젝트 정리 (재실행 대비)
-- ------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE PERF_TEST_LOB2 PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN RAISE; END IF; -- ORA-00942: 테이블 없음은 무시
END;
/

-- ------------------------------------------------------------
-- 1. 테스트 테이블 생성 (LOB 컬럼 2개, ENABLE STORAGE IN ROW)
-- DOC_A / DOC_B에 CHUNK를 다르게 줘서 컬럼별 영향도 비교도 가능
-- ------------------------------------------------------------
CREATE TABLE PERF_TEST_LOB2 (
id NUMBER NOT NULL,
size_group VARCHAR2(10) NOT NULL, -- 'SMALL' (<=4000B) / 'LARGE' (>4000B)
created_date DATE DEFAULT SYSDATE,
doc_a CLOB,
doc_b CLOB,
CONSTRAINT pk_perf_test_lob2 PRIMARY KEY (id)
)
LOB (doc_a) STORE AS SECUREFILE doc_a_lseg (
TABLESPACE users
ENABLE STORAGE IN ROW
CHUNK 8192
NOCACHE
NOLOGGING
)
LOB (doc_b) STORE AS SECUREFILE doc_b_lseg (
TABLESPACE users
ENABLE STORAGE IN ROW
CHUNK 8192
NOCACHE
NOLOGGING
);

-- ------------------------------------------------------------
-- 2. 세션 I/O 통계 측정용 헬퍼 프로시저
-- (V$SESSTAT 기반으로 논리적/물리적 읽기 변화량을 계산)
-- ------------------------------------------------------------
CREATE OR REPLACE PROCEDURE PRINT_SESS_IO_DELTA (
p_label IN VARCHAR2,
p_start_lio IN NUMBER,
p_start_pio IN NUMBER
) IS
v_lio NUMBER;
v_pio NUMBER;
BEGIN
SELECT ss.value INTO v_lio
FROM v$sesstat ss, v$statname sn
WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
AND ss.statistic# = sn.statistic#
AND sn.name = 'session logical reads';

SELECT ss.value INTO v_pio
FROM v$sesstat ss, v$statname sn
WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
AND ss.statistic# = sn.statistic#
AND sn.name = 'physical reads';

DBMS_OUTPUT.PUT_LINE(
RPAD(p_label, 28) ||
' logical_reads(delta)=' || LPAD(v_lio - p_start_lio, 10) ||
' physical_reads(delta)=' || LPAD(v_pio - p_start_pio, 10)
);
END;
/

CREATE OR REPLACE FUNCTION GET_SESS_LIO RETURN NUMBER IS
v NUMBER;
BEGIN
SELECT ss.value INTO v
FROM v$sesstat ss, v$statname sn
WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
AND ss.statistic# = sn.statistic#
AND sn.name = 'session logical reads';
RETURN v;
END;
/

CREATE OR REPLACE FUNCTION GET_SESS_PIO RETURN NUMBER IS
v NUMBER;
BEGIN
SELECT ss.value INTO v
FROM v$sesstat ss, v$statname sn
WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
AND ss.statistic# = sn.statistic#
AND sn.name = 'physical reads';
RETURN v;
END;
/

-- ------------------------------------------------------------
-- 3. 테스트 1: SMALL 그룹 삽입 (각 LOB 3900byte, 4000byte 미만 -> 인라인)
-- ------------------------------------------------------------
DECLARE
v_start_time NUMBER;
v_start_lio NUMBER;
v_start_pio NUMBER;
v_row_count PLS_INTEGER := 2000; -- 필요에 따라 조정
v_small_text CLOB;
BEGIN
v_small_text := RPAD('A', 3900, 'A'); -- 3900byte, 4000byte 임계치 이하

v_start_time := DBMS_UTILITY.GET_TIME;
v_start_lio := GET_SESS_LIO;
v_start_pio := GET_SESS_PIO;

FOR i IN 1 .. v_row_count LOOP
INSERT INTO PERF_TEST_LOB2 (id, size_group, doc_a, doc_b)
VALUES (i, 'SMALL', v_small_text, v_small_text);
END LOOP;
COMMIT;

DBMS_OUTPUT.PUT_LINE('=== [SMALL] ' || v_row_count || '건 INSERT ===');
DBMS_OUTPUT.PUT_LINE('경과시간(centisec): ' || (DBMS_UTILITY.GET_TIME - v_start_time));
PRINT_SESS_IO_DELTA('SMALL INSERT I/O', v_start_lio, v_start_pio);
END;
/

-- ------------------------------------------------------------
-- 4. 테스트 2: LARGE 그룹 삽입 (각 LOB 8000byte, 4000byte 초과 -> 아웃오브라인)
-- ------------------------------------------------------------
DECLARE
v_start_time NUMBER;
v_start_lio NUMBER;
v_start_pio NUMBER;
v_row_count PLS_INTEGER := 2000;
v_large_text CLOB;
BEGIN
v_large_text := RPAD('B', 8000, 'B'); -- 8000byte, 4000byte 임계치 초과

v_start_time := DBMS_UTILITY.GET_TIME;
v_start_lio := GET_SESS_LIO;
v_start_pio := GET_SESS_PIO;

FOR i IN 1 .. v_row_count LOOP
INSERT INTO PERF_TEST_LOB2 (id, size_group, doc_a, doc_b)
VALUES (2000 + i, 'LARGE', v_large_text, v_large_text);
END LOOP;
COMMIT;

DBMS_OUTPUT.PUT_LINE('=== [LARGE] ' || v_row_count || '건 INSERT ===');
DBMS_OUTPUT.PUT_LINE('경과시간(centisec): ' || (DBMS_UTILITY.GET_TIME - v_start_time));
PRINT_SESS_IO_DELTA('LARGE INSERT I/O', v_start_lio, v_start_pio);
END;
/

-- 통계 최신화 (조회 테스트 전 필수)
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PERF_TEST_LOB2', CASCADE => TRUE);

-- ------------------------------------------------------------
-- 5. 조회 성능 비교: SMALL 그룹 vs LARGE 그룹 (LOB 길이 합산으로 실제 LOB 접근 유도)
-- ------------------------------------------------------------
DECLARE
v_start_time NUMBER;
v_start_lio NUMBER;
v_start_pio NUMBER;
v_sum_len NUMBER;
BEGIN
-- SMALL 그룹 조회
v_start_time := DBMS_UTILITY.GET_TIME;
v_start_lio := GET_SESS_LIO;
v_start_pio := GET_SESS_PIO;

SELECT SUM(DBMS_LOB.GETLENGTH(doc_a) + DBMS_LOB.GETLENGTH(doc_b))
INTO v_sum_len
FROM PERF_TEST_LOB2
WHERE size_group = 'SMALL';

DBMS_OUTPUT.PUT_LINE('=== [SMALL] 전체 조회 (sum_len=' || v_sum_len || ') ===');
DBMS_OUTPUT.PUT_LINE('경과시간(centisec): ' || (DBMS_UTILITY.GET_TIME - v_start_time));
PRINT_SESS_IO_DELTA('SMALL SELECT I/O', v_start_lio, v_start_pio);

-- LARGE 그룹 조회
v_start_time := DBMS_UTILITY.GET_TIME;
v_start_lio := GET_SESS_LIO;
v_start_pio := GET_SESS_PIO;

SELECT SUM(DBMS_LOB.GETLENGTH(doc_a) + DBMS_LOB.GETLENGTH(doc_b))
INTO v_sum_len
FROM PERF_TEST_LOB2
WHERE size_group = 'LARGE';

DBMS_OUTPUT.PUT_LINE('=== [LARGE] 전체 조회 (sum_len=' || v_sum_len || ') ===');
DBMS_OUTPUT.PUT_LINE('경과시간(centisec): ' || (DBMS_UTILITY.GET_TIME - v_start_time));
PRINT_SESS_IO_DELTA('LARGE SELECT I/O', v_start_lio, v_start_pio);
END;
/

-- ------------------------------------------------------------
-- 6. 실제 인라인/아웃오브라인 여부 확인 (LOB 세그먼트 공간 사용량 기준)
-- -> 인라인 저장분은 LOB 세그먼트를 거의 소비하지 않고,
-- 아웃오브라인 저장분은 LOB 세그먼트 공간을 그만큼 소비함
-- ------------------------------------------------------------
COLUMN segment_name FORMAT A20
COLUMN mb FORMAT 999,999.99

SELECT ds.segment_name, ds.segment_type, ROUND(ds.bytes/1024/1024, 2) AS mb
FROM dba_segments ds
WHERE ds.owner = USER
AND ds.segment_name IN (
SELECT segment_name FROM dba_lobs
WHERE owner = USER AND table_name = 'PERF_TEST_LOB2'
)
ORDER BY ds.segment_name;

-- LOB 세그먼트 상세 설정 확인
SELECT column_name, segment_name, chunk, in_row, securefile, cache
FROM dba_lobs
WHERE owner = USER AND table_name = 'PERF_TEST_LOB2';

-- ------------------------------------------------------------
-- 7. (선택) FREEPOOLS/CHUNK 값을 바꿔가며 재테스트하고 싶을 때
-- 아래처럼 MOVE로 컬럼별 설정을 바꾼 뒤 3~6번을 재실행해서 비교
-- ------------------------------------------------------------
-- ALTER TABLE PERF_TEST_LOB2 MOVE LOB (doc_a) STORE AS (CHUNK 32768 FREEPOOLS 4);
-- ALTER TABLE PERF_TEST_LOB2 MOVE LOB (doc_b) STORE AS (CHUNK 32768 FREEPOOLS 4);

-- ------------------------------------------------------------
-- 8. 정리 (재테스트 전 초기화하고 싶을 때)
-- ------------------------------------------------------------
-- TRUNCATE TABLE PERF_TEST_LOB2;
-- DROP TABLE PERF_TEST_LOB2 PURGE;
-- DROP FUNCTION GET_SESS_LIO;
-- DROP FUNCTION GET_SESS_PIO;
-- DROP PROCEDURE PRINT_SESS_IO_DELTA;
</source>

Lob 테스트 - 편집 역사

Oracle: /* LOB ENABLE STORAGE IN ROW 성능 비교 테스트 */