SQLP 튜닝 연습장

테마

📌 『튜너의 1년』 독자는 먼저 ../README.md(고객사 카드)를 본다. 이 문서는 컨테이너를 하나만 쓰던 시절에 썼다. 컨테이너 oracle-free는 ora-newshop으로 바꿔 읽는다. 12화에 필요한 것은 1~4장(ORA_SQL_TEST + FK 7개)까지다. 쓰는 화: 12.

『SQL BOOSTER』 책 원본 SQL — 챕터별 따라 치며 환경구축 & 실습 (계정 ORA_SQL_TEST) ​

『SQL BOOSTER』(유일환)의 책 원본 SQL 파일을 챕터(C1~C11) 순서대로 직접 실행하면서 환경을 만들고 실습한다. 튜닝 에센셜이나 SQL 파워업처럼 "한 줄씩 따라 치는" 방식이다. 일반판(루트, 전통 조인)과 ANSI판(ANSI_/, ANSI 조인)이 함께 있다. 둘 중 하나만 고른다. 계정: ORA_SQL_TEST / 1qaz2wsx(책 기준). ★C6의 통계 명령에 이 스키마명이 하드코딩되어 있으므로 계정명을 바꾸면 안 된다.

📌 이미 있는 SQL BOOSTER 자료와의 관계: 1_시험준비_SQLP합격/11_SQLBOOSTER_정리/ *(공개 사이트에는 없음)*는 책 정리 노트 + 축소판 스키마다(계정 sqlp, T_ORD_BIG 100만 건을 한 번에 생성하며, Hero가 쓰는 "스키마 B"). 이 폴더는 그와 별개로 책 원본 SQL을 챕터별로 그대로 돌려 보는 곳이다(계정 ORA_SQL_TEST). 둘은 계정이 달라서 함께 쓸 수 있다. 전체 데이터셋 지도는 종합안내에 있다.

0. 폴더 구성 ​

SQL_BOOSTER/
├── README.md                  ← (이 문서)
├── C1_01_DB사용자생성.sql       ← [환경구축①] 사용자·권한 (SYS로) ※책 원본은 E:\ 경로 → 아래 적응판 사용
├── C1_02_테이블생성.sql         ← [환경구축②] 9개 테이블 + FK (ORA_SQL_TEST로)
├── C1_03_데이터생성.sql         ← [환경구축③] 기준데이터 생성 (CONNECT BY, 한글 코드명 → UTF-8)
├── C2_GROUPBY_ROLLUP.sql       ┐
├── C3_JOIN.sql                 │
├── C4_유용한SQL문법.sql         │  [챕터 실습] 책 PART별 예제 SQL.
├── C5_성능기본지식.sql          │   · C6 → T_ORD_BIG(대용량) 생성
├── C6_INDEX.sql                │   · C7 → T_ORD_JOIN + 인덱스들 생성
├── C7_JOIN과성능.sql           │   · C8 → OLTP용 M_ACC/T_PO 등 생성
├── C8_OLTP_SQL기술.sql         │  (따라가며 객체가 추가됨)
├── C9_분석함수.sql             │
├── C10_페이징처리.sql          │
├── C11_SQL개발가이드.sql       ┘
└── ANSI_/                      ← 위와 동일하나 조인을 ANSI 문법으로. + C_BAS_CD_INSERT.sql

챕터 매핑(책 11장): C1 준비 · C2 GROUP BY/ROLLUP · C3 JOIN · C4 유용한 문법 · C5 성능기본 · C6 INDEX · C7 JOIN과 성능 · C8 OLTP · C9 분석함수 · C10 페이징 · C11 개발가이드. (8주/과목 매핑은 11_SQLBOOSTER_정리 개요 *(공개 사이트에는 없음)*에 있다.)

⚠️ 시작 전 2가지 ​

  1. 인코딩 = UTF-8: 파일에 한글 코드명(예: 한국어,가전제품,완료)이 들어 있다. UTF-8 클라이언트로 실행해야 깨지지 않는다(SQLcl도 기본 UTF-8, SQL Developer도 기본 UTF-8). docker exec -i ... < 파일 방식도 된다.
  2. C1_01 원본은 그대로 실행하면 안 된다: 책 원본은 E:\ORA_SQL_TEST...10G 테이블스페이스와 XE TEMP 경로를 만든다(우리 컨테이너에는 없는 경로다). 아래 1단계 적응판을 쓴다.

