바인드 변수로 바꾸면 피킹 때문에 위험하다며 날짜를 문자열로 이어 붙인다

기초SELECT유통572 / 700

상황

본사 「일별 판매 건수 추이」 화면은 시작일과 종료일을 골라 날짜마다 판매 건수를 그래프로 보여 준다(하루 2,000번쯤, 기간은 하루 ~ 2년). 지난달 인스턴스의 CURSOR_SHARING = FORCE를 EXACT로 되돌린 뒤로 이 화면이 하드 파싱 상위에 올라왔다. 리뷰에서 바인드 변수를 쓰라고 하자 개발자가 답했다. "바인드로 바꾸면 처음 들어온 값이 계획을 정한다고 들었습니다. 기간이 하루일 때와 2년일 때 계획이 같을 리 없잖아요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) '20230601' · 하루 약 706건 · 약 2년치
인덱스 IX_CF10_SLE_DT (SLE_DT): 리프 1,393블록 · 통계 최신 · 히스토그램 없음
CURSOR_SHARING = EXACT · 바인드 피킹 켬

원본 SQL

sql

// SalesTrendDao.java : 날짜를 SQL 문자열에 이어 붙인다 (화면이 'YYYYMMDD'를 넘긴다)
String sql = "SELECT SLE_DT, COUNT(*) AS SLE_CNT FROM CF10 "
           + "WHERE SLE_DT BETWEEN '" + from + "' AND '" + to + "' "
           + "GROUP BY SLE_DT ORDER BY SLE_DT";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

실행계획 학습용 예시

xplan

[같은 모양의 문장을 기간별로 (리터럴 · 실측)]
 기간     A-Rows    계획                                             Buffers
 하루         706   INDEX RANGE SCAN IX_CF10_SLE_DT + SORT GROUP BY NOSORT      4
 31일      21,890   〃                                                  63
 1년      257,700   〃                                                 720
 2년 전체  500,000   〃 (INDEX FAST FULL SCAN + HASH GROUP BY도 비슷)     1,395
 (테이블에는 어느 기간이든 가지 않는다 · FULL이면 4,583)

[V$SQLAREA — 어제 하루 · FORCE_MATCHING_SIGNATURE 하나로 묶은 무리]
  PARENTS 1,310 · EXECS 2,140 · 하드 파싱 1,310 · SHARABLE_MEM 24.1MB

[31일 한 번]
---------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |     31 |00:00:00.01 |      63 |
|   1 |  SORT GROUP BY NOSORT |                |      1 |     31 |     31 |00:00:00.01 |      63 |
|*  2 |   INDEX RANGE SCAN    | IX_CF10_SLE_DT |      1 |  21886 |  21890 |00:00:00.01 |      63 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SLE_DT">='20230601' AND "SLE_DT"<='20230701')

문제

개발자의 걱정(첫 값이 계획을 정한다)이 이 문장에서 맞는지 위 측정으로 판단하시오. 바인드 피킹이 해를 끼치려면 무엇이 성립해야 하는지 함께 쓰시오.
코드를 고치고, 고친 문장이 앞으로도 안전하려면 무엇을 지켜야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

// 날짜는 바인드로 · 컬럼(VARCHAR2(8))과 같은 문자열 타입으로
String sql = "SELECT SLE_DT, COUNT(*) AS SLE_CNT FROM CF10 "
           + "WHERE SLE_DT BETWEEN ? AND ? "
           + "GROUP BY SLE_DT ORDER BY SLE_DT";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, from);   // 'YYYYMMDD'
    ps.setString(2, to);
    ResultSet rs = ps.executeQuery();
    // ...
}

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

xplan

[다음 날 — 바인드 · 자식 하나]
 PARENTS 1 · EXECS 2,151 · 하드 파싱 1 · SHARABLE_MEM 21KB
 계획 INDEX RANGE SCAN IX_CF10_SLE_DT + SORT GROUP BY NOSORT · 하루 4 · 31일 63 · 1년 720 · 2년 1,395
 (어느 기간으로 태어나든 같은 계획)

모범 답안 서술

개발자의 걱정은 일반론으로는 맞지만 이 문장에는 해당하지 않는다. 바인드 피킹이 해를 끼치려면 값마다 맞는 계획이 달라야 한다. 이 문장은 SLE_DT 하나만 읽고 세므로 IX_CF10_SLE_DT만으로 답이 나온다. 하루(4블록)든 31일(63블록)이든 2년(1,395블록)이든 인덱스 범위만 읽고, 인덱스 순서 그대로 묶어서(SORT GROUP BY NOSORT) 테이블에는 가지 않는다. 2년 전체도 FULL(4,583)보다 적게 읽으므로, 어느 기간을 먼저 엿보든 같은 계획이 선다. 반대로 리터럴은 비용이 분명하다. 날짜 조합마다 다른 문장이라 어제 하루 호출 2,140번 중 1,310번을 하드 파싱했고, 리터럴만 다른 부모 1,310개가 공유 풀을 24.1MB 차지했다. FORCE를 되돌린 뒤 이 화면이 하드 파싱 상위에 오른 까닭이다. 날짜를 문자열로 이어 붙이는 코드는 SQL 주입의 구멍이기도 하다. 그래서 날짜를 바인드 변수로 넘긴다. 컬럼이 VARCHAR2(8)이므로 setString으로 같은 타입을 맞춘다(숫자로 넘기면 컬럼이 TO_NUMBER로 바뀌어 인덱스를 못 쓴다). 문장이 하나가 되어 하드 파싱은 1,310 → 1번, 공유 메모리는 24.1MB → 21KB가 된다. 앞으로도 안전하려면 이 문장이 읽는 컬럼이 인덱스 밖으로 나가지 않는지 지켜본다. 누가 매장 번호(SHP_NO)를 하나 더 보여 달라고 하면 테이블까지 가야 하고, 그때부터는 기간에 따라 맞는 계획이 갈릴 수 있다. 그런 변경이 오면 하루와 2년으로 다시 재고, 필요하면 인덱스에 컬럼을 더하거나 문장을 나눈다.

해설

"바인드는 위험하다"도 "바인드는 늘 옳다"도 일반론이다. 문장마다 값이 바뀌었을 때 맞는 계획이 바뀌는지를 재 보고 정한다. 교재 11화의 SQL 코딩 가이드는 화면이 고르는 값은 바인드로, 값마다 계획이 달라야 하는 코드값은 상수로 쓰라고 한다.
히스토그램이 없어도 범위 바인드라 이 문장은 IS_BIND_SENSITIVE = Y가 된다. 표시가 붙는다고 계획이 흔들리는 것은 아니다. 1년 넘는 기간(1,000블록 넘게)과 짧은 기간이 섞이면 ACS가 자식을 몇 개 나눌 수 있지만 계획은 같다. 자식이 눈에 띄게 늘면 PARSE-023처럼 다룬다.
FORCE를 다시 켜서 해결하려 하면 다른 문장의 일부러 쓴 리터럴까지 묶인다(PARSE-016). 이 화면은 소스에서 고친다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황