점주 앱의 「시간대별 주문」만 느리다, 같은 SQL을 SQL Developer에서 돌리면 0.01초다

기초SELECT버거22 / 700

상황

버거원 점주 앱의 「시간대별 주문」 화면은 매장의 그날 주문 수를 시간대별로 보여 준다. 가맹점 200곳이 하루 4만 번쯤 연다. 화면이 뜨는 데 0.3초가 넘는다는 불만이 올라왔다. 개발자는 SQL을 복사해 SQL Developer에서 날짜 '20130315'와 매장 'SH0042'를 넣고 돌려 보았다. "0.01초인데요? DB 문제 아닌가요?" 앱 코드는 날짜를 정수로 다루는 공통 함수를 거쳐 ps.setInt(1, 20130315), ps.setString(2, "SH0042")로 값을 넘긴다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) ('20120101' ~ '20131231') · ORD_HMS VARCHAR2(6) · SHOP_NO VARCHAR2(10) 'SH0001' ~ 'SH0200' · 하루 약 1,370건, 매장 하나의 하루 주문은 평균 7건
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 3,570블록 · 날짜순 적재라 CF 22,223 · 통계 최신

원본 SQL

sql

SELECT SUBSTR(ORD_HMS, 1, 2) AS hh, COUNT(*) AS cnt
  FROM ORD
 WHERE ORD_DT  = :1
   AND SHOP_NO = :2
 GROUP BY SUBSTR(ORD_HMS, 1, 2)
 ORDER BY hh

실행계획 학습용 예시

xplan

[앱에서 실행 · 2013-03-15, SH0042]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      6 |00:00:00.31 |   22426 |
|   1 |  SORT GROUP BY     |      |      1 |     50 |      6 |00:00:00.31 |   22426 |
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |     50 |      7 |00:00:00.31 |   22426 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SHOP_NO"=:2 AND TO_NUMBER("ORD_DT")=:1))

[V$SQL_BIND_CAPTURE]
NAME  POSITION  DATATYPE_STRING  VALUE_STRING
----  --------  ---------------  ------------
:1           1  NUMBER           20130315
:2           2  VARCHAR2(32)     SH0042

문제

SQL Developer에서는 빠르고 앱에서만 느린 이유를 Predicate Information과 바인드 정보로 설명하시오.
앱에서도 ORD_X01로 그날 범위만 읽도록 SQL이나 바인드 방식을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 앱 : ps.setInt(1, 20130315) → ps.setString(1, "20130315")  (문자 컬럼에는 문자로 바인드)
--      앱을 바로 못 고치면 SQL에서 바인드 쪽을 문자로 바꾼다 (컬럼은 그대로)
SELECT SUBSTR(ORD_HMS, 1, 2) AS hh, COUNT(*) AS cnt
  FROM ORD
 WHERE ORD_DT  = TO_CHAR(:1)
   AND SHOP_NO = :2
 GROUP BY SUBSTR(ORD_HMS, 1, 2)
 ORDER BY hh

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

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      6 |00:00:00.01 |      38 |
|   1 |  SORT GROUP BY               |         |      1 |      7 |      6 |00:00:00.01 |      38 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ORD     |      1 |      7 |      7 |00:00:00.01 |      38 |
|*  3 |    INDEX RANGE SCAN          | ORD_X01 |      1 |   1370 |   1370 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SHOP_NO"=:2)
   3 - access("ORD_DT"=TO_CHAR(:1))

모범 답안 서술

2번 줄 조건은 filter(TO_NUMBER("ORD_DT")=:1)이다. SQL 문장에는 TO_NUMBER가 없다. V$SQL_BIND_CAPTURE를 보면 :1의 타입이 NUMBER다. 앱이 setInt로 날짜를 숫자로 넘겼기 때문에, 문자 컬럼과 숫자를 비교하게 된 오라클이 묵시적 형변환으로 문자 쪽, 즉 컬럼 ORD_DT를 숫자로 바꿨다. 컬럼이 가공되어 ORD_X01에서 범위를 지목할 수 없으니 100만 행을 다 읽고 7건을 남겨 시간대별로 셌다(Buffers 22,426). SQL Developer에서는 '20130315'라는 문자 리터럴을 넣었으므로 형변환이 없었고 인덱스를 탔다. 같은 SQL 문장이라도 바인드 타입이 다르면 계획이 다르다. 앱에서만 느리면 바인드 타입부터 확인한다. 고치는 곳은 앱이다. 날짜 값을 setString으로 문자로 넘긴다. 앱을 바로 배포할 수 없으면 SQL에서 ORD_DT = TO_CHAR(:1)처럼 바인드 쪽을 문자로 바꾼다. 그러면 access("ORD_DT"=TO_CHAR(:1))로 ORD_X01에서 그날 1,370건의 범위만 읽고, 그중 매장 조건에 맞는 7건을 센다(Buffers 22,426 → 38).

해설

setInt는 숫자 타입으로 바인드한다. 오라클은 값의 모양이 아니라 바인드 타입으로 비교 방법을 정하므로, 값이 20130315처럼 날짜 모양이어도 컬럼 쪽이 TO_NUMBER로 바뀐다(교재 03화의 SLE_DT = 20230601과 같은 일이다).
ORM을 쓴다면 엔티티 필드 타입이 원인일 때가 많다. 문자 컬럼 ORD_DT에 정수형 필드를 매핑하면 모든 조회가 숫자로 바인드된다. 매핑 타입을 컬럼 타입과 맞춘다.
매장 하나의 주문 7건을 찾으려고 그날 1,370건을 테이블에서 읽는다. 더 줄이려면 (SHOP_NO, ORD_DT, ORD_HMS) 같은 인덱스를 검토한다. 이것은 형변환과 별개인 인덱스 설계 문제다(ACC 단원).
같은 SQL 문장도 바인드 타입이 다르면 자식 커서가 따로 생긴다. 앱과 도구의 계획이 다를 때는 V$SQL에서 자식 번호까지 확인한다(교재 11화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황