분석용 사본을 읽는 본사 화면 넷 중 매장 화면만 날마다 다른 쪽이 느려진다

실전SELECT버거570 / 700

상황

본사 분석용 사본 ORD_RANDOM을 읽는 화면이 넷 있다. 모두 바인드 변수로 쓰여 있다. 그중 점장이 쓰는 「매장 기간 주문 목록」(S1)은 기간이 짧은 날 느리다가, 다른 날은 긴 기간이 끝없이 돈다. 「매장 최근 주문」(S3)은 늘 0.3초쯤 걸린다. 박팀장이 말했다. "S1에 힌트를 박을지, 문장을 나눌지, 인덱스를 만들지 네 화면을 같이 보고 정해 주세요. 사본은 매일 밤 다시 적재하는데, 적재가 06:00 전에 끝나야 합니다."

테이블과 인덱스

ORD_RANDOM: 1,000,000행 · 22,414블록 · POS 원장 ORD를 무작위 순서로 복제한 사본 · ORD_DT VARCHAR2(8) 하루 약 1,370건 · SHOP_NO 'SH0001' ~ 'SH0200' 매장마다 고르게(하루 약 7건) · ORD_NO 유일
인덱스 ORD_RANDOM_X01 (ORD_DT, ORD_HMS): CF 999,958 · BLEVEL 2 · 리프 3,774블록 · 다른 인덱스는 없다
매일 01:00부터 사본 여러 개를 다시 적재하며 06:00 전에 끝나야 한다. ORD_RANDOM 몫은 TRUNCATE, 직접 경로 적재, X01 유지, 통계 수집을 합쳐 지금 약 2분이다.
바인드 피킹 켬 · 히스토그램 없음 · 화면 호출 수(하루): S1 3,000 · S2 2,000 · S3 4,000 · S4 30

원본 SQL

sql

-- S1 매장 기간 주문 목록 (점장 · 하루 ~ 3개월)
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD_RANDOM
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :f AND :t
 ORDER BY ORD_DT, ORD_HMS;

-- S2 시간대별 주문 수 (본사 · 하루)
SELECT SUBSTR(ORD_HMS, 1, 2) AS HH, COUNT(*) AS ORD_CNT
  FROM ORD_RANDOM
 WHERE ORD_DT = :d
 GROUP BY SUBSTR(ORD_HMS, 1, 2);

-- S3 매장 최근 주문 20건 (점장)
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM (SELECT ORD_NO, ORD_DT, ORD_HMS FROM ORD_RANDOM
         WHERE SHOP_NO = :shop
         ORDER BY ORD_DT DESC, ORD_HMS DESC)
 WHERE ROWNUM <= 20;

-- S4 월 정산 리포트 (한 달 · 자기 문장 · FULL 힌트)
SELECT /*+ FULL(o) */ o.SHOP_NO, COUNT(*) AS ORD_CNT
  FROM ORD_RANDOM o
 WHERE o.ORD_DT BETWEEN :f AND :t
 GROUP BY o.SHOP_NO;

실행계획 학습용 예시

xplan

[S1 — 커서가 하루로 태어난 날 · 3개월(124,670행 중 그 매장 623행) 한 번]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    623 |00:00:01.12 |    125K |
|   1 |  SORT ORDER BY                        |                |      1 |      7 |    623 |00:00:01.12 |    125K |
|*  2 |   FILTER                              |                |      1 |        |    623 |00:00:01.11 |    125K |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |      7 |    623 |00:00:01.11 |    125K |
|*  4 |     INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |   1370 |   124K |00:00:00.03 |     482 |
-------------------------------------------------------------------------------------------------------------
   2 - filter(:F<=:T)
   3 - filter("SHOP_NO"=:SHOP)
   4 - access("ORD_DT">=:F AND "ORD_DT"<=:T)