1. 환경구축① — 사용자 생성 (SYS로, 적응판) ​

데이터가 적어서 별도 테이블스페이스 없이 기본 USERS 테이블스페이스를 쓴다. PowerShell에서 접속한다.

powershell
docker exec -it oracle-free sqlplus sys/oracle as sysdba

SQL>에서 다음을 붙여 넣는다.

sql
ALTER SESSION SET CONTAINER = FREEPDB1;

CREATE USER ORA_SQL_TEST IDENTIFIED BY "1qaz2wsx" DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
ALTER USER ORA_SQL_TEST ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE, CREATE VIEW, UNLIMITED TABLESPACE TO ORA_SQL_TEST;

-- 책이 요구하는 측정/통계 권한 (책의 개별 V$ GRANT 대신 롤로 한 번에)
GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY, ALTER SESSION TO ORA_SQL_TEST;
GRANT EXECUTE ON DBMS_STATS TO ORA_SQL_TEST;
GRANT ALTER SYSTEM TO ORA_SQL_TEST;   -- 성능 장의 ALTER SYSTEM FLUSH BUFFER_CACHE 용
EXIT

책 원본 C1_01_DB사용자생성.sql은 참고용으로 두고, 계정은 위 적응판으로 만든다. 테이블스페이스와 TEMP RESIZE 줄은 우리 환경에서는 필요 없다.

2. 환경구축② — 테이블 9개 + FK (ORA_SQL_TEST로) ​

powershell
docker exec -i oracle-free sqlplus ORA_SQL_TEST/1qaz2wsx@localhost:1521/FREEPDB1 < "C1_02_테이블생성.sql"

(또는 SQL Developer에서 ORA_SQL_TEST로 접속한 뒤 파일을 열어 실행한다.)

  • 생성: C_BAS_CD·C_BAS_CD_DV(공통코드) / M_ITM·M_ITM_PRC_HIS·M_RGN·M_CUS(마스터) / T_ITM_EVL·T_ORD·T_ORD_DET(트랜잭션) + PK 9개 + FK 7개.
  • ★ 이 책 스키마에는 FK가 실제로 걸려 있다. 인덱스 없는 FK와 참조 무결성을 실습할 수 있다는 점이 축소판이나 PUP과 다르다.

3. 환경구축③ — 기준데이터 생성 ​

powershell
docker exec -i oracle-free sqlplus ORA_SQL_TEST/1qaz2wsx@localhost:1521/FREEPDB1 < "C1_03_데이터생성.sql"

⚠️ 『튜너의 1년』 독자는 이 블록 대신 L4 카드의 명령을 쓴다. 이대로 흘려 넣으면 C_BAS_CD(공통코드)가 오류 없이 0행이 된다. 원본 INSERT 문 중간의 공백 줄에서 SQL*Plus가 문장을 버리기 때문이다. 카드의 명령은 앞에 SET SQLBLANKLINES ON을 붙인다. 그리고 PowerShell에서는 < 리다이렉션을 쓸 수 없다.

  • CONNECT BY로 소량 생성: 상품 100·지역 5·고객 90·주문 3,047·주문상세 3,224·평가 345 + 공통코드 15(한글명).
  • ⚠️ 한글이 ???로 들어가면 UTF-8 문제다. SQL Developer(UTF-8)에서 파일을 열어 실행하거나 클라이언트 인코딩을 UTF-8로 맞춘다.

4. 검증 ​

powershell
docker exec -it oracle-free sqlplus ORA_SQL_TEST/1qaz2wsx@localhost:1521/FREEPDB1
sql
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ORA_SQL_TEST');
SET PAGESIZE 50
SELECT table_name, num_rows FROM user_tables ORDER BY 1;     -- 9개 테이블
SELECT COUNT(*) FROM T_ORD;                                   -- 주문 수만 건
-- 한글 코드명 확인(인코딩 점검)
SELECT bas_cd, bas_cd_nm FROM C_BAS_CD WHERE bas_cd_dv='ITM_TP';  -- 가전제품/컴퓨터/주방기구/옷

