매장 칸을 비우고 조회하면 하루치 매장별 주문 수가 백 배 느려지고 합계도 모자란다

중급SELECT버거40 / 700

상황

버거원 본사 운영팀의 「일별 매장 주문」은 날짜(필수)와 매장 번호(선택)를 받아 매장별 주문 수를 보여 준다. 매장 번호를 비우면 전 매장이 나온다. 개발자는 한 SQL로 두 경우를 다 받으려고 SHOP_NO LIKE :v_shop || '%'로 썼다. 매장 번호를 비우면 '%' 하나만 넘어간다. 매장을 넣으면 0.01초인데 비우면 0.4초가 걸린다. 오과장은 "키오스크 장애가 있던 날은 이 화면의 전 매장 합계가 일 마감 보고서의 주문 수보다 적다"는 민원도 함께 넘겼다. 아래는 같은 커서의 두 실행이다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) 2012-01-01 ~ 2013-12-31 · 날짜순 적재 · 하루 약 1,344건 · 매장 200곳('SH0001' ~ 'SH0200')
SHOP_NO는 NOT NULL 제약이 없다. 2013-03-02 ~ 03-05 키오스크 장애 때 매장 번호 없이 들어온 주문 1,204건이 있다 (문제 설정: 교재의 ORD에는 SHOP_NO가 빈 행이 없다)
인덱스 ORD_X01 (ORD_DT, ORD_HMS): 리프 3,774 · CF 22,223 / IX_ORD_SHOP_DT (SHOP_NO, ORD_DT): 매장 화면용 · BLEVEL 2 · 리프 3,310 · 통계 최신
이 커서는 아침에 매장 번호를 넣은 조회로 처음 파싱되었다

원본 SQL

sql

SELECT o.SHOP_NO, COUNT(*) AS day_cnt
  FROM ORD o
 WHERE o.ORD_DT = :v_dt
   AND o.SHOP_NO LIKE :v_shop || '%'
 GROUP BY o.SHOP_NO

실행계획 학습용 예시

xplan

[첫 실행 · :v_shop = 'SH0012' · :v_dt = '20130614']
-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |      1 |        |      1 |00:00:00.01 |      19 |
|   1 |  SORT GROUP BY NOSORT|                |      1 |      1 |      1 |00:00:00.01 |      19 |
|*  2 |   INDEX RANGE SCAN   | IX_ORD_SHOP_DT |      1 |      7 |      7 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------------------

[같은 커서 · 매장 칸을 비움(:v_shop = NULL → '%') · :v_dt = '20130614']
-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |      1 |        |    200 |00:00:00.38 |    3313 |
|   1 |  SORT GROUP BY NOSORT|                |      1 |      1 |    200 |00:00:00.38 |    3313 |
|*  2 |   INDEX RANGE SCAN   | IX_ORD_SHOP_DT |      1 |      7 |   1344 |00:00:00.38 |    3313 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."SHOP_NO" LIKE :V_SHOP||'%' AND "O"."ORD_DT"=:V_DT)
       filter(("O"."ORD_DT"=:V_DT AND "O"."SHOP_NO" LIKE :V_SHOP||'%'))

문제

같은 커서인데 매장 칸을 비우면 19블록이 3,313블록으로 느는 이유를 실행계획으로 설명하시오. 비운 칸이 왜 첫 실행의 계획을 그대로 쓰는지도 설명하시오.
키오스크 장애가 있던 날 전 매장 합계가 모자라는 이유를 설명하시오.
두 경우가 각자 맞는 길로 읽도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 매장을 지정했을 때 : 두 컬럼 모두 = 로 (IX_ORD_SHOP_DT 의 access 두 개)
SELECT o.SHOP_NO, COUNT(*) AS day_cnt
  FROM ORD o
 WHERE o.SHOP_NO = :v_shop
   AND o.ORD_DT  = :v_dt
 GROUP BY o.SHOP_NO;

-- ② 매장을 비웠을 때 : 매장 조건을 아예 빼고 날짜로 찾는다 (ORD_X01) · 매장 미확인(NULL)도 한 줄로 나온다
SELECT o.SHOP_NO, COUNT(*) AS day_cnt
  FROM ORD o
 WHERE o.ORD_DT = :v_dt
 GROUP BY o.SHOP_NO;

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

