업그레이드 다음 날부터 가맹점주 앱의 「최근 주문 20건」이 2초를 넘긴다

중급SELECT버거246 / 700

상황

가맹점주 앱 첫 화면의 「최근 주문 20건」은 매장의 최근 주문 20건과 주문마다 수량 합계를 보여 준다. 점주 200명이 하루 평균 50번씩 연다. 지난 주말 DB를 11g에서 19c로 올린 다음 날부터 한 번에 2.4초가 걸린다. SQL과 인덱스는 바꾸지 않았다. 업그레이드 전 계획을 보관해 둔 오과장의 메모에는 "11g: INDEX RANGE SCAN DESCENDING, 스칼라 서브쿼리 Starts 20, Buffers 104"라고 적혀 있다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · 매장마다 약 5,000건 · ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS)(이 화면용)
ORD_ITEM: 2,472,717행 · 55,265블록 · 주문당 1 ~ 4줄(같은 주문의 줄은 같은 블록에 있다) · ORD_ITEM_PK (ORD_NO, ITEM_ID)
19c에서는 스칼라 서브쿼리 Unnesting(_optimizer_unnest_scalar_sq)이 기본으로 켜져 있다 · 통계 최신 · 아래는 :shop = 'SH0001'

원본 SQL

sql

SELECT *
  FROM (SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS,
               (SELECT SUM(i.ORD_ITEM_QTY)
                  FROM ORD_ITEM i
                 WHERE i.ORD_NO = o.ORD_NO) AS qty
          FROM ORD o
         WHERE o.SHOP_NO = :shop
         ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC)
 WHERE ROWNUM <= 20

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |      1 |        |     20 |00:00:02.41 |   60310 | 55265 |          |
|*  1 |  COUNT STOPKEY                         |          |      1 |        |     20 |00:00:02.41 |   60310 | 55265 |          |
|   2 |   VIEW                                 |          |      1 |   5000 |     20 |00:00:02.41 |   60310 | 55265 |          |
|*  3 |    SORT ORDER BY STOPKEY               |          |      1 |   5000 |     20 |00:00:02.41 |   60310 | 55265 | 6144 (0) |
|*  4 |     HASH JOIN OUTER                    |          |      1 |   5000 |   5000 |00:00:02.40 |   60310 | 55265 | 1517K (0)|
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   5000 |   5000 |00:00:00.02 |    5045 |     0 |          |
|*  6 |       INDEX RANGE SCAN                 | ORD_X02  |      1 |   5000 |   5000 |00:00:00.01 |      21 |     0 |          |
|   7 |      VIEW                              | VW_SSQ_1 |      1 |   1000K|   1000K|00:00:02.10 |   55265 | 55265 |          |
|   8 |       HASH GROUP BY                    |          |      1 |   1000K|   1000K|00:00:01.95 |   55265 | 55265 |   71M (0)|
|   9 |        TABLE ACCESS FULL               | ORD_ITEM |      1 |   2472K|   2472K|00:00:00.52 |   55265 | 55265 |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   4 - access("ITEM_1"(+)="O"."ORD_NO")
   6 - access("O"."SHOP_NO"=:SHOP)

문제

11g의 계획과 비교해 지금 계획에서 무엇이 바뀌었는지 줄 번호로 설명하시오. 7번 줄의 VW_SSQ_1이 무엇이고, 왜 주문상품 247만 행을 다 읽는지도 쓰시오.
화면에 나가는 20건에만 수량을 구하도록 SQL을 고치시오.
옵티마이저가 왜 이 계획을 골랐는지 설명하고, 원본에 NO_UNNEST만 넣는 것으로 충분한지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 20 건을 먼저 자르고(인덱스를 거꾸로 읽다 멈춘다), 수량은 그 바깥에서 20 번만 구한다
-- 서브쿼리 안의 NO_UNNEST : 펼쳐서 주문상품 전체를 묶는 계획을 막는다 (힌트 대장에 이유와 재검토 조건을 적는다)
SELECT v.ORD_NO, v.ORD_DT, v.ORD_HMS,
       (SELECT /*+ NO_UNNEST */ SUM(i.ORD_ITEM_QTY)
          FROM ORD_ITEM i
         WHERE i.ORD_NO = v.ORD_NO) AS qty
  FROM (SELECT ORD_NO, ORD_DT, ORD_HMS
          FROM (SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS
                  FROM ORD o
                 WHERE o.SHOP_NO = :shop
                 ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC)
         WHERE ROWNUM <= 20) v
 ORDER BY v.ORD_DT DESC, v.ORD_HMS DESC;

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

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |      1 |        |     20 |00:00:00.01 |     104 |
|   1 |  SORT AGGREGATE                        |             |     20 |      1 |     20 |00:00:00.01 |      80 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM    |     20 |      2 |     49 |00:00:00.01 |      80 |
|*  3 |    INDEX RANGE SCAN                    | ORD_ITEM_PK |     20 |      2 |     49 |00:00:00.01 |      60 |
|   4 |  SORT ORDER BY                         |             |      1 |     20 |     20 |00:00:00.01 |     104 |
|   5 |   VIEW                                 |             |      1 |     20 |     20 |00:00:00.01 |      24 |
|*  6 |    COUNT STOPKEY                       |             |      1 |        |     20 |00:00:00.01 |      24 |
|   7 |     VIEW                               |             |      1 |   5000 |     20 |00:00:00.01 |      24 |
|   8 |      TABLE ACCESS BY INDEX ROWID       | ORD         |      1 |   5000 |     20 |00:00:00.01 |      24 |
|*  9 |       INDEX RANGE SCAN DESCENDING      | ORD_X02     |      1 |   5000 |     20 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ORD_NO"=:B1)
   6 - filter(ROWNUM<=20)
   9 - access("O"."SHOP_NO"=:SHOP)

