본부장이 매장 150곳을 고르면 같은 화면이 날마다 0.2초였다 3초였다 한다

실전SELECT버거598 / 700

상황

버거 본사의 「매장 묶음 시간대 분석」 화면은 사용자가 매장을 고르면(1곳 ~ 200곳) 매장 번호를 전역 임시 테이블 GT_SHOP_PICK에 넣고 아래 SQL을 실행한다. 연도는 화면이 리터럴로 넣고, 바인드 변수는 없다. 사용자는 점장, 지역장, 본부장 약 30명이고 하루 약 400번 쓴다. 박팀장이 전했다. "본부장님이 150곳을 고르면 어떤 날은 바로 뜨고 어떤 날은 3초가 넘는답니다." 조민우가 공유 풀을 보니 느린 날의 커서는 그날 08:40에 한 점장이 매장 한 곳을 골라 하드 파싱한 것이었다.

테이블과 인덱스

GT_SHOP_PICK: 전역 임시 테이블(ON COMMIT PRESERVE ROWS) · SHOP_NO VARCHAR2(6) · 세션마다 1 ~ 200행 · 통계 없음(아무도 모으지 않았다) · GLOBAL_TEMP_TABLE_STATS는 기본값 SESSION
ORD: 1,000,000행 · 22,422블록 · 2013년 500,218행 · 매장당 한 해 약 2,500행 · 날짜순 적재
인덱스: ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · ORD_X02 (SHOP_NO, ORD_DT)(클러스터링 팩터 약 99만, 한 매장의 주문은 테이블 곳곳에 흩어져 있다)
optimizer_dynamic_sampling 2(기본값)

원본 SQL

sql

-- 화면 : INSERT INTO GT_SHOP_PICK (SHOP_NO) VALUES (...) 를 고른 매장 수만큼 · 그다음
SELECT o.SHOP_NO, SUBSTR(o.ORD_HMS, 1, 2) AS hh, COUNT(*) AS ord_cnt
  FROM GT_SHOP_PICK g, ORD o
 WHERE o.SHOP_NO = g.SHOP_NO
   AND o.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY o.SHOP_NO, SUBSTR(o.ORD_HMS, 1, 2)

실행계획 학습용 예시

xplan

