바인드 변수로 고쳤다는 주문 일괄 조회가 여전히 하드 파싱 상위에 있다

중급SELECT한빛유통554 / 700

상황

고객센터의 「주문 일괄 조회」 화면은 상담원이 엑셀에서 주문번호를 1~1,000개 붙여 넣으면 주문 상태와 금액을 보여 준다. 지난달 개발팀은 주문번호를 문자열로 이어 붙이던 코드를 바인드 변수로 고쳤다. 그런데 DBA가 뽑은 하드 파싱 상위 목록에는 여전히 이 화면이 맨 위에 있다. 개발팀은 이렇게 말한다. "이제 값은 전부 ?로 넘기는데요."

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_SEQ는 NUMBER
하루 약 2,400번 호출된다. 붙여 넣는 개수는 1~1,000개로 제각각이고, 대부분은 수십 개 이하다.
1,001개 이상을 붙이면 ORA-01795가 나서 화면이 1,000개로 막아 두었다.
CURSOR_SHARING = EXACT

원본 SQL

sql

// BulkOrderDao.java (지난달 수정) : 리터럴 대신 ? 를 개수만큼 만든다
String marks = String.join(",", Collections.nCopies(ids.size(), "?"));
String sql = "SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD WHERE ORD_SEQ IN (" + marks + ")";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (int i = 0; i < ids.size(); i++) ps.setLong(i + 1, ids.get(i));
    try (ResultSet rs = ps.executeQuery()) { /* 화면에 뿌린다 */ }
}

실행계획 학습용 예시

xplan

[V$SQLAREA · 금요일 17:00 · 이 화면의 부모 커서 (월요일부터 누적)]
  바인드 수   EXECUTIONS   LOADS   SHARABLE_MEM   FORCE_MATCHING_SIGNATURE
          3          214       1          18KB    7781020440133318265
          7          188       1          21KB    1290047755316282003
         12          121       1          26KB    16610993057461230118
        250            3       2         117KB    4436159902384511870
       1000            1       1         412KB    9022157316650884471
  …
  부모 734개 · 실행 9,860 · 합계 61.3MB · FORCE_MATCHING_SIGNATURE 734가지

[바인드 수별 하드 파싱 한 번의 비용 (10046 → TKPROF의 Parse 줄 cpu)]
    10개     0.002초
   100개     0.011초
  1000개     0.094초

[한 번 실행의 계획 — 주문번호 1,000개]
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |   1000 |00:00:00.01 |    3012 |
|   1 |  INLIST ITERATOR             |          |      1 |        |   1000 |00:00:00.01 |    3012 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD    |   1000 |   1000 |   1000 |00:00:00.01 |    3012 |
|*  3 |    INDEX UNIQUE SCAN         | PK_T_ORD |   1000 |   1000 |   1000 |00:00:00.01 |    2008 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(("ORD_SEQ"=:1 OR "ORD_SEQ"=:2 OR "ORD_SEQ"=:3 OR … OR "ORD_SEQ"=:1000))

문제

값을 모두 바인드 변수로 넘기는데도 부모 커서가 734개 생긴 이유를 설명하시오. CURSOR_SHARING = FORCE로는 이 문장들이 묶이지 않는 이유도 쓰시오.
붙여 넣는 개수와 상관없이 문장이 하나가 되도록 고치시오. 고친 방법이 결과와 실행계획에 주는 영향도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만 만든다 : 숫자 목록 타입
CREATE OR REPLACE TYPE NUM_TAB AS TABLE OF NUMBER;

// BulkOrderDao.java : 개수와 상관없이 문장은 하나 · 목록은 배열 값 하나로 넘긴다
String sql = "SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, o.ORD_AMT "
           + "  FROM T_ORD o "
           + " WHERE o.ORD_SEQ IN (SELECT /*+ CARDINALITY(t 50) */ t.COLUMN_VALUE "
           + "                       FROM TABLE(CAST(? AS NUM_TAB)) t)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    Array arr = conn.unwrap(OracleConnection.class).createOracleArray("NUM_TAB", ids.toArray(new Long[0]));
    ps.setArray(1, arr);
    try (ResultSet rs = ps.executeQuery()) { /* 화면에 뿌린다 */ }
}

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

xplan

