Lob 테스트

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

-- ============================================================
-- 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;

storage in row 옵션 성능 개선 테스트

-- ============================================================
-- ENABLE STORAGE IN ROW vs DISABLE STORAGE IN ROW 성능 비교 테스트
-- 목적: "작은 크기(4000byte 이하) LOB"을 저장할 때
--       ENABLE STORAGE IN ROW(인라인 허용)와
--       DISABLE STORAGE IN ROW(항상 LOB세그먼트로 분리)의
--       INSERT/SELECT 성능, I/O, 공간 사용량 차이를 측정
-- 구성: 한 테이블에 LOB 컬럼 2개
--       DOC_INROW  -> ENABLE STORAGE IN ROW  (옵션 켬)
--       DOC_OUTROW -> DISABLE STORAGE IN ROW (옵션 끔, 항상 아웃오브라인)
--       두 컬럼에 "완전히 동일한 크기의 작은 데이터"를 넣어 공정 비교
-- 실행: sqlplus 계정/비번@dsn @lob_inrow_option_test.sql
-- ============================================================

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

-- ------------------------------------------------------------
-- 0. 기존 오브젝트 정리 (재실행 대비)
-- ------------------------------------------------------------
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE PERF_TEST_INROW PURGE';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

-- ------------------------------------------------------------
-- 1. 테스트 테이블 생성
--    DOC_INROW  : ENABLE STORAGE IN ROW  (기본값, 작은 값은 행에 인라인 저장)
--    DOC_OUTROW : DISABLE STORAGE IN ROW (항상 별도 LOB세그먼트에 저장)
--    나머지 옵션(CHUNK 등)은 동일하게 맞춰서 옵션 하나만의 차이를 봄
-- ------------------------------------------------------------
CREATE TABLE PERF_TEST_INROW (
    id           NUMBER          NOT NULL,
    created_date DATE            DEFAULT SYSDATE,
    doc_inrow    CLOB,
    doc_outrow   CLOB,
    CONSTRAINT pk_perf_test_inrow PRIMARY KEY (id)
)
LOB (doc_inrow) STORE AS SECUREFILE inrow_lseg (
    TABLESPACE users
    ENABLE STORAGE IN ROW
    CHUNK 8192
    NOCACHE
    NOLOGGING
)
LOB (doc_outrow) STORE AS SECUREFILE outrow_lseg (
    TABLESPACE users
    DISABLE STORAGE IN ROW
    CHUNK 8192
    NOCACHE
    NOLOGGING
);

-- ------------------------------------------------------------
-- 2. 세션 I/O 측정 헬퍼 함수 (이미 있다면 재생성됨)
-- ------------------------------------------------------------
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;
/

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 := GET_SESS_LIO;
    v_pio NUMBER := GET_SESS_PIO;
BEGIN
    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;
/

-- ------------------------------------------------------------
-- 3. 테스트 1: DOC_INROW 컬럼에 작은 값 INSERT (4000byte 이하 -> 인라인 저장)
-- ------------------------------------------------------------
DECLARE
    v_start_time NUMBER;
    v_start_lio  NUMBER;
    v_start_pio  NUMBER;
    v_row_count  PLS_INTEGER := 5000;
    v_small_text CLOB;
BEGIN
    v_small_text := RPAD('A', 500, 'A');   -- 500byte, 동일 크기로 공정 비교

    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_INROW (id, doc_inrow)
        VALUES (i, v_small_text);
    END LOOP;
    COMMIT;

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

-- ------------------------------------------------------------
-- 4. 테스트 2: DOC_OUTROW 컬럼에 "완전히 동일한" 작은 값 INSERT
--    (DISABLE STORAGE IN ROW이므로 크기와 무관하게 항상 아웃오브라인)
-- ------------------------------------------------------------
DECLARE
    v_start_time NUMBER;
    v_start_lio  NUMBER;
    v_start_pio  NUMBER;
    v_row_count  PLS_INTEGER := 5000;
    v_small_text CLOB;
BEGIN
    v_small_text := RPAD('A', 500, 'A');   -- 위와 동일한 500byte

    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
        UPDATE PERF_TEST_INROW
        SET doc_outrow = v_small_text
        WHERE id = i;
    END LOOP;
    COMMIT;

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

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

-- ------------------------------------------------------------
-- 5. 조회 성능 비교: 같은 크기(500byte)인데 저장 방식만 다른 두 컬럼
--    DBMS_LOB.GETLENGTH로 실제 LOB 값을 건드리는 조회를 유도
-- ------------------------------------------------------------
DECLARE
    v_start_time NUMBER;
    v_start_lio  NUMBER;
    v_start_pio  NUMBER;
    v_sum_len    NUMBER;
BEGIN
    -- DOC_INROW 조회
    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_inrow)) INTO v_sum_len
    FROM PERF_TEST_INROW;

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

    -- DOC_OUTROW 조회
    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_outrow)) INTO v_sum_len
    FROM PERF_TEST_INROW;

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

-- ------------------------------------------------------------
-- 6. 핵심 검증: LOB 세그먼트 공간 사용량 비교
--    -> 같은 크기(500byte)인데도 DISABLE STORAGE IN ROW는
--       LOB 세그먼트 공간을 그만큼 소비하고,
--       ENABLE STORAGE IN ROW는 거의 소비하지 않아야 정상
-- ------------------------------------------------------------
COLUMN segment_name FORMAT A20
COLUMN mb FORMAT 999,999.99

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

-- 컬럼별 IN_ROW 설정 및 실제 LOB 세그먼트 정보 확인
SELECT column_name, segment_name, in_row, securefile, chunk
FROM dba_lobs
WHERE owner = USER AND table_name = 'PERF_TEST_INROW';

-- ------------------------------------------------------------
-- 7. 결과 해석 가이드
-- ------------------------------------------------------------
-- - INROW_LSEG 의 MB가 OUTROW_LSEG 대비 현저히 작다면
--   (거의 0에 가깝다면) ENABLE STORAGE IN ROW가 정상 동작 중인 것
-- - DOC_INROW SELECT의 physical_reads(delta)가 DOC_OUTROW SELECT보다
--   작게 나오면, 인라인 저장이 실제로 I/O를 줄여주고 있다는 증거
-- - 두 컬럼의 physical_reads가 비슷하다면 OS/버퍼캐시에 이미
--   캐싱된 상태일 수 있으므로, 재테스트 전 아래로 캐시를 비우고 확인
--   ALTER SYSTEM FLUSH BUFFER_CACHE;   -- 테스트/개발 환경에서만 사용
--
-- 5000byte 등 4000byte를 "초과"하는 값으로 위 3~4번을 다시 돌려보면
-- ENABLE STORAGE IN ROW라도 자동으로 아웃오브라인 전환되어
-- DOC_INROW와 DOC_OUTROW의 결과가 비슷해지는 것도 확인할 수 있음
-- (옵션의 의미: "작을 때만" 인라인을 "허용"하는 것이지 강제하는 게 아님)

-- ------------------------------------------------------------
-- 8. 정리
-- ------------------------------------------------------------
-- DROP TABLE PERF_TEST_INROW PURGE;