JOIN-014

힌트를 USE_HASH로 일괄 바꾼 뒤 '최근 주문 50줄' 상황판이 3초씩 걸린다

중급SELECT버거164 / 700

상황

본사 상황실의 「최근 주문」 상황판은 전 매장의 가장 최근 주문 상품 50줄을 보여 주고 10초마다 새로 고친다(하루 8,640번). 지난달 외부 컨설턴트가 집계 배치를 빠르게 하려고 주문 모듈 SQL의 USE_NL을 USE_HASH로 일괄 바꿨고, 이 상황판 SQL도 함께 바뀌었다. 그 뒤로 상황판이 한 번에 3초씩 걸리고 서버 CPU 사용률이 올랐다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 날짜순 적재
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 주문당 평균 2.47줄
ITEM 상품: 92행 · 5블록 · ITEM_PK (ITEM_ID)
pga_aggregate_target 512M · 통계 최신

원본 SQL

sql

SELECT *
  FROM (SELECT /*+ LEADING(o i t) USE_HASH(i t) */
               o.ORD_DT, o.ORD_HMS, o.SHOP_NO, o.ORD_NO,
               t.ITEM_NM, i.ORD_ITEM_QTY
          FROM ORD o, ORD_ITEM i, ITEM t
         WHERE i.ORD_NO  = o.ORD_NO
           AND t.ITEM_ID = i.ITEM_ID
         ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC)
 WHERE ROWNUM <= 50

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |        |     50 |00:00:03.06 |   77692 |           |
|*  1 |  COUNT STOPKEY           |          |      1 |        |     50 |00:00:03.06 |   77692 |           |
|   2 |   VIEW                   |          |      1 |  2472K |     50 |00:00:03.06 |   77692 |           |
|*  3 |    SORT ORDER BY STOPKEY |          |      1 |  2472K |     50 |00:00:03.06 |   77692 |   18K (0) |
|*  4 |     HASH JOIN            |          |      1 |  2472K |  2472K |00:00:02.41 |   77692 | 1036K (0) |
|   5 |      TABLE ACCESS FULL   | ITEM     |      1 |     92 |     92 |00:00:00.01 |       5 |           |
|*  6 |      HASH JOIN           |          |      1 |  2472K |  2472K |00:00:01.88 |   77687 |   47M (0) |
|   7 |       TABLE ACCESS FULL  | ORD      |      1 |  1000K |  1000K |00:00:00.29 |   22422 |           |
|   8 |       TABLE ACCESS FULL  | ORD_ITEM |      1 |  2472K |  2472K |00:00:00.61 |   55265 |           |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=50)
   3 - filter(ROWNUM<=50)
   4 - access("T"."ITEM_ID"="I"."ITEM_ID")
   6 - access("I"."ORD_NO"="O"."ORD_NO")

문제

이 SQL이 50줄을 내려고 몇 행을 조인하고 정렬하는지 A-Rows와 Buffers로 설명하고, 해시 조인에서는 왜 50줄에서 멈출 수 없는지 서술하시오.
50줄을 읽으면 멈추도록 SQL을 고치고, 이 방식이 성립하는 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 일괄 치환된 해시 힌트를 뗀다. 굳이 고정한다면 NL 과 인덱스 역순으로 뜻을 적는다
SELECT *
  FROM (SELECT /*+ LEADING(o i t) USE_NL(i t) INDEX_DESC(o ORD_X01) */
               o.ORD_DT, o.ORD_HMS, o.SHOP_NO, o.ORD_NO,
               t.ITEM_NM, i.ORD_ITEM_QTY
          FROM ORD o, ORD_ITEM i, ITEM t
         WHERE i.ORD_NO  = o.ORD_NO
           AND t.ITEM_ID = i.ITEM_ID
         ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC)
 WHERE ROWNUM <= 50

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |      1 |        |     50 |00:00:00.01 |     122 |
|*  1 |  COUNT STOPKEY                    |             |      1 |        |     50 |00:00:00.01 |     122 |
|   2 |   VIEW                            |             |      1 |     50 |     50 |00:00:00.01 |     122 |
|   3 |    NESTED LOOPS                   |             |      1 |     50 |     50 |00:00:00.01 |     122 |
|   4 |     NESTED LOOPS                  |             |      1 |     50 |     50 |00:00:00.01 |      72 |
|   5 |      NESTED LOOPS                 |             |      1 |     50 |     50 |00:00:00.01 |      70 |
|   6 |       TABLE ACCESS BY INDEX ROWID | ORD         |      1 |  1000K |     21 |00:00:00.01 |       5 |
|   7 |        INDEX FULL SCAN DESCENDING | ORD_X01     |      1 |     20 |     21 |00:00:00.01 |       4 |
|   8 |       TABLE ACCESS BY INDEX ROWID | ORD_ITEM    |     21 |      2 |     50 |00:00:00.01 |      65 |
|*  9 |        INDEX RANGE SCAN           | ORD_ITEM_PK |     21 |      2 |     50 |00:00:00.01 |      15 |
|* 10 |      INDEX UNIQUE SCAN            | ITEM_PK     |     50 |      1 |     50 |00:00:00.01 |       2 |
|  11 |     TABLE ACCESS BY INDEX ROWID   | ITEM        |     50 |      1 |     50 |00:00:00.01 |      50 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=50)
   9 - access("I"."ORD_NO"="O"."ORD_NO")
  10 - access("T"."ITEM_ID"="I"."ITEM_ID")

