실행계획 읽기 실습 - 편집 역사

https://dbstudy.co.kr/w/index.php?action=history&feed=atom&title=%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D_%EC%9D%BD%EA%B8%B0_%EC%8B%A4%EC%8A%B5 실행계획 읽기 실습 - 편집 역사 2026-11-04T06:06:03Z 이 문서의 편집 역사 MediaWiki 1.39.10 https://dbstudy.co.kr/w/index.php?title=%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D_%EC%9D%BD%EA%B8%B0_%EC%8B%A4%EC%8A%B5&diff=1992&oldid=prev Dbstudy: 상단 배너를 {{타이틀 주제}} 틀로 교체 2026-11-04T04:26:20Z

<p>상단 배너를 {{타이틀 주제}} 틀로 교체</p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="ko"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← 이전 판</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">2026년 11월 4일 (수) 13:26 판</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">1번째 줄:</td> <td colspan="2" class="diff-lineno">1번째 줄:</td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><div style="background:linear-gradient(135deg,#6e2c00 0%,#a04000 55%,#d35400 100%);color:#ffffff;padding:30px 36px;border-radius:14px;margin-bottom:24px;box-shadow:0 4px 14px rgba(110,44,0,0.25);"></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">{{타이틀 주제</ins></div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><div style</del>=<del style="font-weight: bold; text-decoration: none;">"font-size:12px;letter-spacing:3px;opacity:0.8;margin-bottom:6px;"></del>ORACLE SQL TUNING · HANDS-ON LAB 1<del style="font-weight: bold; text-decoration: none;"></div></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">|부제 </ins>= ORACLE SQL TUNING · HANDS-ON LAB 1</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><div style</del>=<del style="font-weight: bold; text-decoration: none;">"font-size:28px;font-weight:700;line-height:1.3;"></del>실행계획 읽기 실습<del style="font-weight: bold; text-decoration: none;"></div></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">|제목 </ins>= 실행계획 읽기 실습</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><div style</del>=<del style="font-weight: bold; text-decoration: none;">"font-size:15px;opacity:0.92;margin-top:10px;line-height:1.7;"></del>DBMS_XPLAN 포맷부터 오퍼레이션 해석, 병목 단계 찾기까지 — 10개 실습으로 익히는 실행계획<del style="font-weight: bold; text-decoration: none;"></div></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">|설명 </ins>= DBMS_XPLAN 포맷부터 오퍼레이션 해석, 병목 단계 찾기까지 — 10개 실습으로 익히는 실행계획</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><div style</del>=<del style="font-weight: bold; text-decoration: none;">"margin-top:16px;display:flex;flex-wrap:wrap;gap:8px;"></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">|색상 </ins>= <ins style="font-weight: bold; text-decoration: none;">갈색</ins></div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><span style</del>=<del style="font-weight: bold; text-decoration: none;">"background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;"></del>선수 학습 · [[SQL 튜닝 입문]] 4~5장<del style="font-weight: bold; text-decoration: none;"></span></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">|배지1 </ins>= 선수 학습 · [[SQL 튜닝 입문]] 4~5장</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><span style</del>=<del style="font-weight: bold; text-decoration: none;">"background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;"></del>커리큘럼 · 5주차<del style="font-weight: bold; text-decoration: none;"></span></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">|배지2 </ins>= 커리큘럼 · 5주차</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><span style</del>=<del style="font-weight: bold; text-decoration: none;">"background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;"></del>기준 · Oracle 19c<del style="font-weight: bold; text-decoration: none;"></span></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">|배지3 </ins>= 기준 · Oracle 19c</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"></div></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">}}</ins></div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"></div></del></div></td><td colspan="2" class="diff-side-added"></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>__TOC__</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>__TOC__</div></td></tr> </table>

Dbstudy https://dbstudy.co.kr/w/index.php?title=%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D_%EC%9D%BD%EA%B8%B0_%EC%8B%A4%EC%8A%B5&diff=1982&oldid=prev Dbstudy: 새 문서: <div style="background:linear-gradient(135deg,#6e2c00 0%,#a04000 55%,#d35400 100%);color:#ffffff;padding:30px 36px;border-radius:14px;margin-bottom:24px;box-shadow:0 4px 14px rgba(110,44,0,0.25);"> <div style="font-size:12px;letter-spacing:3px;opacity:0.8;margin-bottom:6px;">ORACLE SQL TUNING · HANDS-ON LAB 1</div> <div style="font-size:28px;font-weight:700;line-height:1.3;">실행계획 읽기 실습</div> <div style="font-size:15px;opacity:0.92;margin-top:10px;line-height:1.7... 2026-11-04T03:55:03Z