[느린 날 10:05 · 본부장 150곳 · 커서는 08:40 점장(1곳)이 하드 파싱한 것]
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |   2400 |00:00:03.12 |     378K|
|   1 |  HASH GROUP BY                |              |      1 |   2501 |   2400 |00:00:03.12 |     378K|
|   2 |   NESTED LOOPS                |              |      1 |   2501 |    375K|00:00:02.96 |     378K|
|   3 |    NESTED LOOPS               |              |      1 |   2501 |    375K|00:00:00.61 |    1653 |
|   4 |     TABLE ACCESS FULL         | GT_SHOP_PICK |      1 |      1 |    150 |00:00:00.01 |       3 |
|*  5 |     INDEX RANGE SCAN          | ORD_X02      |    150 |   2501 |    375K|00:00:00.42 |    1650 |
|   6 |    TABLE ACCESS BY INDEX ROWID| ORD          |    375K|      1 |    375K|00:00:02.18 |     376K|
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("O"."SHOP_NO"="G"."SHOP_NO" AND "O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231')

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

[빠른 날 · 그날 첫 사용자가 지역장(120곳)이었다]
---------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |   2400 |00:00:00.21 |   22425 |
|   1 |  HASH GROUP BY      |              |      1 |   2856 |   2400 |00:00:00.21 |   22425 |
|*  2 |   HASH JOIN         |              |      1 |    300K|    375K|00:00:00.17 |   22425 |
|   3 |    TABLE ACCESS FULL| GT_SHOP_PICK |      1 |    120 |    150 |00:00:00.01 |       3 |
|*  4 |    TABLE ACCESS FULL| ORD          |      1 |    500K|    500K|00:00:00.09 |   22422 |
---------------------------------------------------------------------------------------

[같은 두 계획을 점장(1곳)이 쓰면]  NL 계획 Buffers 2,515 · 0.02초 / 해시 계획 Buffers 22,425 · 0.15초

문제

같은 SQL이 날마다 다른 계획으로 도는 이유를 E-Rows와 Note, 커서 공유로 설명하시오. 바인드 변수가 없는데도 그날 첫 사용자가 계획을 정하는 까닭과, 적응적 커서 공유(ACS)가 도울 수 없는 까닭도 쓰시오.
네 가지 안(동적 샘플링 힌트, 세션 전용 통계, 고른 매장 수로 SQL 나누기, 계획 하나로 고정)을 읽는 양과 부작용으로 비교하시오.
고른 안을 SQL로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 화면은 고른 매장 수(n)를 안다 → 9곳 이하 / 10곳 이상으로 SQL 을 둘로 나눈다 (근거 : 22,422 ÷ 한 곳 약 2,500)
-- (A) 9곳 이하 : 매장마다 ORD_X02 로 그 매장의 주문만 찾아간다
SELECT /*+ LEADING(g o) USE_NL(o) INDEX(o ORD_X02) */
       o.SHOP_NO, SUBSTR(o.ORD_HMS, 1, 2) AS hh, COUNT(*) AS ord_cnt
  FROM GT_SHOP_PICK g, ORD o
 WHERE o.SHOP_NO = g.SHOP_NO
   AND o.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY o.SHOP_NO, SUBSTR(o.ORD_HMS, 1, 2);

-- (B) 10곳 이상 : 주문을 한 번 읽고 해시로 거른다
SELECT /*+ LEADING(g o) USE_HASH(o) FULL(o) */
       o.SHOP_NO, SUBSTR(o.ORD_HMS, 1, 2) AS hh, COUNT(*) AS ord_cnt
  FROM GT_SHOP_PICK g, ORD o
 WHERE o.SHOP_NO = g.SHOP_NO
   AND o.ORD_DT BETWEEN '20130101' AND '20131231'
 GROUP BY o.SHOP_NO, SUBSTR(o.ORD_HMS, 1, 2);
-- 문턱 9곳은 2013년 규모(매장당 한 해 약 2,500건)의 판단 → 힌트 유효기간 대장에 규모와 재검토 조건을 적는다

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

xplan

[(A) 점장 1곳]  NESTED LOOPS · ORD_X02 Starts 1 · Buffers 2,515 · 0.02초
[(B) 본부장 150곳]
---------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |   2400 |00:00:00.21 |   22425 |
|   1 |  HASH GROUP BY      |              |      1 |   2501 |   2400 |00:00:00.21 |   22425 |
|*  2 |   HASH JOIN         |              |      1 |   2501 |    375K|00:00:00.17 |   22425 |
|   3 |    TABLE ACCESS FULL| GT_SHOP_PICK |      1 |      1 |    150 |00:00:00.01 |       3 |
|*  4 |    TABLE ACCESS FULL| ORD          |      1 |    500K|    500K|00:00:00.09 |   22422 |
---------------------------------------------------------------------------------------
(E-Rows 는 여전히 첫 사용자의 표본이지만, 조인 방법은 SQL 이 정했다)

모범 답안 서술

두 계획의 4번 줄(임시 테이블) E-Rows가 1과 120으로 다르고, Note에는 dynamic sampling (level=2)가 찍혀 있다. GT_SHOP_PICK에는 통계가 없으므로 옵티마이저는 하드 파싱 때 표본을 떠서(동적 샘플링) 행 수를 센다. 그런데 표본을 뜨는 것은 하드 파싱 때 한 번뿐이고, 그 커서는 공유 풀에서 모든 세션이 그대로 다시 쓴다. 임시 테이블의 내용은 세션마다 다르지만 SQL 텍스트는 같기 때문이다. 그래서 그날 첫 사용자가 고른 매장 수가 그날 계획을 정한다. 08:40 점장이 1곳을 고른 날에는 NL이 굳어, 본부장의 150곳도 ORD_X02로 150번 찾아가 흩어진 주문 375K건을 한 블록씩 읽었다(Buffers 378K, 3.1초). 바인드 변수가 없으니 피킹도 ACS도 도울 수 없다. ACS는 바인드 값의 선택도가 달라질 때 커서를 나누는 장치인데, 임시 테이블의 행 수는 커서가 보지 않는 입력이다. 네 가지 안을 견주면 이렇다. ① DYNAMIC_SAMPLING 힌트는 하드 파싱 때만 표본을 뜨므로 공유된 커서에는 소용이 없다. ② 행을 넣은 뒤 그 세션에서 GATHER_TABLE_STATS로 세션 전용 통계를 모으면, 세션 전용 통계로 만든 커서는 다른 세션과 나눠 쓰지 않는다. 대신 호출마다 통계 수집이 따르고 세션마다 하드 파싱을 해서 자식 커서가 늘며, 한 세션 안에서 고른 수가 바뀔 때도 다시 모아야 한다. ③ 고른 매장 수로 SQL을 나누는 안은 화면이 이미 아는 숫자를 쓰므로 결정적이다. 대가는 SQL 두 벌과 문턱 관리다. ④ 해시 하나로 고정하면 가장 단순하지만 점장의 1곳 조회도 매번 22,425블록(NL이면 2,515)을 읽는다. 하루 400번이면 약 900만 블록이다. 매장 번호를 IN 목록 리터럴로 넘기는 안은 조합마다 다른 SQL이 되어 하드 파싱이 쌓인다. ③을 고른다. 두 계획이 갈리는 지점은 22,422 ÷ 매장 한 곳 약 2,500 ≒ 9곳이다. 9곳 이하는 ORD_X02로 찾아가는 NL(1곳 2,515블록), 10곳 이상은 주문을 한 번 읽는 해시 조인(22,425블록)으로 SQL을 나누고 조인 방법을 힌트로 정한다. 본부장의 150곳은 Buffers 378K → 22,425, 점장의 1곳은 늘 2,515다. 문턱 9곳은 2013년 규모의 판단이므로 힌트 유효기간 대장에 규모와 재검토 조건을 적는다.

해설

힌트 없이 SQL 텍스트만 나눠도 각 커서의 첫 사용자가 그 범위 안에 있으므로 대개 맞는 계획이 선다. 다만 문턱 근처(9곳과 10곳)의 첫 사용자가 계획을 정하므로 조인 방법을 박는 편이 확실하다.
문제는 통계가 틀린 것이 아니다. 첫 사용자에게는 맞는 숫자였다. 한 번 센 숫자를 모든 세션이 나눠 쓰는 것이 문제다. 임시 테이블을 쓰는 화면은 내용의 크기가 사용자마다 크게 다른지 먼저 본다.
세션 전용 통계는 배치처럼 한 세션이 큰 임시 테이블을 오래 쓰는 곳에 더 잘 맞는다(OPT-009). 화면처럼 짧은 호출이 많은 곳에서는 수집과 파싱 비용이 앞선다.
매장이 늘거나 연도 범위가 바뀌면 문턱도 바뀐다. 문턱은 Buffers로 정한 것이고, 옵티마이저의 비용 문턱과는 다를 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황