PART-007

본사 대시보드의 월 매장 실적이 한 달을 보려고 47,460블록을 읽는다

기초SELECT버거382 / 700

상황

본사 영업기획팀의 「월 매장 실적」 대시보드(Java, MyBatis)는 월을 고르면 매장별 주문 건수와 영업일 수를 보여 준다. 주문 테이블을 LIST 파티션 복사본 ORD_LIST로 옮긴 뒤에도 빨라지지 않았다. 선배는 "바인드 변수라서 칸을 못 고르는 것 같으니 리터럴로 바꾸자"라고 한다. 매퍼 파라미터 DTO의 ym 필드는 int다.

테이블과 인덱스

ORD_LIST 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY LIST (ORD_YM) 25칸(P201201 ~ P201312 24칸과 비어 있는 P_DEFAULT (DEFAULT)). 칸 하나를 FULL로 읽으면 약 1,980블록이다(데이터 약 970블록과 적재 때 생긴 빈 블록 약 1,006블록, 교재 14화 §8.5).
ORD_YM은 VARCHAR2(6) '201206' 형식의 파티션 키다. 2012년 6월은 41,040건이고 매장은 200개다.
LOCAL 인덱스 ORD_LIST_PK (ORD_NO, ORD_YM)과 ORD_LIST_X01 (ORD_DT, ORD_HMS)는 이 조회에 쓰이지 않는다. 통계는 최신이다.

원본 SQL

sql

-- MyBatis 매퍼 : WHERE ORD_YM = #{ym}   (DTO 의 ym 은 int)
SELECT SHOP_NO, COUNT(*) AS ord_cnt, COUNT(DISTINCT ORD_DT) AS work_days
  FROM ORD_LIST
 WHERE ORD_YM = :1
 GROUP BY SHOP_NO
 ORDER BY SHOP_NO

실행계획 학습용 예시 · 교재 14화 규모

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        |    200 | 00:00:00.47 |   47460 |        |       |
|   1 |  SORT GROUP BY       |          |      1 |    200 |    200 | 00:00:00.47 |   47460 |        |       |
|   2 |   PARTITION LIST ALL |          |      1 |  10000 |  41040 | 00:00:00.43 |   47460 |      1 |    25 |
|*  3 |    TABLE ACCESS FULL | ORD_LIST |     24 |  10000 |  41040 | 00:00:00.42 |   47460 |      1 |    25 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(TO_NUMBER("ORD_YM")=:1)

[바인드 — V$SQL_BIND_CAPTURE]
   NAME  DATATYPE_STRING  VALUE_STRING
   :1    NUMBER           201206

문제

선배의 진단(바인드 변수라서 칸을 못 고른다)이 맞는지 실행계획과 바인드 정보로 판단하시오.
6월 칸 하나만 읽도록 고치고, 고친 뒤 실행계획의 Pstart와 Pstop이 어떻게 찍힐지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 근본 처방 : DTO 의 ym 을 String 으로 (또는 #{ym, jdbcType=VARCHAR}) → 바인드가 VARCHAR2 로 넘어온다
-- 매퍼를 당장 못 바꾸면 SQL 에서 값 쪽을 문자로 바꾼다 (컬럼은 맨몸)
SELECT SHOP_NO, COUNT(*) AS ord_cnt, COUNT(DISTINCT ORD_DT) AS work_days
  FROM ORD_LIST
 WHERE ORD_YM = TO_CHAR(:1)
 GROUP BY SHOP_NO
 ORDER BY SHOP_NO;

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |    200 | 00:00:00.05 |    1980 |        |       |
|   1 |  SORT GROUP BY          |          |      1 |    200 |    200 | 00:00:00.05 |    1980 |        |       |
|   2 |   PARTITION LIST SINGLE |          |      1 |  41040 |  41040 | 00:00:00.03 |    1980 |    KEY |   KEY |
|*  3 |    TABLE ACCESS FULL    | ORD_LIST |      1 |  41040 |  41040 | 00:00:00.02 |    1980 |    KEY |   KEY |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ORD_YM"=TO_CHAR(:1))

  (실행 때 고른 칸 — 6번 P201206)

모범 답안 서술

선배의 진단은 틀렸다. 3번 줄의 조건은 filter(TO_NUMBER("ORD_YM")=:1)이고, 바인드 정보를 보면 :1이 NUMBER(201206)로 들어왔다. DTO의 ym이 int라서 MyBatis가 숫자로 바인드했고, 숫자와 문자를 비교하면 문자 쪽이 숫자로 바뀐다(묵시적 형변환). 바뀐 쪽이 파티션 키 컬럼 ORD_YM이다. LIST 칸막이에는 '201206' 같은 값 목록이 붙어 있는데 TO_NUMBER(ORD_YM)은 그 목록과 직접 맞대 볼 수 없다. 그래서 2번 줄처럼 PARTITION LIST ALL(Pstart 1, Pstop 25)로 데이터가 있는 24칸을 다 열었다(Starts 24, Buffers 47,460). 바인드 변수는 원인이 아니다. 바인드 변수여도 타입이 맞으면 Pstart와 Pstop이 KEY로 찍히고, 실행할 때 값을 보고 칸 하나를 고른다. 리터럴로 바꿔서 빨라진다면 그것은 '201206'처럼 따옴표를 붙여 문자로 넘겼기 때문이지 리터럴이라서가 아니다. 숫자 리터럴 ORD_YM = 201206도 똑같이 24칸을 연다. 처방은 값을 문자로 넘기는 것이다. DTO의 ym을 String 타입으로 바꾸거나 매퍼에 jdbcType=VARCHAR를 준다. 매퍼를 당장 못 바꾸면 SQL에서 값 쪽을 TO_CHAR(:1)로 바꾼다. 그러면 2번 줄이 PARTITION LIST SINGLE이 되고 Pstart와 Pstop은 KEY로 찍히며, 실행할 때 6월 칸 하나만 연다(Starts 1). Buffers는 47,460 → 1,980으로 준다.

해설

숫자는 문자를 이긴다(교재 03화 §1). ORD_YM 값이 전부 숫자라서 에러 없이 조용히 느려지기만 한다. 숫자로 바꿀 수 없는 값이 P_DEFAULT에 들어오면 그때 ORA-01722로 터진다.
리터럴로 바꾸자는 처방의 대가: 월은 24가지뿐이라 하드 파싱 부담은 작다. 하지만 진짜 원인(타입)이 남아서, 같은 DTO를 쓰는 다른 SQL도 같은 병을 앓는다.
교재 14화 §7.3 실측: ORD_LIST에서 ORD_YM = '201206'은 LIST SINGLE, 1,980블록이었고, 칸을 고르지 못한 조건은 LIST ALL, 47,460블록이었다.
진단할 때는 바인드 값만이 아니라 타입(V$SQL_BIND_CAPTURE.DATATYPE_STRING)까지 본다. 같은 SQL 문장이라도 바인드 타입이 다르면 자식 커서가 따로 생긴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황