<p>새 문서: <div style="background:linear-gradient(135deg,#6e2c00 0%,#a04000 55%,#d35400 100%);color:#ffffff;padding:30px 36px;border-radius:14px;margin-bottom:24px;box-shadow:0 4px 14px rgba(110,44,0,0.25);"> <div style="font-size:12px;letter-spacing:3px;opacity:0.8;margin-bottom:6px;">ORACLE SQL TUNING · HANDS-ON LAB 1</div> <div style="font-size:28px;font-weight:700;line-height:1.3;">실행계획 읽기 실습</div> <div style="font-size:15px;opacity:0.92;margin-top:10px;line-height:1.7...</p> <p><b>새 문서</b></p><div><div style="background:linear-gradient(135deg,#6e2c00 0%,#a04000 55%,#d35400 100%);color:#ffffff;padding:30px 36px;border-radius:14px;margin-bottom:24px;box-shadow:0 4px 14px rgba(110,44,0,0.25);"><br /> <div style="font-size:12px;letter-spacing:3px;opacity:0.8;margin-bottom:6px;">ORACLE SQL TUNING · HANDS-ON LAB 1</div><br /> <div style="font-size:28px;font-weight:700;line-height:1.3;">실행계획 읽기 실습</div><br /> <div style="font-size:15px;opacity:0.92;margin-top:10px;line-height:1.7;">DBMS_XPLAN 포맷부터 오퍼레이션 해석, 병목 단계 찾기까지 — 10개 실습으로 익히는 실행계획</div><br /> <div style="margin-top:16px;display:flex;flex-wrap:wrap;gap:8px;"><br /> <span style="background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;">선수 학습 · [[SQL 튜닝 입문]] 4~5장</span><br /> <span style="background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;">커리큘럼 · 5주차</span><br /> <span style="background:rgba(255,255,255,0.18);border:1px solid rgba(255,255,255,0.35);padding:4px 12px;border-radius:20px;font-size:12px;">기준 · Oracle 19c</span><br /> </div><br /> </div><br /> <br /> __TOC__<br /> <br /> == 1. 실습 준비 ==<br /> === 1.1 권한과 환경 ===<br /> <syntaxhighlight lang="sql"><br /> -- DISPLAY_CURSOR에 필요한 권한 (DBA가 부여)<br /> GRANT SELECT ON V_$SQL_PLAN TO lab_user;<br /> GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO lab_user;<br /> GRANT SELECT ON V_$SQL TO lab_user;<br /> GRANT SELECT ON V_$SESSION TO lab_user;<br /> -- 또는 GRANT SELECT_CATALOG_ROLE TO lab_user;<br /> <br /> -- SQL*Plus 출력 설정<br /> SET LINESIZE 200 PAGESIZE 0 SERVEROUTPUT OFF<br /> </syntaxhighlight><br /> <br /> <div style="border-left:5px solid #c0392b;background:#fdf1ef;padding:12px 18px;margin:14px 0;border-radius:0 8px 8px 0;"><br /> ⚠ <code>SERVEROUTPUT ON</code> 상태에서는 SQL*Plus가 실행 후 <code>DBMS_OUTPUT.GET_LINES</code>를 호출하므로, <code>DISPLAY_CURSOR(NULL, NULL)</code>이 '''방금 실행한 SQL이 아닌 그 호출의 계획'''을 보여준다. 반드시 <code>SERVEROUTPUT OFF</code>.<br /> </div><br /> <br /> === 1.2 실습 테이블 ===<br /> <syntaxhighlight lang="sql"><br /> CREATE TABLE lab_cust NOLOGGING AS<br /> SELECT 'C' || LPAD(LEVEL, 6, '0') AS cust_no,<br /> 'CUST_' || LEVEL AS cust_nm,<br /> DECODE(MOD(LEVEL, 10), 0, 'VIP', 'NORMAL') AS grade_cd,<br /> DECODE(MOD(LEVEL, 5), 0, '서울', 1, '부산', 2, '대구', 3, '광주', '대전') AS region<br /> FROM dual CONNECT BY LEVEL <= 50000;<br /> <br /> CREATE TABLE lab_order NOLOGGING AS<br /> SELECT 'O' || LPAD(LEVEL, 10, '0') AS order_no,<br /> DATE '2024-01-01' + MOD(LEVEL, 1000) AS order_dt,<br /> 'C' || LPAD(MOD(LEVEL, 50000) + 1, 6, '0') AS cust_no,<br /> DECODE(MOD(LEVEL, 100), 0, '90', 1, '30', '10') AS stat_cd,<br /> ROUND(DBMS_RANDOM.VALUE(1000, 500000)) AS order_amt<br /> FROM dual CONNECT BY LEVEL <= 3000000;<br /> <br /> ALTER TABLE lab_cust ADD CONSTRAINT pk_lab_cust PRIMARY KEY (cust_no);<br /> ALTER TABLE lab_order ADD CONSTRAINT pk_lab_order PRIMARY KEY (order_no);<br /> CREATE INDEX ix_lab_order_01 ON lab_order (cust_no, order_dt);<br /> CREATE INDEX ix_lab_order_02 ON lab_order (order_dt);<br /> <br /> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LAB_CUST');<br /> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LAB_ORDER');<br /> </syntaxhighlight><br /> <br /> == 2. DBMS_XPLAN 완전 정복 ==<br /> === 2.1 함수 종류 ===<br /> {| class="wikitable" style="width:100%;"<br /> |- style="background:#6e2c00;color:#fff;"<br /> ! 함수 !! 소스 !! 용도<br /> |-<br /> | <code>DISPLAY</code> || PLAN_TABLE (EXPLAIN PLAN) || 예상 계획 확인<br /> |-<br /> | <code>DISPLAY_CURSOR</code> || V$SQL_PLAN (Library Cache) || '''실제 실행된 계획 + 실측 통계'''<br /> |-<br /> | <code>DISPLAY_AWR</code> || DBA_HIST_SQL_PLAN || 과거 계획 이력, 계획 변경 추적<br /> |-<br /> | <code>DISPLAY_SQL_PLAN_BASELINE</code> || SPM || 고정된 Baseline 계획 확인<br /> |-<br /> | <code>DISPLAY_SQLSET</code> || SQL Tuning Set || STS에 저장된 계획<br /> |}<br /> <br /> === 2.2 포맷 옵션 ===<br /> {| class="wikitable" style="width:100%;font-size:13px;"<br /> |- style="background:#fbeee6;"<br /> ! 포맷 !! 보여주는 것 !! 언제<br /> |-<br /> | <code>BASIC</code> || Id, Operation, Name만 || 구조만 빠르게<br /> |-<br /> | <code>TYPICAL</code> (기본) || + Rows, Bytes, Cost, Time, Predicate || 일반 확인<br /> |-<br /> | <code>ALL</code> || + Query Block, Column Projection || 컬럼 흐름 추적<br /> |-<br /> | <code>ADVANCED</code> || ALL + Outline + Peeked Binds || 계획 재현·힌트 추출<br /> |-<br /> | <code>ALLSTATS LAST</code> || '''Starts, E-Rows, A-Rows, A-Time, Buffers, Reads''', 메모리 사용 || '''튜닝 시 기본'''<br /> |-<br /> | <code>+PEEKED_BINDS</code> || 하드 파싱 시 엿본 바인드 값 || 바인드 영향 분석<br /> |-<br /> | <code>+OUTLINE</code> || 계획을 재현하는 전체 힌트 세트 || 힌트 작성 참고<br /> |-<br /> | <code>+ADAPTIVE</code> || 적응적 계획에서 비활성 단계(-)까지 표시 || 12c+ Adaptive Plan<br /> |-<br /> | <code>+NOTE</code> / <code>-NOTE</code> || Dynamic Sampling, SPM, Profile 사용 여부 || 계획 결정 근거<br /> |}<br /> <br /> === 2.3 실측 통계 수집 방법 ===<br /> <syntaxhighlight lang="sql"><br /> -- 방법 1: 힌트 (해당 SQL만)<br /> SELECT /*+ GATHER_PLAN_STATISTICS */ ... ;<br /> <br /> -- 방법 2: 세션 단위 (여러 SQL을 연속 분석할 때)<br /> ALTER SESSION SET statistics_level = ALL;<br /> <br /> -- 확인<br /> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));<br /> <br /> -- 다른 세션 SQL은 SQL_ID와 CHILD_NUMBER 지정<br /> SELECT sql_id, child_number, plan_hash_value, executions<br /> FROM v$sql WHERE sql_text LIKE '%찾을 문자열%' AND sql_text NOT LIKE '%v$sql%';<br /> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_no, 'ALLSTATS LAST +PEEKED_BINDS'));<br /> </syntaxhighlight><br /> <br /> <div style="border:1px solid #f0d9b5;background:#fffaf0;padding:12px 18px;border-radius:8px;margin:14px 0;"><br /> 💡 '''ALLSTATS vs ALLSTATS LAST''' — <code>LAST</code> 없이 쓰면 '''모든 실행의 누적값'''이 나온다. 튜닝 전후 비교는 항상 <code>LAST</code>로 '''마지막 1회'''를 본다.<br /> </div><br /> <br /> == 3. 실행계획 컬럼 해석 ==<br /> {| class="wikitable" style="width:100%;"<br /> |- style="background:#6e2c00;color:#fff;"<br /> ! 컬럼 !! 의미 !! 주의<br /> |-<br /> | Id || 단계 번호. '''*''' 표시는 Predicate가 있는 단계 || Predicate 섹션과 매칭<br /> |-<br /> | Operation / Name || 오퍼레이션과 대상 객체 || 들여쓰기가 부모-자식 관계<br /> |-<br /> | Starts || 이 단계가 시작된 횟수 || NL Inner, FILTER 서브쿼리에서 크게 증가<br /> |-<br /> | E-Rows || 예상 행 수 ('''1회 실행 기준''') || A-Rows와 비교 시 '''E-Rows × Starts'''<br /> |-<br /> | A-Rows || 실제 반환 행 수 ('''전체 누적''') ||<br /> |-<br /> | A-Time || 실제 경과 시간 ('''자식 포함 누적''') || 자식 시간을 빼야 순수 단계 시간<br /> |-<br /> | Buffers || Logical Read ('''자식 포함 누적''') || 부모 − 자식 = 해당 단계 자체 I/O<br /> |-<br /> | Reads / Writes || Physical Read / Write || Writes는 Temp 사용 의미<br /> |-<br /> | OMem / 1Mem / Used-Mem || 최적 / One-Pass / 실사용 PGA || Used-Tmp가 있으면 디스크 정렬·해시<br /> |-<br /> | Pstart / Pstop || 접근한 파티션 범위 || KEY = 실행 시 결정, 숫자 = 파싱 시 결정<br /> |}<br /> <br /> == 4. 주요 오퍼레이션 사전 ==<br /> {| class="wikitable sortable" style="width:100%;font-size:13px;"<br /> |- style="background:#fbeee6;"<br /> ! 오퍼레이션 !! 의미 !! 체크 포인트<br /> |-<br /> | TABLE ACCESS FULL || 테이블 전체 Multi Block 스캔 || 대량 처리면 정상, 소량 결과면 인덱스 검토<br /> |-<br /> | TABLE ACCESS BY INDEX ROWID || ROWID로 테이블 단건 접근 || 인덱스 A-Rows 대비 버려지는 비율<br /> |-<br /> | TABLE ACCESS BY INDEX ROWID BATCHED || ROWID를 모아 블록 단위로 일괄 접근 (12c+) || 랜덤 I/O 감소, 정렬 순서 보장 안 됨<br /> |-<br /> | INDEX UNIQUE SCAN || 1건 탐색 ||<br /> |-<br /> | INDEX RANGE SCAN || 범위 탐색 || access 조건 범위가 좁은가<br /> |-<br /> | INDEX RANGE SCAN DESCENDING || 역순 범위 탐색 || ORDER BY DESC 정렬 대체<br /> |-<br /> | INDEX FULL SCAN (MIN/MAX) || 인덱스 한쪽 끝만 읽음 || MIN/MAX 최적화<br /> |-<br /> | INDEX FAST FULL SCAN || 인덱스 전체 Multi Block || 필요한 컬럼이 인덱스에 모두 있을 때<br /> |-<br /> | INDEX SKIP SCAN || 선두 컬럼 건너뛰기 || 대개 인덱스 설계 개선 신호<br /> |-<br /> | INLIST ITERATOR || IN 목록 값마다 인덱스 탐색 반복 ||<br /> |-<br /> | NESTED LOOPS / HASH JOIN / MERGE JOIN || 조인 방식 || [[조인 튜닝 심화]] 참고<br /> |-<br /> | HASH JOIN SEMI / ANTI || EXISTS / NOT EXISTS 변환 ||<br /> |-<br /> | FILTER || 조건 필터, 또는 '''서브쿼리를 메인 행마다 실행''' || 자식 2개면 서브쿼리 Filter 방식<br /> |-<br /> | VIEW || 인라인 뷰·뷰가 병합되지 않고 독립 처리 || 뷰 병합 실패 여부<br /> |-<br /> | SORT ORDER BY / GROUP BY || 정렬 / 정렬 기반 그룹핑 || Used-Tmp 여부<br /> |-<br /> | HASH GROUP BY / HASH UNIQUE || 해시 기반 그룹핑 / DISTINCT ||<br /> |-<br /> | SORT ORDER BY STOPKEY || Top-N 정렬 (N건만 유지) || 페이징<br /> |-<br /> | COUNT STOPKEY || <code>ROWNUM <= n</code> / <code>FETCH FIRST</code> 조기 종료 || 부분 범위 처리<br /> |-<br /> | WINDOW SORT / WINDOW BUFFER || 분석 함수 처리 ||<br /> |-<br /> | PARTITION RANGE SINGLE / ITERATOR / ALL || 파티션 1개 / 일부 / 전체 || ALL이면 프루닝 실패<br /> |-<br /> | PX COORDINATOR / PX SEND / PX RECEIVE || 병렬 처리 || 병렬 분배 방식(HASH, BROADCAST)<br /> |-<br /> | STATISTICS COLLECTOR || 적응적 계획의 전환 판단 지점 (12c+) || +ADAPTIVE로 확인<br /> |}<br /> <br /> == 5. 실습 문제 ==<br /> <div style="font-size:13px;color:#6b5a4a;margin-bottom:10px;">각 실습의 실행계획은 학습용으로 단순화한 예시이다. 본인 환경에서 직접 실행해 수치를 비교해 보자. 해설은 클릭하면 펼쳐진다.</div><br /> <br /> === 실습 1. 읽는 순서 ===<br /> <syntaxhighlight lang="text"><br /> -----------------------------------------------------------------------------<br /> | Id | Operation | Name | Starts | A-Rows | Buffers |<br /> -----------------------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | | 1 | 20 | 67 |<br /> | 1 | NESTED LOOPS | | 1 | 20 | 67 |<br /> | 2 | NESTED LOOPS | | 1 | 20 | 47 |<br /> | 3 | TABLE ACCESS BY INDEX ROWID| LAB_ORDER | 1 | 20 | 23 |<br /> |* 4 | INDEX RANGE SCAN | IX_LAB_ORDER_01 | 1 | 20 | 3 |<br /> |* 5 | INDEX UNIQUE SCAN | PK_LAB_CUST | 20 | 20 | 24 |<br /> | 6 | TABLE ACCESS BY INDEX ROWID | LAB_CUST | 20 | 20 | 20 |<br /> -----------------------------------------------------------------------------<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 실행 순서를 Id로 나열하고, 각 단계의 순수 Buffers를 구하라.<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * 순서: '''4 → 3 → 5 → 2 → 6 → 1 → 0''' (NL은 Outer(3) 1행마다 Inner(5→6) 수행)<br /> * 순수 Buffers: 4 = 3, 3 = 23 − 3 = '''20''', 5 = '''24''', 2 = 47 − 23 − 24 = 0, 6 = '''20''', 1 = 67 − 47 − 20 = 0<br /> * 20건에 67블록 — 건당 약 3.3블록으로 양호하다.<br /> </div><br /> </div><br /> <br /> === 실습 2. 카디널리티 오류 찾기 ===<br /> <syntaxhighlight lang="text"><br /> --------------------------------------------------------------------------------------<br /> | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |<br /> --------------------------------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | | 1 | | 29700 | 187K |<br /> | 1 | NESTED LOOPS | | 1 | 30 | 29700 | 187K |<br /> | 2 | TABLE ACCESS BY INDEX ROWID | LAB_ORDER | 1 | 30 | 29700 | 29812 |<br /> |* 3 | INDEX RANGE SCAN | IX_LAB_ORDER_02 | 1 | 30 | 29700 | 84 |<br /> | 4 | TABLE ACCESS BY INDEX ROWID | LAB_CUST | 29700 | 1 | 29700 | 157K |<br /> |* 5 | INDEX UNIQUE SCAN | PK_LAB_CUST | 29700 | 1 | 29700 | 59400 |<br /> --------------------------------------------------------------------------------------<br /> 3 - access("ORDER_DT"=:B1)<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 문제 단계와 원인, 개선 방향은?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * '''Id 3''': E-Rows 30 vs A-Rows 29,700 — '''약 1,000배''' 과소 추정<br /> * 옵티마이저는 30건으로 보고 NL을 선택했지만 실제로는 29,700번 Inner를 반복하여 Buffers 18만 블록 소모<br /> * 원인 후보: 통계 오래됨(Stale), 바인드 값 분포 편중(Bind Peeking 시점 값과 현재 값 차이), 히스토그램 부재<br /> * 개선: 통계 재수집 → 계획 재확인. 대량이 정상이라면 <code>LEAD</code> 순서 유지 + '''Hash Join''' (LAB_CUST Full Scan, 약 300블록 수준)<br /> </div><br /> </div><br /> <br /> === 실습 3. access vs filter ===<br /> <syntaxhighlight lang="sql"><br /> SELECT /*+ GATHER_PLAN_STATISTICS INDEX(o ix_lab_order_01) */ *<br /> FROM lab_order o<br /> WHERE o.order_dt BETWEEN DATE '2026-01-01' AND DATE '2026-01-31'<br /> AND o.cust_no = 'C000123';<br /> </syntaxhighlight><br /> <syntaxhighlight lang="text"><br /> |* 2 | INDEX RANGE SCAN | IX_LAB_ORDER_01 | 1 | 2 | 2 | 3 |<br /> Predicate Information:<br /> 2 - access("CUST_NO"='C000123' AND "ORDER_DT">=... AND "ORDER_DT"<=...)<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 인덱스를 <code>(ORDER_DT, CUST_NO)</code>로 바꾸면 Predicate가 어떻게 달라지는가?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * <code>access("ORDER_DT">=... AND "ORDER_DT"<=...)</code> + <code>filter("CUST_NO"='C000123')</code><br /> * 선두 컬럼이 '''범위 조건'''이면 그 뒤 컬럼은 스캔 범위를 줄이지 못하고 filter가 된다 → 한 달치 인덱스 리프(약 9만 건)를 모두 스캔<br /> * 원칙: '''= 조건 컬럼 선두, 범위 조건 컬럼 후행'''<br /> </div><br /> </div><br /> <br /> === 실습 4. 테이블 랜덤 액세스 낭비 ===<br /> <syntaxhighlight lang="text"><br /> | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | LAB_ORDER | 1 | 12 | 45210 |<br /> |* 2 | INDEX RANGE SCAN | IX_LAB_ORDER_02 | 1 | 45000 | 130 |<br /> Predicate:<br /> 1 - filter("STAT_CD"='90')<br /> 2 - access("ORDER_DT">=... AND "ORDER_DT"<...)<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 무엇이 비효율이고 어떻게 개선하는가?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * 인덱스에서 45,000건을 찾아 테이블을 45,000번 방문했지만 <code>STAT_CD</code> filter로 '''12건'''만 남았다 (99.97% 버림)<br /> * 테이블 단계 순수 Buffers ≈ 45,080<br /> * 개선: 인덱스 <code>(STAT_CD, ORDER_DT)</code> 또는 기존 인덱스에 STAT_CD 추가 → 인덱스 단계에서 걸러 테이블 접근 12회<br /> </div><br /> </div><br /> <br /> === 실습 5. FILTER 서브쿼리 ===<br /> <syntaxhighlight lang="text"><br /> | 0 | SELECT STATEMENT | | 1 | 5000 | 60230 |<br /> |* 1 | FILTER | | 1 | 5000 | 60230 |<br /> | 2 | TABLE ACCESS FULL | LAB_CUST | 1 | 50000 | 310 |<br /> |* 3 | INDEX RANGE SCAN | IX_LAB_ORDER_01 | 50000 | 5000 | 59920 |<br /> 1 - filter( EXISTS (SELECT 0 FROM "LAB_ORDER" ... ))<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 이 계획의 처리 방식은? 개선 방법은?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * FILTER의 자식이 2개 → 서브쿼리를 '''메인 행마다 실행'''하는 Filter 방식 (Starts 50,000)<br /> * 입력값(CUST_NO)이 모두 달라 서브쿼리 캐싱 효과가 없음<br /> * 개선: 서브쿼리 Unnesting → <code>HASH JOIN SEMI</code> (<code>/*+ UNNEST HASH_SJ */</code>) 또는 조건 추가로 메인 집합 축소<br /> </div><br /> </div><br /> <br /> === 실습 6. 정렬과 Temp 사용 ===<br /> <syntaxhighlight lang="text"><br /> | Id | Operation | Name | A-Rows | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp |<br /> | 1 | SORT ORDER BY | | 3000K | 15420 | 21540 | 21540 | 189M | 4.8M | 25M (1) | 172M |<br /> | 2 | TABLE ACCESS FULL| LAB_ORDER | 3000K | 15420 | 0 | 0 | | | | |<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' Used-Mem 옆의 <code>(1)</code>과 Used-Tmp는 무엇을 뜻하는가?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * 최적(OMem) 189MB가 필요했지만 PGA가 부족해 '''One-Pass''' 정렬 수행 — <code>(0)</code> Optimal, <code>(1)</code> One-Pass, <code>(n)</code> Multi-Pass<br /> * Temp 172MB 사용, Reads/Writes는 Temp I/O<br /> * 개선 순서: ① 정렬이 정말 필요한가 (인덱스로 정렬 대체, Top-N) ② 정렬 대상 컬럼 최소화 ③ PGA(<code>PGA_AGGREGATE_TARGET</code>) 검토<br /> </div><br /> </div><br /> <br /> === 실습 7. 페이징 — STOPKEY ===<br /> <syntaxhighlight lang="sql"><br /> SELECT /*+ GATHER_PLAN_STATISTICS */ *<br /> FROM lab_order<br /> WHERE cust_no = 'C000123'<br /> ORDER BY order_dt DESC<br /> FETCH FIRST 10 ROWS ONLY;<br /> </syntaxhighlight><br /> <syntaxhighlight lang="text"><br /> | 1 | VIEW | | 1 | 10 | 13 |<br /> |* 2 | WINDOW NOSORT STOPKEY | | 1 | 10 | 13 |<br /> | 3 | TABLE ACCESS BY INDEX ROWID | LAB_ORDER | 1 | 11 | 13 |<br /> | 4 | INDEX RANGE SCAN DESCENDING| IX_LAB_ORDER_01 | 1 | 11 | 3 |<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 정렬(SORT)이 없는 이유와, 부분 범위 처리가 깨지는 경우는?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * 인덱스 <code>(CUST_NO, ORDER_DT)</code>가 이미 정렬되어 있어 '''DESCENDING 스캔'''으로 정렬 대체 → <code>NOSORT STOPKEY</code>로 11건만 읽고 종료<br /> * 깨지는 경우: ORDER BY 컬럼이 인덱스 순서와 다름, 인덱스에 없는 컬럼으로 정렬, 조건 컬럼 가공, 중간에 GROUP BY·DISTINCT·Hash Join 존재<br /> </div><br /> </div><br /> <br /> === 실습 8. 파티션 프루닝 ===<br /> <syntaxhighlight lang="text"><br /> | Id | Operation | Name | Pstart | Pstop | A-Rows | Buffers |<br /> | 1 | PARTITION RANGE ALL | | 1 | 36 | 3000 | 158K |<br /> |* 2 | TABLE ACCESS FULL | TB_ORDER | 1 | 36 | 3000 | 158K |<br /> 2 - filter(TO_CHAR("ORDER_DT",'YYYYMM')='202611')<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 왜 36개 파티션을 모두 읽었는가?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * 파티션 키 <code>ORDER_DT</code>를 '''가공'''하여 프루닝 실패 → <code>PARTITION RANGE ALL</code><br /> * 개선: <code>ORDER_DT >= DATE '2026-11-01' AND ORDER_DT < DATE '2026-12-01'</code> → <code>PARTITION RANGE SINGLE</code>, Pstart=Pstop<br /> </div><br /> </div><br /> <br /> === 실습 9. 계획 변경 추적 ===<br /> <syntaxhighlight lang="sql"><br /> -- 같은 SQL의 과거 계획 이력 (Diagnostics Pack 필요)<br /> SELECT snap_id, plan_hash_value,<br /> executions_delta AS execs,<br /> ROUND(buffer_gets_delta / NULLIF(executions_delta,0)) AS gets_per_exec,<br /> ROUND(elapsed_time_delta / NULLIF(executions_delta,0) / 1000, 1) AS ms_per_exec<br /> FROM dba_hist_sqlstat<br /> WHERE sql_id = '&sql_id'<br /> ORDER BY snap_id;<br /> <br /> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id', &plan_hash_value, NULL, 'TYPICAL'));<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' "어제까지 빨랐는데 오늘 갑자기 느려졌다"를 어떻게 확인하는가?<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> * 위 쿼리로 '''PLAN_HASH_VALUE가 바뀐 시점'''과 gets_per_exec 변화를 찾는다<br /> * 계획 변경 원인 후보: 통계 재수집, 바인드 피킹 값 변화, 인덱스 추가·삭제, 파라미터·패치 변경<br /> * 긴급 조치: 좋은 계획을 '''SQL Plan Baseline'''으로 고정 (<code>DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE</code> 또는 AWR에서 로드)<br /> </div><br /> </div><br /> <br /> === 실습 10. 종합 ===<br /> <syntaxhighlight lang="text"><br /> ---------------------------------------------------------------------------------------------<br /> | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |<br /> ---------------------------------------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | | 1 | | 50 | 412K |<br /> | 1 | SORT ORDER BY | | 1 | 12 | 50 | 412K |<br /> | 2 | HASH GROUP BY | | 1 | 12 | 50 | 412K |<br /> | 3 | NESTED LOOPS | | 1 | 1200 | 102K | 412K |<br /> |* 4 | TABLE ACCESS FULL | LAB_CUST | 1 | 100 | 5000 | 310 |<br /> |* 5 | TABLE ACCESS BY INDEX ROWID | LAB_ORDER | 5000 | 12 | 102K | 411K |<br /> |* 6 | INDEX RANGE SCAN | IX_LAB_ORDER_01 | 5000 | 60 | 306K | 15200 |<br /> ---------------------------------------------------------------------------------------------<br /> 4 - filter("C"."GRADE_CD"='VIP')<br /> 5 - filter("O"."STAT_CD"='10')<br /> 6 - access("O"."CUST_NO"="C"."CUST_NO" AND "O"."ORDER_DT">=:B1)<br /> </syntaxhighlight><br /> <div class="mw-collapsible mw-collapsed" style="border:1px solid #f0d9b5;border-radius:10px;padding:10px 16px;margin-bottom:16px;"><br /> '''Q.''' 문제점을 모두 찾고 개선안을 제시하라.<br /> <div class="mw-collapsible-content" style="margin-top:8px;color:#5a4a3a;"><br /> # '''Id 4 카디널리티 오류''': VIP E-Rows 100 vs A-Rows 5,000 (50배) → GRADE_CD 히스토그램 확인<br /> # '''Id 5 랜덤 액세스 낭비''': 인덱스 306K건 → 테이블 방문 306K회 → filter 후 102K건. 테이블 단계 순수 Buffers 약 396K로 '''전체의 96%'''<br /> # '''조인 방식''': Outer 5,000건 × 반복 → 결과가 대량이므로 Hash Join이 유리할 수 있음<br /> * 개선안 A: 인덱스 <code>(CUST_NO, STAT_CD, ORDER_DT)</code> + 집계 컬럼 포함 시 테이블 접근 제거<br /> * 개선안 B: <code>LEADING(c) USE_HASH(o)</code> + LAB_ORDER Full Scan (약 15K 블록) → 412K → 약 16K<br /> * 두 안을 실측 비교하고, 실행 빈도(OLTP/배치)에 따라 결정<br /> </div><br /> </div><br /> <br /> == 6. 실행계획 분석 체크리스트 ==<br /> <div style="border:1px solid #f0d9b5;background:#fffaf0;border-radius:10px;padding:14px 22px;line-height:1.9;"><br /> □ '''실측 계획'''(ALLSTATS LAST)인가, 예상 계획인가<br/><br /> □ 전체 Buffers 중 '''가장 큰 비중'''을 차지하는 단계는 어디인가 (부모 − 자식)<br/><br /> □ '''E-Rows × Starts''' 와 '''A-Rows'''가 10배 이상 차이 나는 단계가 있는가<br/><br /> □ '''filter'''로 대량의 행이 버려지는 단계가 있는가 (특히 테이블 액세스 단계)<br/><br /> □ NL Inner 단계의 '''Starts'''가 과도하지 않은가<br/><br /> □ '''Used-Tmp''' (디스크 정렬·해시)가 발생하는가<br/><br /> □ 파티션 테이블에서 '''PARTITION RANGE ALL'''이 나오는가<br/><br /> □ Note 섹션에 dynamic sampling, SQL profile, baseline 사용이 표시되는가<br /> </div><br /> <br /> <div style="margin-top:30px;padding:14px 20px;border-top:3px solid #6e2c00;background:#fdf7f2;border-radius:0 0 10px 10px;font-size:13px;color:#5a4a3a;"><br /> '''이전''' ← [[SQL 튜닝 입문]] &nbsp;|&nbsp; '''다음''' → [[인덱스 튜닝 심화]] · [[조인 튜닝 심화]] · [[AWR 리포트 분석]]<br /> </div><br /> <br /> [[분류:SQL 튜닝]]<br /> [[분류:Oracle]]<br /> [[분류:실습]]</div>

Dbstudy