5. 챕터별 실습 (C2~C11) — 일반판 또는 ANSI판 택1 ​

원하는 챕터 파일을 ORA_SQL_TEST로 열어 필요한 부분만 실행한다(전통 조인이면 루트, ANSI 조인이면 ANSI_/).

powershell
# 예: 6장 인덱스 실습 (T_ORD_BIG 대용량 생성 포함 → 인덱스 전/후 성능 비교)
docker exec -i oracle-free sqlplus ORA_SQL_TEST/1qaz2wsx@localhost:1521/FREEPDB1 < "C6_INDEX.sql"
  • C6_INDEX: T_ORD_BIG(대용량)과 인덱스를 만든다. 인덱스를 만들기 전과 후의 실행계획과 Buffers를 비교하는 본무대다.
  • C7_JOIN과성능: T_ORD_JOIN과 여러 인덱스를 만들어 NL, 해시, 소트 머지 조인을 튜닝한다.
  • C8_OLTP_SQL기술: M_ACC/T_PO/시퀀스를 만들어 트랜잭션, 락, 데드락, 채번을 다룬다.
  • 나머지(C2~C5, C9~C11)는 위 테이블로 SELECT 위주의 실습을 한다.

성능 장에서는 책이 다음 측정 도구를 쓴다: SET AUTOTRACE, /*+ GATHER_PLAN_STATISTICS */ + DBMS_XPLAN.DISPLAY_CURSOR, ALTER SYSTEM FLUSH BUFFER_CACHE(격리된 실습 인스턴스에서만).

6. 데이터 모델 요약 ​

구분테이블의미키
공통코드C_BAS_CD / C_BAS_CD_DV기준코드 / 코드구분(CD_DV,LNG,CD) / (CD_DV)
마스터M_RGN지역RGN_ID
M_ITM / M_ITM_PRC_HIS상품 / 가격이력ITM_ID / (ITM_ID,TO_YMD)
M_CUS고객(RGN_ID→M_RGN)CUS_ID
트랜잭션T_ORD / T_ORD_DET주문 / 주문상세ORD_SEQ / (ORD_SEQ,ORD_DET_NO)
T_ITM_EVL상품평가(ITM_ID,EVL_LST_NO)
(성능장 생성)T_ORD_BIG(C6) · T_ORD_JOIN(C7) · M_ACC/T_PO(C8)대용량·OLTP 실습용챕터에서 생성

FK 7개: C_BAS_CD→C_BAS_CD_DV, M_ITM_PRC_HIS→M_ITM, M_CUS→M_RGN, T_ITM_EVL→M_CUS·M_ITM, T_ORD→M_CUS, T_ORD_DET→T_ORD. CUS_ID는 VARCHAR2('CUS_0001')이고, 날짜 일부는 YYYYMMDD 문자열이다(TO_YMD/BTH_YMD).

7. 트러블슈팅 ​

증상해결
C1_01 원본 실행 시 ORA-01119/02236(E:\ 경로·OMF)원본 대신 1단계 적응판(USERS) 사용
한글 코드명이 ???/깨짐UTF-8 클라이언트로 실행(SQL Developer 권장)
C6 GATHER_TABLE_STATS('ORA_SQL_TEST',...) 오류계정명이 ORA_SQL_TEST가 아니다. 계정명을 바꾸면 안 된다
FK 위반(ORA-02291)C1_02→C1_03 순서대로. 마스터 먼저, 트랜잭션 나중(파일이 이미 그 순서로 짜여 있다)
ALTER SYSTEM FLUSH ... 권한오류1단계의 GRANT ALTER SYSTEM 확인(FLUSH는 격리 인스턴스에서만)

8. 정리 / 재설치 ​

sql
-- (sys) 완전 제거 후 재구축
ALTER SESSION SET CONTAINER = FREEPDB1;
DROP USER ORA_SQL_TEST CASCADE;
-- 이후 1→3단계 반복

요약: 1) (SYS) 적응판 사용자 생성 → 2) C1_02 테이블 → 3) C1_03 데이터 → 4) 통계·검증 → 5) C2~C11 챕터 실습(C6=T_ORD_BIG 대용량). 일반판과 ANSI판 중 하나만 쓴다.