모범 답안 서술

11g에서는 ORD_X02를 거꾸로 읽어(INDEX RANGE SCAN DESCENDING) 20건에서 멈추고, 수량 스칼라 서브쿼리도 그 20건에만 돌았다(Starts 20, Buffers 104). 19c에서는 스칼라 서브쿼리 Unnesting이 일어나 서브쿼리가 7번 줄의 VW_SSQ_1(주문번호별 수량 합계 뷰)로 바뀌었다. 이 뷰에는 매장 조건이 들어가지 못하므로 9번 줄에서 주문상품 247만 행(55,265블록)을 모두 읽고 8번 줄에서 주문 100만 건을 다 묶는다. 5번 줄은 해시 조인을 위해 매장 주문 5,000건을 모두 읽고(5,045블록), 3번 줄 SORT ORDER BY STOPKEY에서야 20건으로 자른다. 부분범위 처리가 사라져 Buffers 60,310, 2.41초가 됐다. 옵티마이저가 이 계획을 고른 것은 비용 때문이다. 정렬 전에 스칼라 서브쿼리를 매장 주문 5,000건(E-Rows 5000) 모두에 부른다고 보면 5,000 × 4 = 약 2만 블록이고, 펼친 쪽은 큰 테이블 하나를 다중 블록으로 읽는 비용으로 계산된다. 화면이 20건에서 멈춘다는 점을 비용에 넣지 못한 것이다. 고칠 때는 두 가지를 함께 쓴다. 정렬과 ROWNUM으로 20건을 먼저 자른 인라인 뷰를 만들고, 수량 스칼라 서브쿼리는 그 바깥에서 부른다. 그러면 계획에 정렬이 생기더라도 서브쿼리는 화면에 나가는 20건에만 돈다. 서브쿼리 안에는 /*+ NO_UNNEST */를 넣어 다시 펼쳐지지 않게 한다. Buffers는 60,310 → 104(주문 24 + 수량 80)이고, 스칼라 서브쿼리 Starts는 20이다. 원본에 NO_UNNEST만 넣어도 지금은 11g 계획으로 돌아간다. 인덱스가 정렬을 대신해 20건에서 멈추기 때문이다. 하지만 ORDER BY에 인덱스에 없는 컬럼이 붙는 날 정렬이 생기면 서브쿼리가 다시 5,000번 돈다. 그래서 서브쿼리를 페이징 바깥으로 빼 둔다.

해설

11g 계획이 빨랐던 이유: 정렬을 인덱스가 대신해서 행이 하나씩 흘러나왔고, ROWNUM이 20건에서 멈추자 그 앞의 스칼라 서브쿼리도 20번만 돌았다. 스칼라 서브쿼리가 싸려면 바깥 집합이 작아야 한다.
시스템 전체에서 Unnesting을 끄는 안: 펼쳐야 빨라지는 SQL(바깥 집합이 큰 리포트)까지 함께 느려진다(교재 08화 §8). 이 화면의 문제는 이 SQL에서 고친다. 힌트에는 넣은 이유와 다시 볼 조건을 힌트 대장에 적어 둔다(교재 06화).
조인으로 바꾸는 안: 페이징한 20건에 주문상품을 조인해 GROUP BY해도 같은 일량이다. 매장 조건 없이 주문상품 전체를 묶어 붙이는 조인은 지금 계획과 똑같아진다.
업그레이드 직후에는 상위 SQL의 계획을 이전 판과 대조하고, 계획 해시가 바뀐 SQL에서 VW_SSQ 같은 변환 이름표를 찾는다(교재 08화 §5).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황