Lob chunk size 최적화 테스트

SELECT
    MIN(DBMS_LOB.GETLENGTH(doc))    AS min_len,
    ROUND(AVG(DBMS_LOB.GETLENGTH(doc))) AS avg_len,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DBMS_LOB.GETLENGTH(doc)) AS median_len,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY DBMS_LOB.GETLENGTH(doc)) AS p90_len,
    MAX(DBMS_LOB.GETLENGTH(doc))    AS max_len
FROM 실제테이블 SAMPLE(5)
WHERE doc IS NOT NULL;

-- ============================================================
-- LOB CHUNK 크기 최적화 - 후보 값 실측 비교 스크립트
-- 목적: 여러 CHUNK 후보값에 대해 INSERT/SELECT 성능과
--       공간 효율(fragmentation)을 동일 조건에서 비교 측정
-- 방법: 테이블 1개를 두고 후보 CHUNK 값마다
--       TRUNCATE -> MOVE LOB(CHUNK 변경) -> 동일 데이터 INSERT
--       -> INSERT/SELECT 시간, 공간 사용량 측정을 반복
-- 실행 전: 4번 TEST_LOB_SIZE 값을 실제 워크로드의 대표 크기
--          (1번 분포조사에서 나온 median/avg 값)로 맞춰서 실행
-- ============================================================

SET SERVEROUTPUT ON SIZE UNLIMITED
SET TIMING OFF
SET LINESIZE 200

-- ------------------------------------------------------------
-- 0. 정리 및 테이블 생성
-- ------------------------------------------------------------
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE CHUNK_TEST_TBL PURGE';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

CREATE TABLE CHUNK_TEST_TBL (
    id  NUMBER NOT NULL,
    doc CLOB,
    CONSTRAINT pk_chunk_test PRIMARY KEY (id)
)
LOB (doc) STORE AS SECUREFILE chunk_test_lseg (
    TABLESPACE users
    ENABLE STORAGE IN ROW
    CHUNK 8192
    NOCACHE
    NOLOGGING
);

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

-- ------------------------------------------------------------
-- 1. 테스트 실행 (후보 CHUNK 값마다 반복)
--    TEST_LOB_SIZE : 실제 워크로드 대표 크기(byte)로 조정할 것
--    ROW_COUNT     : 반복 횟수(너무 크면 시간이 오래 걸림, 1000~5000 권장)
-- ------------------------------------------------------------
DECLARE
    TYPE t_chunk_list IS TABLE OF NUMBER;
    v_chunks       t_chunk_list := t_chunk_list(8192, 32768, 131072, 1048576);
                                -- 8K,      32K,       128K,     1M   -- 필요시 후보 추가/조정

    c_test_lob_size CONSTANT PLS_INTEGER := 50000;  -- <<< 실제 대표 크기로 조정
    c_row_count     CONSTANT PLS_INTEGER := 2000;

    v_start_time  NUMBER;
    v_ins_sec     NUMBER;
    v_sel_sec     NUMBER;
    v_start_pio   NUMBER;
    v_pio_ins     NUMBER;
    v_pio_sel     NUMBER;
    v_seg_bytes   NUMBER;
    v_actual_bytes NUMBER;
    v_frag_pct    NUMBER;
    v_sum_len     NUMBER;
    v_doc         CLOB;