[S1 — 커서가 3개월로 태어난 날 · 하루(그 매장 7행) 한 번]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |      7 |00:00:00.29 |   22414 |
|   1 |  SORT ORDER BY                        |                |      1 |    623 |      7 |00:00:00.29 |   22414 |
|*  2 |   FILTER                              |                |      1 |        |      7 |00:00:00.29 |   22414 |
|*  3 |    TABLE ACCESS FULL                  | ORD_RANDOM     |      1 |    623 |      7 |00:00:00.29 |   22414 |
-------------------------------------------------------------------------------------------------------------
   3 - filter(("SHOP_NO"=:SHOP AND "ORD_DT">=:F AND "ORD_DT"<=:T))

[S2 — 어느 날이든]   INDEX RANGE SCAN ORD_RANDOM_X01 (테이블에 가지 않는다) · 하루 1,370행 · Buffers 7
[S3 — 어느 매장이든] TABLE ACCESS FULL + SORT ORDER BY STOPKEY · Buffers 22,414 · 0.3초
[S4 — 자기 문장]     TABLE ACCESS FULL · Buffers 22,414 · 한 달에 맞는 계획
[S1 — 하루로 태어난 날의 하루 한 번]  X01 + 테이블 · Buffers 1,377 (그 매장 7행을 위해 1,370행을 테이블까지 가서 거른다)

문제

네 문장 각각에 대해 바인드 값이 바뀌면 맞는 계획도 바뀌는지 판단하고, S1이 날마다 다른 쪽에서 느린 이유를 설명하시오.
네 화면을 모두 보고 인덱스를 설계하고 필요한 SQL을 고치시오. 힌트를 박는 안과 문장을 나누는 안을 S1에 쓰면 어떻게 되는지도 비교하시오.
설계안의 부작용(사본 적재 시간, 인덱스 크기, 다른 문장의 계획)을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 사본 적재 순서 : TRUNCATE → INSERT /*+ APPEND */ → 인덱스 (적재 뒤에 만들고 통계는 인덱스를 만들 때 같이 모인다)
CREATE INDEX ORD_RANDOM_X02 ON ORD_RANDOM (SHOP_NO, ORD_DT, ORD_HMS) NOLOGGING;

-- S1 : 그대로 (바인드) · 이제 기간이 무엇이든 그 매장 행만 읽는다
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD_RANDOM
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :f AND :t
 ORDER BY ORD_DT, ORD_HMS;

-- S3 : 그대로 · X02 를 거꾸로 읽다 20 건에서 멈춘다
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM (SELECT ORD_NO, ORD_DT, ORD_HMS FROM ORD_RANDOM
         WHERE SHOP_NO = :shop
         ORDER BY ORD_DT DESC, ORD_HMS DESC)
 WHERE ROWNUM <= 20;

-- S2 · S4 : 손대지 않는다 (S2 는 언제나 하루 · S4 는 이미 자기 문장)

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

xplan

[S1 — 3개월 · 어느 값으로 태어나든 같은 계획]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    623 |00:00:00.01 |     629 |
|*  1 |  FILTER                               |                |      1 |        |    623 |00:00:00.01 |     629 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD_RANDOM     |      1 |      7 |    623 |00:00:00.01 |     629 |
|*  3 |    INDEX RANGE SCAN                   | ORD_RANDOM_X02 |      1 |      7 |    623 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------------
   1 - filter(:F<=:T)
   3 - access("SHOP_NO"=:SHOP AND "ORD_DT">=:F AND "ORD_DT"<=:T)

[S1 — 하루]  같은 계획 · Buffers 10 · 정렬 없음 (인덱스 순서가 ORD_DT, ORD_HMS)
[S3]         INDEX RANGE SCAN DESCENDING ORD_RANDOM_X02 + COUNT STOPKEY · Buffers 23 (22,414 대신)
[적재]       X02 생성 약 40초 · ORD_RANDOM 적재 약 2분 → 2분 40초 · X02 리프 약 4,200블록

모범 답안 서술