모범 답안 서술

4번과 6번 줄의 A-Rows가 2,472K다. 50줄을 보여 주려고 주문 100만 건과 주문상품 247만 줄을 전부 해시 조인했고, 3번 줄 SORT ORDER BY STOPKEY에서 그 결과를 정렬한 뒤 앞의 50줄만 남겼다. ORD와 ORD_ITEM을 한 번씩 FULL로 읽어 Buffers가 77,692다. 10초마다 새로 고치므로 하루 8,640번, 약 6억 7천만 블록이다. 해시 조인은 build 쪽을 끝까지 읽어 해시 테이블을 만든 뒤에야 probe를 시작하고, 정렬은 마지막 행까지 받아야 첫 행을 내놓는다. 그래서 ROWNUM <= 50이 있어도 일을 다 마친 다음에야 멈춘다. 부분범위 처리가 되지 않는 구조다. USE_HASH를 떼고 NL 조인으로 ORD_X01을 거꾸로(INDEX FULL SCAN DESCENDING) 읽는다. 인덱스가 이미 ORD_DT, ORD_HMS 순서라서 정렬이 필요 없고, 가장 최근 주문부터 한 건씩 주문상품과 상품명을 붙여 내보낸다. 50줄이 차면 COUNT STOPKEY가 멈춘다. 주문 21건만 읽고 Buffers는 77,692 → 122로 준다. 이 방식이 성립하려면 ORDER BY가 드라이빙 테이블 인덱스의 컬럼 순서와 같아야 하고, 그 위에 정렬이나 해시 조인, GROUP BY가 없어야 한다. 정렬 기준에 inner 쪽 컬럼(상품명 등)이 끼면 다시 전부 조인해서 정렬해야 한다. 컨설턴트의 일괄 치환은 전부 읽는 집계 배치에는 맞았지만, 앞의 몇 줄만 보는 상황판에는 반대로 작용했다. 힌트는 SQL마다 그 SQL이 전부 읽는지, 앞부분만 읽는지를 보고 정한다.

해설

동률까지 고정하려면: 같은 초에 들어온 주문 사이의 순서까지 정하려면 ORDER BY에 ORD_NO를 더하고 인덱스도 (ORD_DT, ORD_HMS, ORD_NO)로 맞춰야 정렬 없이 멈출 수 있다. 상황판은 동률 순서가 중요하지 않아 인덱스를 더 만들지 않았다(교재 09화 §7).
해시 조인과 부분범위: build 쪽이 아주 작으면(ITEM 92행) 해시 조인도 probe 쪽 행을 받는 대로 내보낼 수 있다. 하지만 이 SQL처럼 결과를 정렬해야 하면 소용이 없다. 정렬을 인덱스가 대신하게 하는 것이 먼저다.
힌트를 떼기만 해도 된다: ROWNUM <= 50이 있으면 옵티마이저는 앞의 50행을 기준으로 비용을 계산해 같은 NL 계획을 고른다. 고정한다면 힌트 유효기간 대장에 '앞부분만 읽는 SQL'이라는 성격을 함께 적는다(교재 06화 §8).
FETCH FIRST 50 ROWS ONLY로 써도 같은 계획이 나온다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황