일 조회를 살리려고 붙인 힌트 때문에 행사 매출 화면의 월 조회가 0.4초를 넘긴다

실전SELECT유통265 / 700

상황

본사 영업팀의 「행사 매출」 화면은 행사 상품 두 개 중 하나라도 든 영수증의 매장별 매출을 기간으로 보여 준다. 매장 담당자는 하루 단위로(하루 1,500번), 영업팀장은 한 달 단위로(하루 30번) 본다. 지난달 이 SQL이 월 조회로 먼저 파싱된 날, 일 조회까지 해시 세미 조인 계획을 쓰면서 느려졌다. 개발자가 서브쿼리에 NO_UNNEST를 붙이자 일 조회는 나아졌는데, 이번에는 월 조회가 0.4초를 넘긴다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) (하루치가 약 14블록에 모여 있다) · 하루 영수증 706장, 한 달 21,180장
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) (높이 3, 영수증 한 장의 줄은 한 블록에 모여 있다) · 영수증당 평균 2.2줄
행사 상품 두 개(:v_i1, :v_i2)의 판매 줄은 전 기간 25,222줄 · 행사 상품이 든 영수증은 전체의 약 5%(하루 35장, 한 달 1,060장)
아래 계획은 한 달(2023-06-01 ~ 06-30) 조회 · 통계 최신

원본 SQL

sql

SELECT a.SHP_NO, SUM(b.SLE_QTY * b.SLE_UPRC) AS amt, COUNT(*) AS line_cnt
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to
   AND b.SLE_NO = a.SLE_NO
   AND EXISTS (SELECT /*+ NO_UNNEST */ 1
                 FROM CF11 x
                WHERE x.SLE_NO = a.SLE_NO
                  AND x.ITM_NO IN (:v_i1, :v_i2))
 GROUP BY a.SHP_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |      5 |00:00:00.43 |  148764 |