xplan

[① · 'SH0012' · '20130614']
----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT GROUP BY NOSORT   |                |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN      | IX_ORD_SHOP_DT |      1 |      7 |      7 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------
   2 - access("O"."SHOP_NO"=:V_SHOP AND "O"."ORD_DT"=:V_DT)

[② · 매장 비움 · '20130614']
----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                |      1 |        |    200 |00:00:00.01 |      35 |
|   1 |  HASH GROUP BY          |                |      1 |    200 |    200 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ORD       |      1 |   1368 |   1344 |00:00:00.01 |      35 |
|*  3 |    INDEX RANGE SCAN     | ORD_X01        |      1 |   1368 |   1344 |00:00:00.01 |       6 |
----------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_DT"=:V_DT)

모범 답안 서술

비운 칸은 '%' 하나가 되어 SHOP_NO LIKE '%'로 들어간다. 정렬 위에서 '%'가 고르는 범위는 처음부터 끝까지다. 2번 줄은 이름만 INDEX RANGE SCAN일 뿐 IX_ORD_SHOP_DT의 리프 3,310블록을 다 읽고, 뒤 컬럼 ORD_DT는 범위를 줄이지 못하는 filter로만 쓰여 100만 항목 가운데 그날 1,344개를 골라낸다(Buffers 3,313). 계획이 바뀌지 않은 것은 바인드 피킹 때문이다. 이 커서는 아침에 'SH0012'로 처음 파싱되었고, 옵티마이저는 그 값을 엿보고 매장 하나의 하루치(E-Rows 7)라고 보아 이 인덱스를 골랐다. 그 뒤로는 바인드 값이 '%'로 바뀌어도 같은 커서를 공유해 같은 계획을 쓴다. E-Rows 7과 A-Rows 1,344가 크게 어긋나는 것이 그 흔적이다. 합계가 모자라는 것도 같은 LIKE 때문이다. NULL은 어떤 LIKE에도 걸리지 않는다. 그래서 키오스크 장애 때 매장 번호 없이 들어온 주문은 '전 매장'에서 빠진다. 비운 칸의 뜻은 '조건 없음'인데, SQL은 'SHOP_NO에 값이 있는 주문'을 셌다. 두 경우를 SQL 두 개로 나눈다. 매장을 지정하면 SHOP_NO = :v_shop AND ORD_DT = :v_dt로 두 컬럼 모두 access가 되어 4블록이면 된다. 비우면 매장 조건을 아예 빼고 날짜만 준다. 그날 주문은 ORD_X01에서 찾아가고, 날짜순으로 쌓인 테이블 블록 30개 남짓만 읽는다(Buffers 3,313 → 35). 매장 미확인 주문도 SHOP_NO가 NULL인 한 줄로 나오므로 일 마감 보고서와 합계가 맞는다.

해설

한 SQL로 받아야 한다면 UNION ALL로 두 갈래를 두고 :v_shop IS NULL과 :v_shop IS NOT NULL로 한쪽만 돌게 하는 방법도 있다. 옵티마이저가 갈래마다 계획을 따로 세운다. 이런 옵션 조건 처리는 OR와 옵션 조건 세트(IDX s4)에서 더 다룬다.
입력값도 검사한다. 사용자가 'SH01%'나 'SH_012'처럼 와일드카드를 직접 넣으면 범위가 어디까지일지 알 수 없다. 매장은 목록에서 고르게 하거나 형식을 검사한다.
ACS(적응적 커서 공유)가 켜져 있으면 몇 번 뒤에 새 자식 커서가 생길 수 있다. 하지만 그때까지 느린 실행이 쌓이고, NULL이 빠지는 결과는 ACS로 고쳐지지 않는다(교재 11화 §6, §7).
SHOP_NO에 진짜 NULL이 있어서 NOT NULL 제약을 걸 수 없다. 이런 컬럼에서 '전체'는 LIKE '%'가 아니라 조건을 빼는 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황