테마
📌 『튜너의 1년』 독자는 먼저
../README.md(고객사 카드)를 본다. 이 문서는 컨테이너를 하나만 쓰던 시절에 썼으므로 다음을 바꿔 읽는다. 바뀐 점: 컨테이너oracle-free→ora-burger, 계정dev/tester→dev/dev, 1단계(1.sql)는 건너뛴다(계정은launch_ora가 이미 만들었고, 권한은 카드의 GRANT 블록으로 준다). 나머지 절차와 실습 메뉴는 그대로 쓸 수 있다. 쓰는 화: 04 · 14.
오라클 튜닝 에센셜 — 실습 데이터 구축 & 실습 가이드 (완성본, 파일 1~11)
이 폴더의 정식 스크립트인
1.sql ~ 11.sql을 기준으로 한 가이드다(튜닝 에센셜 데이터는 이 가이드 하나로 본다). (예전의*_express.sql과A_…설치가이드.md는 2번 스크립트가 빠진 불완전본이라 정리해서 지웠다.) 도메인: 패스트푸드(버거) 주문. 저자가 인덱스, 클러스터링 팩터, 파티션 실습에 맞게 설계한 대용량 생성기다. 계정:dev / tester(기존sqlp와 분리). 측정 규약은 우리 방식을 그대로 따른다(AUTOTRACE,GATHER_PLAN_STATISTICS, 전후 consistent gets). [[user-prefers-handson]] 🗺 1~11을 모두 돌린 뒤의 전체 스키마 그림(ERD, 테이블 조감도, 인덱스 설명): 데이터_조감도_ERD_인덱스.md
0. 파일 지도 — 무엇을 읽고 어떤 순서로 실행하나
| # | 파일 | 하는 일 | 의존(선행) | 실행 계정 |
|---|---|---|---|---|
| 1 | 1.sql | 테이블스페이스 MY_DATA + 유저 dev 생성·권한 | — | sys |
| 2 | 2.sql | 테이블 9종 생성 + 기준데이터 INSERT(상품/단품/단품가격/매장/코드 + ORD·ORD_ITEM·ORD_TEMP 빈 껍데기) | 1 | dev |
| 3 | 3.sql | 생성 프로시저 2개 컴파일: GENERATE_ORD_BASE, INSERT_ORD_ITEM | 2 | dev |
| 4 | 4.sql | 프로시저로 주문/주문상품 대량 생성 + ORD 적재 + PK·인덱스 | 3 | dev |
| 5 | 5.sql | ORD_RANDOM·ORD_ITEM_RANDOM(랜덤 정렬 복사본). ★ 클러스터링 팩터 비교용 | 4 | dev |
| 6 | 6.comment_1.sql | 테이블/컬럼 코멘트(메타데이터) | 2 | dev |
| 7 | 7.partition.sql | ORD_LIST(LIST)·ORD_RANGE(RANGE) 파티션 테이블 + 적재 + LOCAL 인덱스 | 5 | dev |
| 8 | 8.comment_2(partition).sql | 파티션 테이블 코멘트 | 7 | dev |
| 9 | 9.partition_hash.sql | ORD_LIST_HASH(LIST + 서브파티션 HASH 8) + 적재 + LOCAL 인덱스 | 7 | dev |
| 10 | 10.comment_3(partition_hash).sql | 해시 파티션 테이블 코멘트 | 9 | dev |
| 11 | 11.price추가.sql | UITEM_PRICE 추가 데이터 | 2 | dev |
최소 실습: 1 → 2 → 3 → 4 → 5(+ 통계 수집)까지 하면 인덱스, CF, 조인 실습을 모두 할 수 있다. 파티션 실습까지 하려면 7 → 9를 더한다. 6, 8, 10, 11은 코멘트와 부가 데이터라 선택이다.
⚠️ 실행 전 꼭 알아야 할 3가지 (원본을 우리 환경에 맞추기)
- 건수 1,500만을 절대 그대로 돌리면 안 된다.
4.sql은GENERATE_ORD_BASE(15000000, ...)로 주문 1,500만 건과 주문상품 약 3,700만 건을 만든다. 저자 노트북에서도 2시간 넘게 걸리고, 23ai Free는 사용자 데이터 약 12GB라는 저장 공간 제한이 있어서 다 채우면 터진다(ORD,ORD_RANDOM, 파티션 복사본까지 합치면 수십 GB). 그러니100만(또는 50만)으로 줄여서 실행한다(아래 4단계). 100만 건이면 몇 분 안에 끝나고, 모든 튜닝 실습에 충분하다. 1.sql의 테이블스페이스 경로는 Windows 네이티브용이다(C:\ORADATA,D:\ORADATA, 30G×3=90G). 우리 환경(Docker 또는 네이티브)에 맞게 경로를 빼고(OMF) 용량을 줄여서 다시 만든다(아래 1단계). 원본의GRANT ... DBA도 권한이 지나치다.- 인코딩:
1.sql은 CP949다(주석이 깨져 보여도 SQL은 정상이다).2.sql과11.sql에는 한글 데이터(버거 이름)가 있고 UTF-8이다. SQLcl과 SQL Developer는 기본이 UTF-8이라 그대로 쓰면 된다. 혹시 한글이???로 들어가면 클라이언트를 UTF-8로 맞춘다. SQLcl은-Dfile.encoding=UTF-8, SQL*Plus는NLS_LANG=.AL32UTF8을 쓴다.
1. 1단계 — 테이블스페이스 + dev 유저 (1.sql 적응판, sys로 실행)
원본 1.sql은 그대로 실행하지 않고 아래 적응판을 쓴다(경로, 용량, 권한 조정 + PDB 전환).
(A) Docker(gvenzl/oracle-free/공식): ★ 가장 간단하다. 전용 테이블스페이스 없이 기본 USERS를 쓴다(100만 건 규모면 충분하다).
sql
-- docker exec -it oracle-free sqlplus sys/oracle as sysdba
ALTER SESSION SET CONTAINER = FREEPDB1;
CREATE USER dev IDENTIFIED BY "tester" DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PROCEDURE, UNLIMITED TABLESPACE TO dev;
GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY, ALTER SESSION TO dev; -- 실행계획/통계 실습용⚠️
CREATE TABLESPACE MY_DATA DATAFILE SIZE 2G ...(파일명 없는 문법)는 OMF(DB_CREATE_FILE_DEST)가 켜져 있어야 동작한다. 켜져 있지 않으면ORA-02236: invalid file name에러가 난다. 위처럼USERS를 쓰면 테이블스페이스를 만들 필요가 아예 없다. 그래도 전용 테이블스페이스를 쓰고 싶으면 둘 중 하나를 고른다.sql-- (B-1) OMF 켜고 원래 문법 ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/opt/oracle/oradata'; CREATE TABLESPACE MY_DATA DATAFILE SIZE 2G AUTOEXTEND ON NEXT 500M; -- (B-2) 또는 파일명 직접 지정(OMF 불필요). 경로는 SELECT name FROM v$datafile; 로 확인 CREATE TABLESPACE MY_DATA DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/my_data01.dbf' SIZE 2G AUTOEXTEND ON NEXT 500M;만들었다면 위
CREATE USER ... DEFAULT TABLESPACE의 값을MY_DATA로 바꾼다.
(B) Windows 네이티브 설치: 경로를 내 설치 위치로 바꾼다.
sql
ALTER SESSION SET CONTAINER = FREEPDB1;
-- 폴더가 실제로 존재해야 함. 예) 설치본의 oradata 경로 사용 권장. 용량은 줄임.
CREATE TABLESPACE MY_DATA DATAFILE
'C:\app\<사용자>\product\...\oradata\FREE\FREEPDB1\MY_DATA01.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G;
CREATE USER dev IDENTIFIED BY "tester" DEFAULT TABLESPACE MY_DATA QUOTA UNLIMITED ON MY_DATA;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PROCEDURE, UNLIMITED TABLESPACE TO dev;
GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY, ALTER SESSION TO dev;원본처럼
C:\ORADATA\를 쓰려면 그 폴더를 먼저 만들어야 한다(mkdir C:\ORADATA). 다만 30G×3은 너무 크니 위처럼 5G 파일 하나로 시작한다.
이후에는 sql dev/tester@localhost:1521/FREEPDB1로 접속한다(또는 SQL Developer에서 새 접속 DEV_local을 만든다).
2. 2단계 — 테이블 + 기준데이터 (2.sql, dev로 실행)
dev/tester로 접속한 뒤 2.sql을 실행한다. 만들어지는 테이블은 다음과 같다.
| 테이블 | 의미 | PK | 비고 |
|---|---|---|---|
ITEM | 상품(버거 등) | ITEM_ID | 기준데이터 INSERT 포함 |
UITEM | 단품(상품 구성 단위) | ITEM_ID, UITEM_ID | |
UITEM_PRICE | 단품가격(적용일자별) | ITEM_ID, UITEM_ID, APPLY_DT | |
SHOP | 매장 | SHOP_NO | |
COM_CD | 공통코드 | UNI_CD | |
ORD_TEMP | 생성용 임시(주문) | — | NOLOGGING |
ORD | 주문(대용량 예정) | (4단계에서 PK) | 아래 ★밀도 컬럼 |
ORD_ITEM | 주문상품(대용량 예정) | (4단계에서 PK) |
맨 앞
DROP TABLE ... PURGE에서 나는 "테이블이 없습니다" 에러는 첫 실행이라 정상이다. 무시하고 진행한다.
★ 밀도 조정용 컬럼(UPPER2, UPPER_CASE, LOWER_CASE, ALPHABET(400), ALPHABET_NUMERIC): 한 블록에 들어가는 주문 건수와 컬럼 선택도(NDV)를 조절하려고 일부러 넣은 컬럼이다.
UPPER2: 대문자 2자리라 NDV ≈ 26×26 = 676이다(약 1/676 샘플링, 중간 선택도 실습).ALPHABET(400): 행을 뚱뚱하게 만들어 "행수는 같아도 블록 수가 많은" 상황을 재현한다(테이블 풀 스캔, CF 실습).
3. 3단계 — 생성 프로시저 (3.sql, dev)
프로시저 두 개가 컴파일된다. 시그니처를 정확히 봐야 한다(인자 2개). 예전 가이드에 있던 인자 3개짜리 호출은 틀렸다.
GENERATE_ORD_BASE(P_GEN_AMOUNT NUMBER, P_COMMIT_NUM NUMBER) -- ORD_TEMP에 주문 '기반' 행 생성
INSERT_ORD_ITEM (P_GEN_AMOUNT NUMBER, P_COMMIT_NUM NUMBER) -- 주문당 1~4개 주문상품 생성P_GEN_AMOUNT는 만들 주문 건수,P_COMMIT_NUM은 커밋 단위(예: 10000)다.- 내부에서는 2012~2013년(2년)의 날짜, 시간, 매장(SH0001~SH0200), 밀도 컬럼을
DBMS_RANDOM으로 채운다.
4. 4단계 — 데이터 생성 (4.sql, dev, ★건수 줄여서)
4.sql은 ① GENERATE_ORD_BASE 실행 → ② ORD_TEMP2(날짜순 정렬 + ROWNUM을 ORD_NO로) → ③ ORD 적재 → ④ ORD_PK·ORD_X01(ORD_DT,ORD_HMS) → ⑤ INSERT_ORD_ITEM → ⑥ ORD_ITEM PK · 인덱스 순서로 진행된다.
원본을 그대로 돌리지 말고, 파일을 열어 15000000 두 곳을 1000000(100만)으로 바꾸거나 아래처럼 직접 실행한다.
sql
SET TIMING ON
SET SERVEROUTPUT ON
EXEC DEV.GENERATE_ORD_BASE(1000000, 10000); -- ★ 100만 (원본 15000000)
-- 이어서 4.sql 의 ORD_TEMP2 정렬적재 → ORD 적재 → ORD_PK → ORD_X01 블록 실행
EXEC DEV.INSERT_ORD_ITEM(1000000, 10000); -- ★ 100만 주문 → 주문상품 약 250만
-- 이어서 ORD_ITEM_PK → ORD_ITEM_X01 블록 실행포인트:
ORD는 ORD_TEMP2에서 주문일자 순으로 정렬해 적재한다. 그래서ORD_X01(ORD_DT,...)인덱스의 클러스터링 팩터가 좋다(인덱스 순서 ≈ 테이블 저장 순서). 이것이 5단계 비교의 한쪽 축이다.
5. 5단계 — 클러스터링 팩터 비교용 복사본 (5.sql, dev)
ORD_RANDOM = SELECT * FROM ORD ORDER BY DBMS_RANDOM.RANDOM이다. 즉 같은 데이터를 무작위 순서로 저장한 복사본(+ORD_ITEM_RANDOM)과, 원본과 구조가 같은 PK · *_X01 인덱스를 만든다. ORD(정렬 저장, 좋은 CF)와 ORD_RANDOM(무작위 저장, 나쁜 CF)을 비교하는 것이 이 데이터셋의 간판 실습이다.
6. 6단계 — 통계 수집 (필수) & 설치 확인
sql
-- dev 접속에서
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('DEV', degree=>4, no_invalidate=>FALSE);
SELECT table_name, num_rows FROM all_tables WHERE owner='DEV' ORDER BY table_name;
SELECT COUNT(*) FROM dev.ord; -- 약 100만
SELECT COUNT(*) FROM dev.ord_item; -- 약 250만7. (선택) 7~10단계 — 파티션 실습 데이터
7.partition.sql:ORD_LIST(월별 LIST 파티션,ORD_YM)와ORD_RANGE(월별 RANGE),ORD_ITEM_*를ORD_RANDOM에서 적재한다(그래서 5단계를 먼저 해야 한다).ORD_YM = SUBSTR(ORD_DT,1,6)컬럼이 추가된다. LOCAL 인덱스와PARALLEL힌트를 쓴다.9.partition_hash.sql:ORD_LIST_HASH(LIST + 서브파티션 HASH(ORD_NO) 8개)를ORD_LIST에서 적재한다(7단계를 먼저 해야 한다).8,10: 각 파티션 테이블의 코멘트.
병렬 INSERT가 동작하려면
ALTER SESSION ENABLE PARALLEL DML;을 먼저 실행한다. 23ai Free는 병렬도가 제한되지만 기능 자체는 동작한다. 디스크가 빠듯하면 파티션 실습용 ORD를 더 적은 건수로(예: 50만) 만들어 두고 7과 9를 돌린다. 복사본이 계속 쌓이기 때문이다.
8. ★ 실습 메뉴 — 이 데이터로 무엇을 측정하나 (핵심 4종)
공통 준비:
SET AUTOTRACE ON(또는/*+ GATHER_PLAN_STATISTICS */+DBMS_XPLAN.DISPLAY_CURSOR('ALLSTATS LAST')).ORD_DT는VARCHAR2(8)의'YYYYMMDD'문자열이다.
실습 A — 클러스터링 팩터가 비용을 좌우한다 (ORD vs ORD_RANDOM)
sql
-- (1) CF 자체를 눈으로
SELECT index_name, clustering_factor, num_rows, leaf_blocks
FROM all_indexes
WHERE owner='DEV' AND index_name IN ('ORD_X01','ORD_RANDOM_X01');
-- 관찰: ORD_X01의 CF는 작고(≈블록수), ORD_RANDOM_X01의 CF는 큼(≈행수에 근접).
-- (2) 같은 기간을 인덱스로 읽되, 테이블 컬럼(비인덱스)을 건드려 '테이블 랜덤 액세스'를 강제
SET AUTOTRACE ON
SELECT /*+ INDEX(o ORD_X01) */ MAX(o.shop_no)
FROM dev.ord o WHERE o.ord_dt BETWEEN '20120601' AND '20120630';
SELECT /*+ INDEX(o ORD_RANDOM_X01) */ MAX(o.shop_no)
FROM dev.ord_random o WHERE o.ord_dt BETWEEN '20120601' AND '20120630';
-- 관찰: 결과·행수는 같은데 ORD_RANDOM 쪽 consistent gets가 몇 배 큼.
-- "인덱스로 찾는 비용은 같아도, 테이블로 흩어진 행을 모으는 비용(CF)이 다르다"가 핵심.연계 학습: 딥다이브 강의4 인덱스 튜닝 (공개 사이트에는 없음) · Hero Lab07 인덱스 원리 (공개 사이트에는 없음)
실습 B — 인덱스 손익분기점 (범위를 넓히면 FULL이 이긴다)
sql
-- 좁은 범위(하루) → 인덱스, 넓은 범위(1년) → FULL 이 유리해지는 지점을 직접 찾기
SELECT /*+ INDEX(o ORD_X01) */ COUNT(o.shop_no) FROM dev.ord o WHERE o.ord_dt BETWEEN '20120601' AND '20120601';
SELECT /*+ FULL(o) */ COUNT(o.shop_no) FROM dev.ord o WHERE o.ord_dt BETWEEN '20120101' AND '20121231';
-- 관찰: 선택도가 커질수록 인덱스 경유(싱글블록 랜덤)가 FULL(멀티블록)보다 불리. 전환점을 Buffers로 확인.실습 C — 파티션 프루닝 (ORD_RANGE / ORD_LIST)
sql
SET AUTOTRACE ON
SELECT * FROM dev.ord_range WHERE ord_ym = '201206'; -- PARTITION RANGE SINGLE (Pstart=Pstop=한 파티션)
SELECT * FROM dev.ord_range WHERE ord_ym BETWEEN '201201' AND '201203'; -- PARTITION RANGE ITERATOR (3개만)
SELECT * FROM dev.ord_range WHERE ord_dt = '20120615'; -- ⚠️ 파티션키(ORD_YM) 아님 → 프루닝 안 됨(전 파티션)
-- 관찰: Pstart/Pstop 열로 '몇 번 파티션만 읽었는지' 확인. 파티션키로 조건을 줘야 프루닝됨.연계 학습: 딥다이브 강의8 DML·파티션·병렬 (공개 사이트에는 없음) · Hero Lab10 소트·파티션·병렬 (공개 사이트에는 없음)
실습 D — 행 밀도(rows-per-block)와 풀스캔 비용
sql
-- ALPHABET(400)로 행이 뚱뚱 → 같은 100만 행이라도 블록(=consistent gets)이 많다
SELECT blocks, num_rows, ROUND(num_rows/blocks,1) rows_per_block
FROM all_tables WHERE owner='DEV' AND table_name='ORD';
-- UPPER2(NDV≈676)로 중간 선택도 조건 만들기 (히스토그램/카디널리티 실습)
SELECT /*+ FULL(o) */ COUNT(*) FROM dev.ord o WHERE o.upper2 = 'AB'; -- 약 1/6769. 정리 / 재실행
sql
-- 데이터만 다시: dev 접속에서 5/7/9의 DROP 후 재실행하거나, 전체 초기화는 sys로
DROP USER dev CASCADE; -- (sys) 완전 제거
-- 테이블스페이스까지: DROP TABLESPACE MY_DATA INCLUDING CONTENTS AND DATAFILES;요약:
1(sys, 경로와 용량, 권한 조정) →2(테이블 + 기준 데이터) →3(프로시저) →4(★100만으로 줄여 생성) →5(RANDOM 복사본) → 통계 수집 → 실습 A/B 순서로 진행한다. 파티션까지 하려면7→9를 더한다. 핵심은 ORD와 ORD_RANDOM의 클러스터링 팩터 비교와 파티션 프루닝이다.