|   1 |  HASH GROUP BY                           |                |      1 |      5 |      5 |00:00:00.43 |  148764 |
|*  2 |   FILTER                                 |                |      1 |        |   2322 |00:00:00.43 |  148764 |
|   3 |    NESTED LOOPS                          |                |      1 |  46400 |  46384 |00:00:00.25 |   85224 |
|   4 |     NESTED LOOPS                         |                |      1 |  46400 |  46384 |00:00:00.17 |   64044 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |  21180 |  21180 |00:00:00.01 |     504 |
|*  6 |       INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |  21180 |  21180 |00:00:00.01 |      84 |
|*  7 |      INDEX RANGE SCAN                    | CF11_PK        |  21180 |      2 |  46384 |00:00:00.12 |   63540 |
|   8 |     TABLE ACCESS BY INDEX ROWID          | CF11           |  46384 |      2 |  46384 |00:00:00.06 |   21180 |
|*  9 |    INDEX RANGE SCAN                      | CF11_PK        |  21180 |      1 |   1060 |00:00:00.13 |   63540 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "CF11" "X" WHERE "X"."SLE_NO"=:B1 AND
              ("X"."ITM_NO"=:V_I1 OR "X"."ITM_NO"=:V_I2)))
   6 - access("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO)
   7 - access("B"."SLE_NO"="A"."SLE_NO")
   9 - access("X"."SLE_NO"=:B1)
       filter(("X"."ITM_NO"=:V_I1 OR "X"."ITM_NO"=:V_I2))

문제

실행계획에서 서브쿼리가 언제, 몇 번 실행되는지 짚고, 148,764블록이 어디서 나오는지 설명하시오.
일 조회와 월 조회 각각에 대해 네 가지 모양(조인 뒤 FILTER, PUSH_SUBQ로 먼저 거르기, 펼쳐서 NL 세미 조인, 펼쳐서 해시 세미 조인)의 일량을 어림해 비교하시오.
두 조회가 모두 빠르도록 고치고, 그 방법의 부작용을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 일 조회 (기간 7일 이하): 영수증을 먼저 거르고(서브쿼리를 조인 전에) 행사 영수증의 줄만 조인
SELECT a.SHP_NO, SUM(b.SLE_QTY * b.SLE_UPRC) AS amt, COUNT(*) AS line_cnt
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to
   AND b.SLE_NO = a.SLE_NO
   AND EXISTS (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1
                 FROM CF11 x
                WHERE x.SLE_NO = a.SLE_NO
                  AND x.ITM_NO IN (:v_i1, :v_i2))
 GROUP BY a.SHP_NO;

-- 월 조회 (기간 7일 초과): 행사 상품 줄을 한 번 읽어 해시 세미 조인
SELECT a.SHP_NO, SUM(b.SLE_QTY * b.SLE_UPRC) AS amt, COUNT(*) AS line_cnt
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to
   AND b.SLE_NO = a.SLE_NO
   AND EXISTS (SELECT /*+ UNNEST HASH_SJ */ 1
                 FROM CF11 x
                WHERE x.SLE_NO = a.SLE_NO
                  AND x.ITM_NO IN (:v_i1, :v_i2))
 GROUP BY a.SHP_NO;
-- 화면은 기간 길이를 보고 두 SQL 중 하나를 보낸다

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

xplan

[일 조회 · 2023-06-01 하루 · PUSH_SUBQ]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |      5 |00:00:00.01 |    2277 |
|   1 |  HASH GROUP BY                           |                |      1 |      5 |      5 |00:00:00.01 |    2277 |
|   2 |   NESTED LOOPS                           |                |      1 |     78 |     77 |00:00:00.01 |    2277 |
|   3 |    NESTED LOOPS                          |                |      1 |     78 |     77 |00:00:00.01 |    2242 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED  | CF10           |      1 |     35 |     35 |00:00:00.01 |    2137 |
|*  5 |      INDEX RANGE SCAN                    | IX_CF10_SLE_DT |      1 |    706 |    706 |00:00:00.01 |       5 |
|*  6 |      INDEX RANGE SCAN                    | CF11_PK        |    706 |      1 |     35 |00:00:00.01 |    2118 |
|*  7 |     INDEX RANGE SCAN                     | CF11_PK        |     35 |      2 |     77 |00:00:00.01 |     105 |
|   8 |    TABLE ACCESS BY INDEX ROWID           | CF11           |     77 |      2 |     77 |00:00:00.01 |      35 |
-------------------------------------------------------------------------------------------------------------------
   4 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "CF11" "X" WHERE "X"."SLE_NO"=:B1 AND
              ("X"."ITM_NO"=:V_I1 OR "X"."ITM_NO"=:V_I2)))
   5 - access("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO)
   6 - access("X"."SLE_NO"=:B1)
       filter(("X"."ITM_NO"=:V_I1 OR "X"."ITM_NO"=:V_I2))
   7 - access("B"."SLE_NO"="A"."SLE_NO")

[월 조회 · 2023-06 한 달 · UNNEST HASH_SJ]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |      5 |00:00:00.06 |   12948 |
|   1 |  HASH GROUP BY                           |                |      1 |      5 |      5 |00:00:00.06 |   12948 |
|   2 |   NESTED LOOPS                           |                |      1 |   2400 |   2322 |00:00:00.06 |   12948 |
|   3 |    NESTED LOOPS                          |                |      1 |   2400 |   2322 |00:00:00.05 |   11888 |
|*  4 |     HASH JOIN RIGHT SEMI                 |                |      1 |   1100 |   1060 |00:00:00.05 |    8708 |
|*  5 |      TABLE ACCESS FULL                   | CF11           |      1 |  25200 |  25222 |00:00:00.04 |    8204 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |  21180 |  21180 |00:00:00.01 |     504 |
|*  7 |       INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |  21180 |  21180 |00:00:00.01 |      84 |
|*  8 |     INDEX RANGE SCAN                     | CF11_PK        |   1060 |      2 |   2322 |00:00:00.01 |    3180 |
|   9 |    TABLE ACCESS BY INDEX ROWID           | CF11           |   2322 |      2 |   2322 |00:00:00.01 |    1060 |
-------------------------------------------------------------------------------------------------------------------
   4 - access("X"."SLE_NO"="A"."SLE_NO")
   5 - filter(("X"."ITM_NO"=:V_I1 OR "X"."ITM_NO"=:V_I2))
   7 - access("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO)
   8 - access("B"."SLE_NO"="A"."SLE_NO")

모범 답안 서술

3번 줄 NESTED LOOPS(조인) 위에 2번 줄 FILTER가 있고, 서브쿼리(9번 줄)의 Starts가 21,180이다. NO_UNNEST 때문에 서브쿼리가 펼쳐지지 않았고, 펼쳐지지 않은 서브쿼리는 맨 마지막 단계에서 실행된다. 그래서 한 달 영수증 21,180장이 모두 판매상품과 조인된 뒤에야 영수증마다 한 번씩(영수증번호가 같은 줄은 바로 앞 결과를 다시 쓴다) 행사 상품이 있는지 물었다. 조인에 85,224블록(CF11_PK 63,540 + 테이블 21,180 + 판매 504), 서브쿼리에 63,540블록이 들어 148,764블록이다. 행사 영수증은 1,060장(5%)뿐이라 조인한 줄의 95%를 버렸다. 네 모양의 일량은 다음과 같다(블록).

모양	일 조회 (706장)	월 조회 (21,180장)
조인 뒤 FILTER (지금)	4,961	148,764
`PUSH_SUBQ`로 먼저 거르기	2,277	68,284
펼쳐서 NL 세미 조인	약 2,280	약 68,300
펼쳐서 해시 세미 조인	8,363	12,948

먼저 거르기와 NL 세미 조인은 영수증마다 CF11_PK를 한 번(3블록) 찾는다는 점에서 같은 일을 한다. 일량이 영수증 수에 비례하므로 바깥이 작은 일 조회에서 이긴다. 해시 세미 조인은 행사 상품 줄 25,222줄을 찾으려고 CF11을 한 번(8,204블록) 통째로 읽고, 그 값이 기간과 상관없이 일정하므로 바깥이 큰 월 조회에서 이긴다. 두 방식이 만나는 경계는 영수증 약 2,700장(약 4일)이다. 그래서 화면에서 SQL을 둘로 나눈다. 기간이 7일 이하면 NO_UNNEST PUSH_SUBQ로 먼저 거르는 SQL(일 2,277블록)을, 넘으면 UNNEST HASH_SJ로 해시 세미 조인을 하는 SQL(월 12,948블록)을 보낸다. 하루 합계는 1,500번 × 2,277 + 30번 × 12,948 ≈ 380만 블록으로, 지금(1,500 × 4,961 + 30 × 148,764 ≈ 1,190만)의 3분의 1이다. 부작용도 있다. 같은 뜻의 SQL이 두 벌이 되어 고칠 때 함께 고쳐야 하고, 하나만 고치면 기간에 따라 답이 달라질 수 있다. 힌트로 모양을 고정했으므로 행사 상품이 늘거나 영수증 수가 크게 바뀌면 경계(7일)를 다시 재야 한다. 원래 원인은 처음 파싱할 때의 바인드 값으로 만든 계획을 다른 기간도 같이 쓴 바인드 피킹이다. 힌트 없이 하나로 두고 ACS(Adaptive Cursor Sharing)에 맡기는 방법도 있지만, ACS는 몇 번 느리게 돈 뒤에야 계획을 나누므로 이 화면에서는 SQL을 나누는 쪽이 확실하다.

해설

어느 모양이든 답은 같다: 네 모양은 모두 같은 결과를 낸다. 고르는 기준은 일량뿐이고, 일량은 바깥 영수증 수에 따라 뒤집힌다.
PUSH_SUBQ와 NL 세미 조인: 일 조회에서 NO_UNNEST PUSH_SUBQ 대신 UNNEST NL_SJ로 펼쳐도 거의 같다. 다만 NO_UNNEST만 남기고 PUSH_SUBQ를 빼면 지금처럼 조인 뒤에 걸러 일 조회도 4,961블록이 된다.
행사 상품 인덱스: CF11 (ITM_NO, SLE_NO) 인덱스가 있으면 월 조회의 해시 세미 조인이 CF11 전체 대신 행사 상품 줄만 인덱스에서 읽어(100블록 안팎) 더 싸지고, 경계도 하루 아래로 내려간다. 대신 가장 큰 테이블의 인덱스가 하나 늘어난다(SUBQ-021, 교재 04화).
바인드 피킹: 처음 파싱할 때의 바인드 값으로 만든 계획을 다른 값도 같이 쓰는 문제는 교재 11화와 PARSE 단원에서 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황