조건 네 칸짜리 주문 검색 하나가 공유 풀에 하루 2만 개 넘는 문장을 남긴다

중급PLSQL한빛유통685 / 700

상황

쇼핑몰 관리 화면의 「주문 검색」은 고객번호, 주문 상태, 주문일 시작과 끝 네 칸 중 채운 칸만 조건으로 쓴다. 프로시저 P_ORD_SEARCH가 조건을 이어 붙여 동적 SQL을 만들고 결과를 SYS_REFCURSOR로 돌려준다. 작년에 바인드 변수로 바꾸려다 "채운 칸마다 바인드 개수가 달라 USING을 쓸 수 없다"(ORA-01008)며 되돌렸다. DBA는 이 프로시저의 문장이 하루 2만 개 넘게 하드 파싱되어 공유 풀을 밀어낸다고 한다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음, 2023 ~ 2024, 하루 약 137건) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2% · 고객마다 50건
인덱스: PK_T_ORD (ORD_SEQ) · IX_T_ORD_CUS_DT (CUS_ID, ORD_DT) · IX_T_ORD_DT (ORD_DT) · 통계 최신
화면은 적어도 한 칸은 채워야 검색할 수 있다 · CURSOR_SHARING = EXACT

원본 SQL

sql

PROCEDURE P_ORD_SEARCH(p_cus_id IN VARCHAR2, p_ord_st IN VARCHAR2,
                       p_dt_fr  IN DATE,     p_dt_to  IN DATE,
                       o_rc     OUT SYS_REFCURSOR) IS
  v_sql VARCHAR2(4000) := 'SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD WHERE 1 = 1';
