2026년 9월 15일 (화) 03:47에 Oracle님의 편집

2026-09-15T03:47:14Z

← 이전 판		2026년 9월 15일 (화) 12:47 판
1번째 줄:		1번째 줄:
			<source lang=sql>
	SELECT		SELECT
	MIN(DBMS_LOB.GETLENGTH(doc)) AS min_len,		MIN(DBMS_LOB.GETLENGTH(doc)) AS min_len,
7번째 줄:		8번째 줄:
	FROM 실제테이블 SAMPLE(5)		FROM 실제테이블 SAMPLE(5)
	WHERE doc IS NOT NULL;		WHERE doc IS NOT NULL;
			</source>

	<source lang=sql>		<source lang=sql>

Oracle: 새 문서: 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 크기...

2026-09-15T03:22:42Z

새 문서: 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; <source lang=sql> -- ============================================================ -- LOB CHUNK 크기...

새 문서

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;

<source lang=sql>
-- ============================================================
-- 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;
</source>

Lob chunk size 최적화 테스트 - 편집 역사

2026년 9월 15일 (화) 03:47에 Oracle님의 편집