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;