BEGIN
  IF p_cus_id IS NOT NULL THEN
    v_sql := v_sql || ' AND CUS_ID = ''' || p_cus_id || '''';
  END IF;
  IF p_ord_st IS NOT NULL THEN
    v_sql := v_sql || ' AND ORD_ST = ''' || p_ord_st || '''';
  END IF;
  IF p_dt_fr IS NOT NULL THEN
    v_sql := v_sql || ' AND ORD_DT >= TO_DATE(''' || TO_CHAR(p_dt_fr, 'YYYYMMDD') || ''', ''YYYYMMDD'')';
  END IF;
  IF p_dt_to IS NOT NULL THEN
    v_sql := v_sql || ' AND ORD_DT < TO_DATE(''' || TO_CHAR(p_dt_to, 'YYYYMMDD') || ''', ''YYYYMMDD'') + 1';
  END IF;
  OPEN o_rc FOR v_sql || ' ORDER BY ORD_DT DESC';
END;

실행계획 학습용 예시

xplan

[V$SQLAREA: P_ORD_SEARCH가 만든 문장 · 하루 (FORCE_MATCHING_SIGNATURE로 묶음)]
  채운 칸                       문장 수     실행 수    하드 파싱
  고객                            6,912       9,840       6,912
  고객 + 기간                     8,105       8,330       8,105
  상태 + 기간                     3,288       3,420       3,288
  기간                            2,617       2,790       2,617
  그 밖의 조합 다섯 가지            940         990         940
  합계 (아홉 가지 조합)          21,862      25,370      21,862

[세션 통계 · 하루 합]  parse count (hard) 21,862 · parse time cpu 1,940 cs

[한 번의 실행 · 고객만 채움]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |     50 |00:00:00.01 |      53 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_ORD           |      1 |     50 |     50 |00:00:00.01 |      53 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_T_ORD_CUS_DT |      1 |     50 |     50 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------
   2 - access("CUS_ID"='CUS_000123')

문제

문장이 하루 2만 개 넘게 생기는 이유와, 작년에 USING으로 바꾸다 실패한 이유를 설명하시오.
채운 칸만 조건으로 쓰는 구조는 그대로 두고 모든 값을 바인드 변수로 넘기도록 고치시오. 고친 뒤 문장이 최대 몇 개가 되는지 쓰시오.
동적 SQL 대신 (:v IS NULL OR 컬럼 = :v) 모양의 정적 SQL 하나로 쓰는 안과 견주어 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_ORD_SEARCH(p_cus_id IN VARCHAR2, p_ord_st IN VARCHAR2,
                       p_dt_fr  IN DATE,     p_dt_to  IN DATE,
                       o_rc     OUT SYS_REFCURSOR) IS
  v_sql VARCHAR2(4000) := 'SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD WHERE 1 = 1';
BEGIN
  -- 채운 칸은 조건으로, 빈 칸은 '늘 참' 인 자리로: 자리는 언제나 네 개, 순서도 같다
  IF p_cus_id IS NOT NULL THEN v_sql := v_sql || ' AND CUS_ID = :cus';
  ELSE                          v_sql := v_sql || ' AND (1 = 1 OR :cus IS NULL)';
  END IF;
  IF p_ord_st IS NOT NULL THEN v_sql := v_sql || ' AND ORD_ST = :st';
  ELSE                          v_sql := v_sql || ' AND (1 = 1 OR :st IS NULL)';
  END IF;
  IF p_dt_fr IS NOT NULL THEN  v_sql := v_sql || ' AND ORD_DT >= :dt_fr';
  ELSE                          v_sql := v_sql || ' AND (1 = 1 OR :dt_fr IS NULL)';
  END IF;
  IF p_dt_to IS NOT NULL THEN  v_sql := v_sql || ' AND ORD_DT < :dt_to + 1';
  ELSE                          v_sql := v_sql || ' AND (1 = 1 OR :dt_to IS NULL)';
  END IF;

  -- 값은 모두 바인드 (원래 SQL 처럼 날짜는 0시로 자른다)
  OPEN o_rc FOR v_sql || ' ORDER BY ORD_DT DESC'
    USING p_cus_id, p_ord_st, TRUNC(p_dt_fr), TRUNC(p_dt_to);
END;

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

xplan

[고친 뒤 · 하루]
  채운 칸              문장 수    실행 수   하드 파싱
  아홉 가지 조합             9     25,370           9      (최대 2 × 2 × 2 × 2 = 16)
  parse time cpu  1,940 cs → 1 cs 남짓

[고객만 채운 검색: 늘 참인 자리는 옵티마이저가 지운다]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |     50 |00:00:00.01 |      53 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_ORD           |      1 |     50 |     50 |00:00:00.01 |      53 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_T_ORD_CUS_DT |      1 |     50 |     50 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------
   2 - access("CUS_ID"=:CUS)

[기간만 채운 검색 (하루)]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |    137 |00:00:00.01 |     139 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_ORD           |      1 |    137 |    137 |00:00:00.01 |     139 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_T_ORD_DT     |      1 |    137 |    137 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT"<:DT_TO+1 AND "ORD_DT">=:DT_FR)

[비교: 정적 SQL 하나 (:v IS NULL OR 컬럼 = :v)]  문장 1 · 계획 1 · 고객만 채운 검색도 T_ORD FULL 896블록

모범 답안 서술

문장이 2만 개 넘게 생기는 것은 값까지 문자열로 이어 붙였기 때문이다. 고객번호와 상태, 날짜가 문장 텍스트에 박혀서 검색할 때마다 처음 보는 문장이 되고, 그때마다 하드 파싱을 한다(하루 21,862개, parse time cpu 1,940cs). 이어 붙인 값으로는 SQL 주입도 열려 있었다. 작년에 실패한 이유는 USING의 동작에 있다. USING은 문장 안의 바인드 자리에 값을 순서대로 넣으므로, 자리 수와 값의 수가 같아야 한다. 채운 칸만 조건으로 붙이면 자리 수가 검색마다 달라지는데 USING 목록은 코드에 고정되어 있으니 ORA-01008이 났다. 고친 판은 빈 칸에도 자리를 남긴다. 채운 칸은 CUS_ID = :cus처럼 조건으로 쓰고, 빈 칸은 (1 = 1 OR :cus IS NULL)처럼 늘 참인 조건으로 쓴다. 자리는 언제나 네 개이고 순서도 같으므로 USING p_cus_id, p_ord_st, TRUNC(p_dt_fr), TRUNC(p_dt_to) 한 줄로 넘길 수 있다. 늘 참인 조건은 옵티마이저가 파싱할 때 지우므로 일을 하지 않는다(Predicate Information에 CUS_ID만 남는다). 문장은 값마다가 아니라 채운 칸의 조합만큼만 생긴다. 칸이 네 개라 최대 16개이고, 실제로 쓰는 조합은 9가지라 문장도 9개다. 하드 파싱은 21,862 → 9가 된다. 동적 SQL을 남기는 이유는 조합마다 맞는 계획이 다르기 때문이다. 고객만 채운 검색은 IX_T_ORD_CUS_DT를, 기간만 채운 검색은 IX_T_ORD_DT를 쓴다. (:cus IS NULL OR CUS_ID = :cus) AND … 모양의 정적 SQL 하나로 쓰면 문장은 하나지만 계획도 하나라서, 어떤 칸이 채워질지 모르는 채로 모든 조합에 맞는 계획을 골라야 한다. 그 결과 고객만 채운 검색까지 FULL 896블록을 읽는다. 조합마다 계획이 달라야 하는 검색 화면은 동적 SQL과 바인드를 함께 쓰는 쪽이 맞다.

해설

늘 참인 자리는 일을 하지 않는다: (1 = 1 OR :cus IS NULL)은 파싱할 때 참으로 정리되어 실행계획에 나오지 않는다. 자리 수만 맞춰 주는 장치다. 바인드 이름이 같아도 동적 SQL의 USING은 위치로 넣으므로 순서를 지킨다.
DBMS_SQL: 사용자가 조건 칸을 늘리는 화면처럼 자리 수를 정말로 정할 수 없으면 DBMS_SQL.PARSE와 BIND_VARIABLE로 이름을 붙여 묶고, DBMS_SQL.TO_REFCURSOR로 REF CURSOR로 바꿔 돌려준다. 코드는 길어진다(교재 11화 12절).
값마다 계획이 달라야 하는 칸: 상태는 COMPLETE 90%, CANCEL 2%로 치우쳐 있다. 바인드로 바꾸면 첫 값으로 계획이 정해지므로, 상태에 따라 계획이 달라야 한다면 상태만 정해진 코드값 리터럴로 남기는 방법도 있다. 값이 세 가지라 문장도 세 배일 뿐이다(PARSE-002).
날짜 조건은 원래 SQL과 같게 0시로 자른 값(TRUNC)을 넘기고, 상한은 < 다음 날로 둔다. 화면이 시각까지 넘기더라도 결과가 바뀌지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황