네 문장의 판단: S2는 언제나 하루(ORD_DT = :d)이고 X01만 읽고 끝나서(7블록), 어느 날을 엿보든 맞는 계획이 같다. S4는 이미 월 정산 전용 문장이고 한 달에 맞는 FULL을 힌트로 정해 두었다. 이 둘은 그대로 둔다. S3은 값이 바뀐다고 계획이 바뀌지는 않지만, 매장을 앞에 둔 인덱스가 없어서 늘 FULL과 정렬로 22,414블록을 읽는다. 흔들리는 것은 S1이다. S1이 흔들리는 이유: 지금 S1에 쓸 수 있는 인덱스는 날짜가 앞인 X01뿐이라, 매장 조건은 테이블까지 가서 3번 줄에서 거른다. 커서가 하루를 엿보고 태어나면 X01 계획이 선다. 그날 3개월을 넘기면 124,670행을 테이블까지 하나씩 찾아가 623행만 남기므로 125K블록, 1.1초다. 커서가 3개월을 엿보고 태어나면 FULL 계획이 서고, 그날은 하루치 7행을 보려고 22,414블록을 읽는다. 어느 날 누가 먼저 오느냐에 따라 느린 쪽이 바뀐다. 두 계획 모두 매장 하나의 행(하루 7, 3개월 623)에 비해 너무 많이 읽는다는 점이 같다. 설계: (SHOP_NO, ORD_DT, ORD_HMS) 인덱스 하나를 만든다. S1은 매장과 기간이 모두 access 조건이 되어 그 매장 행만 읽는다. 하루 10블록, 3개월 629블록이고, 인덱스 순서가 ORDER BY와 같아 정렬도 없다. 맞는 계획이 기간과 상관없이 하나이니 어느 값을 엿보든 해가 없다. 같은 인덱스로 S3은 거꾸로 읽다 20건에서 멈춘다(22,414 → 23). S2와 S4는 손대지 않는다. 다른 안과 비교: S1에 X01 힌트를 박으면 3개월이 늘 125K블록이 된다. FULL 힌트를 박으면 하루가 늘 22,414블록이다. 문장을 기간별로 나누어도(하루용, 기간용) 기간용은 FULL로 22,414블록이라, 623행을 보려고 사본 전체를 읽는 것은 같다. BIND_AWARE도 두 나쁜 계획 중에서 고르는 일일 뿐이다. 값마다 계획이 달라야 하는 이유(매장 조건을 거를 길이 없음) 자체를 인덱스로 없애는 것이 맞다. 부작용: 사본은 매일 다시 적재하므로 인덱스 하나만큼 적재가 길어진다. 적재 뒤 NOLOGGING으로 만들면 약 40초가 늘어 ORD_RANDOM 몫이 2분 40초쯤 되고, 06:00 마감에는 여유가 있다. 인덱스는 리프 약 4,200블록이다. SHOP_NO가 앞인 인덱스가 생기면 매장 조건이 있는 다른 문장의 계획도 바뀔 수 있으므로, 사본을 읽는 문장 목록에서 SHOP_NO 조건이 있는 것을 찾아 전후 계획을 비교한다.

해설

바인드 피킹이 해가 되는 것은 값마다 맞는 계획이 다를 때다. 그 조건을 없애는 길은 셋이다. 문장을 나누거나(PARSE-020), 문장 모양을 바꾸거나(PARSE-022), 이 문제처럼 인덱스로 맞는 계획을 하나로 만든다.
인덱스 컬럼 순서를 (ORD_DT, SHOP_NO)로 하면 S1은 기간만큼 인덱스를 훑으며 매장을 거른다(3개월 리프 약 480블록). 테이블 액세스는 줄지만 기간이 길수록 읽는 양이 늘어 값에 덜 무관하다. 등치 조건(매장)을 앞에 둔다(ACC 단원).
원장 ORD(날짜순 적재, CF 22,223)라면 X01 계획도 3개월에 약 3,300블록이라 덜 흔들렸을 것이다. 같은 문장이라도 사본의 적재 순서가 피킹의 위험을 키웠다(교재 04화).
사본을 ORDER BY SHOP_NO, ORD_DT로 정렬해 적재하는 방법도 있다. 그러면 S1의 테이블 블록이 모여 더 적게 읽지만, 날짜가 앞인 다른 문장(S2 테이블 액세스 등)의 CF가 나빠진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황