BEGIN
    v_doc := RPAD('X', c_test_lob_size, 'X');

    DBMS_OUTPUT.PUT_LINE(RPAD('CHUNK(byte)',12) || RPAD('INSERT(초)',12) ||
                          RPAD('SELECT(초)',12) || RPAD('물리IO(SEL)',12) ||
                          RPAD('세그먼트(MB)',14) || '데이터대비 오버헤드(%)');
    DBMS_OUTPUT.PUT_LINE(RPAD('-',90,'-'));

    FOR i IN 1 .. v_chunks.COUNT LOOP
        -- (1) 초기화 + CHUNK 변경
        EXECUTE IMMEDIATE 'TRUNCATE TABLE CHUNK_TEST_TBL';
        EXECUTE IMMEDIATE
            'ALTER TABLE CHUNK_TEST_TBL MOVE LOB (doc) STORE AS (CHUNK ' || v_chunks(i) || ')';

        -- (2) INSERT 성능 측정
        v_start_time := DBMS_UTILITY.GET_TIME;
        FOR r IN 1 .. c_row_count LOOP
            INSERT INTO CHUNK_TEST_TBL (id, doc) VALUES (r, v_doc);
        END LOOP;
        COMMIT;
        v_ins_sec := (DBMS_UTILITY.GET_TIME - v_start_time) / 100;

        EXECUTE IMMEDIATE
            'BEGIN DBMS_STATS.GATHER_TABLE_STATS(:o, :t, CASCADE => TRUE); END;'
            USING USER, 'CHUNK_TEST_TBL';

        -- (3) SELECT(전체 읽기) 성능 측정
        v_start_time := DBMS_UTILITY.GET_TIME;
        v_start_pio  := GET_SESS_PIO;
        SELECT SUM(DBMS_LOB.GETLENGTH(doc)) INTO v_sum_len FROM CHUNK_TEST_TBL;
        v_sel_sec := (DBMS_UTILITY.GET_TIME - v_start_time) / 100;
        v_pio_sel := GET_SESS_PIO - v_start_pio;

        -- (4) 공간 사용량(세그먼트 크기) 및 오버헤드 비율
        SELECT bytes INTO v_seg_bytes
        FROM dba_segments
        WHERE owner = USER AND segment_name = 'CHUNK_TEST_LSEG';

        v_actual_bytes := c_test_lob_size * c_row_count;
        v_frag_pct := ROUND((v_seg_bytes - v_actual_bytes) / v_actual_bytes * 100, 1);

        DBMS_OUTPUT.PUT_LINE(
            RPAD(v_chunks(i), 12) ||
            RPAD(TO_CHAR(v_ins_sec, '999.99'), 12) ||
            RPAD(TO_CHAR(v_sel_sec, '999.99'), 12) ||
            RPAD(v_pio_sel, 12) ||
            RPAD(TO_CHAR(ROUND(v_seg_bytes/1024/1024, 2), '99999.99'), 14) ||
            TO_CHAR(v_frag_pct)
        );
    END LOOP;
END;
/

-- ------------------------------------------------------------
-- 2. 결과 해석 가이드
-- ------------------------------------------------------------
-- - INSERT(초)      : CHUNK가 너무 작으면 청크 개수가 늘어 관리 오버헤드 증가,
--                      너무 크면 큰 단위 할당 비용으로 오히려 느려질 수 있음
-- - SELECT(초)/물리IO: CHUNK가 데이터 크기에 비해 작을수록 여러 청크를
--                      나눠 읽어 물리IO 횟수가 늘어나는 경향
-- - 오버헤드(%)      : (세그먼트 실제 크기 - 순수 데이터 크기) / 데이터 크기
--                      CHUNK가 데이터보다 훨씬 크면 이 값이 커짐(공간 낭비)
--                      예: 데이터 50KB인데 CHUNK 1MB면 매 LOB이 1MB를 점유
--
-- 최적점은 보통 "오버헤드(%)가 과도하게 커지기 직전이면서
-- SELECT 물리IO가 충분히 낮아지는 지점"입니다.
-- 아래처럼 여러 대표 크기(작음/중간/큼)로 각각 돌려서
-- 실제 운영 중 크기 분포에 맞는 값을 찾는 것을 권장합니다.
--   c_test_lob_size를 3번 조정해서(예: 2000, 50000, 500000) 재실행

-- ------------------------------------------------------------
-- 3. 정리
-- ------------------------------------------------------------
-- DROP TABLE CHUNK_TEST_TBL PURGE;
-- DROP FUNCTION GET_SESS_PIO;