[고친 뒤 · 다음 주 같은 기간 (학습용 예시)]
  V$SQLAREA            부모 1 · 실행 9,910 · LOADS 1 · 31KB
  parse count (hard)   이 화면 몫 1

[한 번 실행의 계획 — 주문번호 1,000개]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |   1000 |00:00:00.01 |    3010 |
|   1 |  NESTED LOOPS                        |          |      1 |     50 |   1000 |00:00:00.01 |    3010 |
|   2 |   NESTED LOOPS                       |          |      1 |     50 |   1000 |00:00:00.01 |    2010 |
|   3 |    SORT UNIQUE                       |          |      1 |     50 |   1000 |00:00:00.01 |       0 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH|          |      1 |     50 |   1000 |00:00:00.01 |       0 |
|*  5 |    INDEX UNIQUE SCAN                 | PK_T_ORD |   1000 |      1 |   1000 |00:00:00.01 |    2010 |
|   6 |   TABLE ACCESS BY INDEX ROWID        | T_ORD    |   1000 |      1 |   1000 |00:00:00.01 |    1000 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("O"."ORD_SEQ"=VALUE(KOKBF$))

모범 답안 서술

리터럴을 바인드 변수로 바꾼 것은 맞지만, ?의 개수가 문장 텍스트의 일부다. 주문번호가 3개면 IN (?,?,?), 7개면 IN (?,?,?,?,?,?,?)라서 개수가 다르면 다른 부모 커서가 되고, 처음 나오는 개수마다 하드 파싱을 한다. 이번 주에만 부모가 734개 생겼다. 문장에 리터럴이 없으니 FORCE_MATCHING_SIGNATURE도 734가지로 모두 다르고, CURSOR_SHARING = FORCE를 켜도 묶이지 않는다. 게다가 목록이 길수록 파싱이 비싸다. 1,000개짜리 문장은 하드 파싱 한 번에 0.094초가 걸리고 커서 하나가 412KB다. 734개가 61.3MB를 차지하면서 서로를 밀어낸다(250개짜리의 LOADS 2). 목록을 문장 텍스트에서 빼서 값 하나로 넘긴다. 숫자 목록 타입 NUM_TAB을 한 번 만들어 두고, 주문번호를 배열 하나로 바인드해 TABLE(CAST(? AS NUM_TAB))로 펼친다. 개수와 상관없이 문장은 하나이고 하드 파싱도 한 번이다. 실행 일량은 1,000개 기준 3,012 → 3,010블록으로 그대로다. 두 가지를 챙긴다. 첫째, IN 목록은 같은 번호가 두 번 들어와도 한 번만 나오지만, 컬렉션과 그냥 조인하면 두 번 나온다. 그래서 IN (SELECT … FROM TABLE(…)) 꼴로 써서 중복을 없앤다(3번 줄 SORT UNIQUE). 둘째, 옵티마이저는 컬렉션에 몇 건이 들어올지 모른다. 추정이 크게 빗나가면 T_ORD를 FULL로 읽는 해시 조인을 고를 수 있으므로, CARDINALITY 힌트로 흔한 크기(50건)를 알려 주고 계획을 확인한다.

해설

칸 수를 고정하는 방법도 있다. 10, 50, 100, 500, 1,000칸짜리 문장 다섯 개를 두고 남는 칸은 마지막 값을 되풀이해 채우면 문장은 다섯 개뿐이다(교재 11화 §12의 ③). 타입을 만들 수 없는 환경에서 쓸 만하다.
임시 테이블(GTT)에 번호를 넣고 조인해도 문장은 하나다. 대신 INSERT와 SELECT로 호출이 둘이 되고, 임시 테이블의 건수 추정을 따로 챙겨야 한다.
ORA-01795는 IN 목록의 식 개수 제한(1,000개)이라 바인드 변수로 써도 똑같이 걸린다. 컬렉션에는 이 제한이 없어서 화면의 1,000개 제한도 풀 수 있다.
컬렉션 문장은 계획이 하나라 3개를 조회하든 1,000개를 조회하든 NL 조인으로 간다. 1,000개라면 T_ORD를 FULL로 읽는 쪽(896블록)이 조금 더 싸지만, 대부분의 요청이 수십 건 이하라면 NL 하나로 고정하는 편이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황