상담원이 주문 세 건을 골라 상세를 펼치면 주문 테이블 두 개를 통째로 읽는다

기초SELECT한빛유통607 / 700

상황

상담 화면 「주문 묶음 상세」는 상담원이 고른 주문(보통 1 ~ 5건)을 전역 임시 테이블 GT_ORD_PICK에 넣고 상세를 펼친다. 상담원 40명이 하루 약 2만 번 쓴다. 이 화면이 붙는 접속 풀은 접속할 때마다 업체가 2012년 튜닝 가이드에 적은 초기화 SQL을 실행한다. 가이드의 설명은 "동적 샘플링을 끄면 파싱 시간이 준다" 한 줄이다.

테이블과 인덱스

GT_ORD_PICK: 전역 임시 테이블(ON COMMIT PRESERVE ROWS) · ORD_SEQ · 세션마다 1 ~ 5행 · 통계 없음
T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3건
블록 크기 8KB · 화면 SQL은 바인드 변수가 없다(고른 주문은 임시 테이블로만 넘긴다).

원본 SQL

sql

-- 접속 풀 초기화 SQL (업체 · 2012년 튜닝 가이드)
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0;

-- 「주문 묶음 상세」 (고른 주문을 GT_ORD_PICK 에 넣은 뒤)
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM GT_ORD_PICK g, T_ORD o, T_ORD_DET d
 WHERE o.ORD_SEQ = g.ORD_SEQ
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_SEQ, d.ORD_DET_NO

실행계획 학습용 예시

xplan

[상담원이 주문 3건을 고른 경우]
---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |      1 |        |      6 |00:00:00.03 |    1793 |          |
|   1 |  SORT ORDER BY       |             |      1 |  16336 |      6 |00:00:00.03 |    1793 | 2048  (0)|
|*  2 |   HASH JOIN          |             |      1 |  16336 |      6 |00:00:00.03 |    1793 | 1164K (0)|
|*  3 |    HASH JOIN         |             |      1 |   8168 |      3 |00:00:00.02 |     897 | 1236K (0)|
|   4 |     TABLE ACCESS FULL| GT_ORD_PICK |      1 |   8168 |      3 |00:00:00.01 |       1 |          |
|   5 |     TABLE ACCESS FULL| T_ORD       |      1 |    100K|    100K|00:00:00.01 |     896 |          |
|   6 |    TABLE ACCESS FULL | T_ORD_DET   |      1 |    200K|    200K|00:00:00.01 |     896 |          |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   3 - access("O"."ORD_SEQ"="G"."ORD_SEQ")

문제

4번 줄의 E-Rows 8,168이 어디서 나온 숫자인지 쓰고, 그 숫자 때문에 고른 계획을 설명하시오.
화면 SQL은 그대로 두고 고치시오. 업체 가이드의 "파싱 시간이 준다"는 설명이 이 화면에 맞는지도 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 접속 풀 초기화 SQL : 동적 샘플링을 끄는 줄을 지운다 (기본값 2 로 돌아간다 · 명시한다면 아래처럼)
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 2;

-- 화면 SQL 은 그대로
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM GT_ORD_PICK g, T_ORD o, T_ORD_DET d
 WHERE o.ORD_SEQ = g.ORD_SEQ
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_SEQ, d.ORD_DET_NO;

개선 후 실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |      6 |00:00:00.01 |      19 |          |
|   1 |  SORT ORDER BY                 |              |      1 |      6 |      6 |00:00:00.01 |      19 | 2048  (0)|
|   2 |   NESTED LOOPS                 |              |      1 |      6 |      6 |00:00:00.01 |      19 |          |
|   3 |    NESTED LOOPS                |              |      1 |      3 |      3 |00:00:00.01 |      10 |          |
|   4 |     TABLE ACCESS FULL          | GT_ORD_PICK  |      1 |      3 |      3 |00:00:00.01 |       1 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| T_ORD        |      3 |      1 |      3 |00:00:00.01 |       9 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_T_ORD     |      3 |      1 |      3 |00:00:00.01 |       6 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID | T_ORD_DET    |      3 |      2 |      6 |00:00:00.01 |       9 |          |
|*  8 |     INDEX RANGE SCAN           | PK_T_ORD_DET |      3 |      2 |      6 |00:00:00.01 |       6 |          |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("O"."ORD_SEQ"="G"."ORD_SEQ")
   8 - access("D"."ORD_SEQ"="O"."ORD_SEQ")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

모범 답안 서술

GT_ORD_PICK에는 통계가 없고, 접속 풀이 동적 샘플링을 0으로 꺼 두었다. 통계도 없고 표본도 뜨지 않으면 옵티마이저는 기본값으로 크기를 정한다. 블록 100개, 행 하나 100바이트로 보아 블록당 약 82행(8KB 블록), 그래서 8,168행이다. 실제로는 3건이다. 8,168건을 PK로 하나씩 찾아가면 비싸다고 보아, 옵티마이저는 T_ORD와 T_ORD_DET를 통째로 읽고 해시 조인 두 번과 정렬을 골랐다. 주문 3건의 상세 6건을 보려고 1,793블록을 읽는다. 3건이라면 PK_T_ORD와 PK_T_ORD_DET로 찾아가는 NL이 맞다. 화면 SQL은 그대로 두고, 접속 풀 초기화 SQL에서 동적 샘플링을 끄는 줄을 지운다(기본값 2). 그러면 하드 파싱 때 임시 테이블의 표본을 떠서 E-Rows 3으로 보고 NL을 고른다. Buffers는 1,793 → 19이고, 하루 2만 번이면 약 3,600만 → 38만 블록이다. 업체 가이드의 "파싱 시간이 준다"는 이 화면에 맞지 않는다. 동적 샘플링은 하드 파싱 때 한 번만 하는데, 이 SQL은 바인드 변수 없이 텍스트가 늘 같아 하드 파싱이 거의 없다. 아끼는 것은 하루 몇 번의 표본이고, 잃는 것은 2만 번의 실행이다. 다만 하드 파싱 때 한 번 뜬 표본을 모든 세션이 같이 쓴다는 점은 알아 둔다. 이 화면은 고르는 건수가 늘 1 ~ 5건이라 첫 세션의 표본이 다른 세션에도 맞는다. 다른 답도 있다. 행을 넣은 뒤 그 세션에서 GATHER_TABLE_STATS로 세션 전용 통계를 모으는 방법은 호출마다 수집이 따르고, DYNAMIC_SAMPLING(g 2) 힌트는 이 SQL만 고치므로 같은 접속 풀의 다른 화면은 그대로 남는다.

해설

8,168은 외워 둘 만한 숫자다. 임시 테이블이나 통계가 없는 테이블의 E-Rows가 8,168(8KB 블록)로 찍혀 있으면 동적 샘플링이 꺼졌다는 신호다.
세션 설정은 SQL에 보이지 않는다. 같은 SQL을 SQL*Plus에서 돌리면 동적 샘플링이 기본값이라 NL이 나와 원인을 놓치기 쉽다. V$SES_OPTIMIZER_ENV나 계획의 Note(동적 샘플링 줄이 있는지)로 그 세션의 설정을 확인한다.
반대로 고르는 건수가 사용자마다 크게 다르다면, 동적 샘플링을 되살려도 첫 세션의 숫자를 모두가 쓴다(OPT-008). 그때는 건수로 SQL을 나눈다.
오래된 튜닝 가이드의 세션 설정은 그때의 버전과 화면을 위한 판단이다. 접속 풀의 초기화 SQL도 힌트처럼 유효기간 대장에 올린다(06화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황