테마
11화 · 같은 SQL이 수만 개
🗓️ 5월 13일 (월) · 10화에서 일주일 뒤 · 무대
ora-hanorder오늘 여는 것: 여섯 조각이 함께 사는 곳, 공유 풀. 인프라팀은 "CURSOR_SHARING=FORCE로 한 방에" 정리하자고 제안한다. 범인으로 지목된 문장은 내가 2주 전에 넘긴 Q4 rev02다.
파일 세 개
| 파일 | 무엇을 하나 | 시간 | |
|---|---|---|---|
| 읽는다 | 1_사건.md | 3만 번 넘겨 보고, 서식함 앞에서 파싱을 배우고, FORCE와 피킹 끄기를 내 세션에서 재 보고, 문장을 한 줄 고치는 하루 | 40분 |
| 한다 | 3_실습과_기록.md | 3만 클릭 · 파싱 네 가지 · FORCE · ACS · 피킹 끄기 · rev03 정합성 · 부모와 자식 · IN 리스트 · (선택) 네 명 동시 · TKPROF · T1 9호 + SQL 코딩 가이드 v0.1 | 80~110분 |
| 찾는다 | 2_파고들기.md | 라이브러리 캐시 구조 · 파싱 네 가지 · V$SQL_SHARED_CURSOR 읽는 법 · 바인드 피킹 · ACS가 움직일 때와 못 움직일 때 · FORCE의 이득과 함정 · OR-Expansion의 두 얼굴 · 04031 | 필요할 때 |
1을 읽고 3을 따라 한다. 2는 막힐 때 펼친다. 시간이 없는 주에는 1_사건을 중단 지점 1까지 읽고 3_실습은 STEP 0, 1, 2, 4, 6, 7, 10만 한다(약 60분).
지난 화에서 들고 오는 것
Q4_rev02 | 09화에서 만든 포스트잇(키 기반 페이징). 어느 페이지든 15~18블록이다. 파일에는 :p_dt, :p_seq를 바인드로 적었다 |
| T1 7호 「리스크」 칸 | "포스트잇 값을 문자열로 붙이면 페이지마다 다른 SQL이 된다 → 11화"라고 적어 두기만 했다 |
| 지난주(10화) | T_ORD_BIG.ORD_ST에 히스토그램을 붙였다. 이제 오라클은 완료 90, 미결 8, 취소 2라는 분포를 안다 |
| 08화가 넘긴 것 | OR-Expansion의 :C IS NULL 갈래. 바인드 값에 따라 갈리는 계획이다 |
| 03화의 Q1 rev03 | 조회월을 이미 바인드(:p_ym)로 받는다 |
그리고 박팀장에게서 메시지가 하나 와 있다.
"인프라팀에서 공유 풀 경보 건으로 연락이 왔습니다. 모양이 거의 같은 SQL이 끝도 없이 들어왔다 나간다고 합니다. CURSOR_SHARING을 FORCE로 바꾸면 한 번에 정리된다는데, 오늘 밤 반영하겠답니다. 의견 있으면 오후 4시까지."
오늘 여는 코드
java
// 개발팀이 지난주 반영한 정산 목록 (Q4 rev02 의 구현)
String sql = "SELECT ... FROM T_ORD_BIG WHERE ORD_DT <= DATE '" + postitDate
+ "' AND (ORD_DT < DATE '" + postitDate + "' OR ORD_SEQ <= " + postitSeq + ") ...";리비전 파일에는 바인드로 적혀 있었다. 그런데 개발팀은 돌아가는 코드를 베꼈다. 09화 실습 스크립트가 SQL*Plus에서 돌리려고 포스트잇 값을 치환 변수로 박아 둔 바로 그 모양이었다. "다음"을 누를 때마다 처음 보는 문장이 하나씩 생긴다.
sql
-- 강도현 · 2019-06-17 · 정산 상태 확인 화면 — 탭(완료 · 미결 · 취소)마다 상태값이 상수
SELECT ... FROM T_ORD_BIG WHERE ORD_ST = 'CANCEL' ORDER BY ORD_DT DESC, ORD_SEQ DESC ... ROWNUM <= 1000FORCE는 공유 풀 전체에 걸리는 스위치다. 일부러 리터럴로 쓴 문장까지 바꾼다.
오늘의 전개 — 두 번 틀리고, 두 번째 안에서 화면이 100만 블록이 된다
| 내가 믿은 것 | 측정 | |
|---|---|---|
| 아침 | "한 번에 15블록이면 문제없다" | 3만 번 클릭에 36.19초, 하드 파싱 30,431번, 그중 파싱 CPU 34.66초. 공유 풀에 남은 건 300~400개뿐이다. 수만 개가 지나갔고 이웃(Q1)까지 밀려났다 |
| — | 바인드로 3만 번 | 0.67초, 하드 파싱 1번. 네 명이 동시에 누르면 클릭 1,000번당 CPU가 135.5 → 3.4cs |
| 1차 | "그럼 FORCE를 켜 두면 된다" | 여섯 조각의 계획은 그대로다. Q4 하드 파싱은 30,431 → 329(DATE 리터럴은 날짜마다 자식이 생긴다). 그런데 상태 탭이 하나로 묶여 첫 손님 값이 계획을 정한다. 취소 탭 5,826 → 50,347, 또는 완료 탭 1,016 → 5,826 |
| 2차 | "ACS가 알아서 고쳐 준다" | 1,000건 화면은 못 고친다(일량 칸이 같다). 100건 화면은 한 번 나쁘게 돈 뒤에 고친다. 그런데 statistics_level=ALL로 쟀더니 고쳐졌다. 재는 도구가 결과를 바꿨다 |
| 두 번째 안 | 인프라팀의 "피킹을 끄자" | rev02를 바인드로 고친 문장이 2페이지에서 1,005,991블록을 읽는다. 값을 모르니 OR를 두 갈래로 펼쳐 둘 다 읽는다(08화의 VW_ORE_) |
| rev03 | 같은 뜻을 OR 없이 | 피킹을 켜도, 꺼도, DBMS_SQL로 돌려도 모두 14~17블록. 10만 페이지 정합성 검사에서 mismatch 0 |
왜 이렇게 짰을까? 09화에서는 "PGA부터 올리자"는 제안을 재 보고 반박했다. 오늘은 같은 모양의 제안이 공유 풀 쪽에서 온다. 이번에는 반박으로 끝내지 않는다. 서버 스위치가 대신해 줄 수 없는 일, 곧 어느 값을 바인드로 쓰고 어느 값을 리터럴로 쓸지 정하는 일을 개발팀이 읽는 문서로 만든다.
오늘 배우는 한 문장
같은 SQL인지는 텍스트가 정하고, 같은 계획을 써도 되는지는 값이 정한다. 값이 바뀌어도 같은 길로 가야 하는 곳은 바인드로, 값마다 길이 달라야 하는 곳은 리터럴로 쓴다. 그 구분은 SQL을 쓰는 사람이 한다. 서버 스위치(FORCE, 피킹 끄기)는 그 구분을 대신하지 못한다.
| 서버 스위치 | 무엇을 고치나 | 무엇을 망가뜨리나 (실측) |
|---|---|---|
CURSOR_SHARING=FORCE | 리터럴만 다른 문장을 하나로 묶는다(3만 클릭 하드 파싱 30,431 → 329) | 일부러 박은 리터럴까지 하나로 묶는다(취소 탭 50,347) · DATE는 값마다 자식 · 결과 칸 길이 16 → 160 |
| 바인드 피킹 끄기 | "첫 손님"이 계획을 정하는 문제 | 값을 모르고 세운 계획(상태 탭은 늘 50,347, rev02 2페이지는 1,005,991) |
🔑 오늘의 비유 — 결재 서식함
복합기 옆에 철제 캐비닛이 있고, 칸마다 결재 양식이 꽂혀 있다. 양식에는 빈칸(금액, 날짜)과 미리 인쇄된 결재선이 있다.
| 서식함 | 오라클 |
|---|---|
| 서식함(칸을 이름으로 찾는다) | 라이브러리 캐시(SQL 텍스트의 해시로 찾는다) |
| 총무팀에 새 양식을 주문한다: 문구 확인 · 결재선 정하기 · 인쇄 | 하드 파싱: 문법 · 의미 확인 · 최적화 · 실행계획 생성 |
| 금액을 제목에 인쇄해 매번 새 양식을 주문한다 | 리터럴을 이어 붙인 동적 SQL |
| 빈칸이 있는 양식 하나 | 바인드 변수 |
| 서식함까지 가서 칸을 찾아 꺼냄 · 칸 위치를 수첩에 적어 둠 · 책상 위에 펼쳐 두고 다시 씀 | 소프트 파싱 · 세션 커서 캐시 · 커서를 열어 둔 채 재실행 |
| 같은 칸에 결재선이 다른 양식 여러 장 · "왜 따로 꽂혔나" 쪽지 | 부모 커서 · 자식 커서 · V$SQL_SHARED_CURSOR |
| 양식을 처음 인쇄할 때 첫 문서의 금액을 보고 결재선을 정한다 | 바인드 피킹 |
| 총무팀이 모든 문서의 숫자를 지워 빈칸 양식으로 만든다. 일부러 셋으로 나눈 양식까지 하나로 합친다 | CURSOR_SHARING=FORCE |
| 처리 시간을 대 · 중 · 소 세 칸으로만 적어 두었다가, 칸이 다르면 양식을 나눈다. 책상 위에 펼쳐 둔 양식은 나눠야 하는지 모른다 | ACS: V$SQL_CS_HISTOGRAM 세 칸 · 파스 호출이 없으면 자식을 못 바꾼다 |
| 서식함이 차면 오래 안 쓴 양식부터 버린다. 내 휴가신청서까지 버려진다 | 공유 풀의 밀어내기(이웃 Q1이 밀려났다) |
(01화 커피 캐리어 = 블록 · 02화 방문객 출입대장 = 인덱스 · 06화 4층 거래처 카드함 = 조인 · 07화 탕비실 픽업 선반 = 버퍼 캐시 · 08화 회식 정산서 = 조인 카디널리티 · 09화 출입대장 포스트잇 = 키 기반 페이징)
☠️ 오늘의 함정 — 재는 도구가 결과를 바꾼다
DISPLAY_CURSOR(… 'ALLSTATS LAST')를 쓰려고 statistics_level = ALL을 켜 둔 채 ACS를 쟀더니 완료 탭이 고쳐졌다. 운영과 같은 TYPICAL로 다시 재자 고쳐지지 않았다.
| 같은 문장 · 같은 순서 (취소 먼저 → 완료 여러 번) | 완료 탭 Buffers | V$SQL_CS_HISTOGRAM |
|---|---|---|
statistics_level = TYPICAL (운영) | 5,826 · 5,826 · 5,826 · 5,826 | 1:5 |
statistics_level = ALL | 5,826 · 1,016 · 1,016 | 1:1 2:1 → 자식 1 |
측정 조건은 운영과 같게 맞추고, 다르면 다르다고 적는다. 그래서 이 화의 ACS와 FORCE 측정은 TYPICAL에서 했고, 한 번 실행한
Buffers는V$SQL.BUFFER_GETS가 늘어난 양으로 쟀다.
실습 준비
bash
docker start ora-hanorder10화를 마친 상태에서 시작한다. T_ORD_BIG.ORD_ST에 FREQUENCY 히스토그램이 있고, 테이블 선호도는 METHOD_OPT = 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST'이며, 인덱스는 3개다. 10화를 마쳤다면 이미 그 상태다. 아니어도 괜찮다. 00, 01, 02, 03 스크립트 맨 앞의 [준비] 단계가 상태를 확인하고, 없으면 같은 상태를 만든다(있으면 손대지 않는다).
PowerShell에서는 먼저
$OutputEncoding = New-Object System.Text.UTF8Encoding $false한 줄을 실행한다.
| 스크립트 | 무엇 | 걸리는 시간 |
|---|---|---|
실습/00_공유풀의_3만_개.sql | [준비] · 3만 번 클릭(리터럴 · 바인드) · 밀려나는 이웃 · 탐지 쿼리 | 약 40초(일부러 느리다) |
실습/01_FORCE_와_여섯_조각.sql | FORCE를 내 세션에만 켠다: 여섯 조각 · HASH_MATCH_FAILED · 상태 탭 · CURSOR_SHARING_EXACT · 칸 길이 · 3만 클릭 | 약 5초 |
실습/02_ACS_는_언제_움직이나.sql | 1,000건 · 100건 · statistics_level · PL/SQL 열어 둔 채 | 약 3초 |
실습/03_피킹을_끄면.sql | 상태 탭 · rev02 2페이지 1,005,991 · rev03 · 08화 NVL(:c) | 약 5초 |
실습/04_포스트잇_rev03.sql | rev03 페이지 곡선 · 정합성 B · C · E · 10만 페이지 순회 | 약 5초 |
실습/05_파싱_네_가지.sql | 하드 · 소프트 · 세션 캐시 · 열어 둔 채 · DBMS_SQL의 값 없는 파싱 | 약 3초 |
실습/06_부모와_자식.sql | VERSION_COUNT · V$SQL_SHARED_CURSOR Y 칸 · 바인드 그릇 | 1초 |
실습/07_IN리스트.sql | 길이가 매번 다른 IN 리스트, 네 가지 방법 | 1초 |
실습/08a · 08b_일꾼_리터럴 · 08b_일꾼_바인드 · 08c | (선택) 네 명이 동시에(07화 03 시리즈 방식) | 약 10초 |
실습/90_10046_TKPROF.sql | (선택) 10046 level 12 → 컨테이너 안에서 TKPROF → docker cp | 1초 + 꺼내기 |
실습/91_라이브러리캐시_SYSDBA.sql | (선택 · SYSDBA) X$KGLOB 핸들 · heap 0 · heap 6 · V$LIBRARYCACHE | 1초 |
어느 스크립트든 몇 번을 돌려도 결과가 같다. 측정 문장마다 실행 표식(R+시각)을 붙여서 실행할 때마다 새 문장 무리를 만든다. 이 화의 주제가 "같은 문장인가"이므로, 표식이 SQL 텍스트를 바꾼다는 점을 그대로 이용한다. 원본 테이블에는 쓰지 않는다. CURSOR_SHARING, optimizer_mode, _optim_peek_user_binds, statistics_level은 모두 세션 단위로 바꾸고, 스크립트 끝에서 되돌린 뒤 확인한다. 공유 풀 FLUSH는 하지 않는다.
리비전은 리비전/Q4_rev03.sql에 남긴다. Q1, Q2, Q3는 리비전을 만들지 않는다. Q1은 이미 바인드를 쓰고, Q2와 Q3는 입력값이 없으며, FORCE를 켜도 계획이 그대로다.
ℹ️ 이 교재의 실습은 Docker로 띄운 Oracle Database Free 한 대로 모두 끝낸다. 수백 세션이 하드 파싱을 두고 다투는 규모의 경합이나, 공유 풀이 실제로
ORA-04031을 내는 장면은 이 컨테이너로 재현하지 않았다. 3만 개를 넣어도 공유 풀은 에러 대신 밀어내기로 버텼고, 여기서는 그 밀려나는 모습을 쟀다. 대규모 EE 환경은 별권에서 다룬다.
이 화의 산출물
| 무엇 | 어디 |
|---|---|
Q4_rev03: 포스트잇 바인드 + OR 없는 조건. 값 없이 파싱될 때 2페이지 1,005,991 → 14 · 3만 클릭 하드 파싱 30,431 → 1 | 리비전/Q4_rev03.sql |
| T1 리포트 9호: 「기각한 대안」 셋(FORCE · FORCE + 피킹 끄기 · rev02 바인드만) + 08화 정합성 체크리스트 | 3_실습과_기록.md STEP 10 |
| 「SQL 코딩 가이드」 v0.1 ★ 새 표준 문서. 개발팀이 읽는 규칙 | 40_보고서와_표준/템플릿/SQL_코딩가이드.md |
이 화가 다음으로 넘기는 것
| 무엇 | 어디로 |
|---|---|
| 하드 파싱이 줄어 처리량이 오르면 그 뒤에 숨어 있던 줄서기가 데이터 쪽에서 드러난다 | 12화 |
T_ORD_BIG 인덱스 3개(변화 없음)의 적재 비용 | 14화 |
PL/SQL이 커서를 열어 둔 채 다시 쓸 때: 파스 호출 0의 이득과 ACS를 잃는 대가 · DBMS_SQL의 값 없는 파싱 | 15화 |
| 「SQL 코딩 가이드」 v0.1 → v1.0 (2회 이상 되풀이된 원인만 조문으로) | 18화 |
| 화면에 검색 조건이 붙는 날(09화 재협상서 1호의 조건) | 조건이 걸리면 다시 잰다 |
← 10화 「어제는 0.1초 오늘은 30초」 · 교재 전체 안내 · 다음 → 12화 「아무도 안 건드렸는데 전체가 멈췄다」