코드 정리 배포 다음 날부터 고객센터 화면의 「취소 급증」 칸만 한 박자 늦게 뜬다

모범 답안 SQL ​

-- PKG_STLM_STAT.status_sum : 인자와 CASE는 그대로 · 분기 안의 SQL에는 리터럴을 쓴다
--   PL/SQL 정적 SQL은 변수와 상수를 모두 바인드(:B1)로 바꾼다. SQL 텍스트에 값이 남는 것은 리터럴뿐이다.
--   상태값마다 맞는 계획이 다르다(취소는 인덱스, 미결과 완료는 FULL). 상수로 바꾸지 않는다. (정적 분석 예외 등록)
PROCEDURE status_sum(p_st IN VARCHAR2, o_cnt OUT NUMBER, o_amt OUT NUMBER) IS
BEGIN
  CASE p_st                                   -- 상수는 PL/SQL 비교에만 쓴다 · ELSE가 없어 모르는 값은 CASE_NOT_FOUND
    WHEN PKG_CONST.c_st_cancel THEN
      SELECT COUNT(*), NVL(SUM(ORD_AMT), 0) INTO o_cnt, o_amt
        FROM T_ORD_BIG
       WHERE ORD_DT >= TRUNC(SYSDATE) - 30
         AND ORD_ST = 'CANCEL';
    WHEN PKG_CONST.c_st_pending THEN
      SELECT COUNT(*), NVL(SUM(ORD_AMT), 0) INTO o_cnt, o_amt
        FROM T_ORD_BIG
       WHERE ORD_DT >= TRUNC(SYSDATE) - 30
         AND ORD_ST = 'PENDING';
    WHEN PKG_CONST.c_st_complete THEN
      SELECT COUNT(*), NVL(SUM(ORD_AMT), 0) INTO o_cnt, o_amt
        FROM T_ORD_BIG
       WHERE ORD_DT >= TRUNC(SYSDATE) - 30
         AND ORD_ST = 'COMPLETE';
  END CASE;
END;

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

[고친 뒤 · 「취소 급증」 칸 · ORD_ST = 'CANCEL' · 자기 문장, 자기 계획]
Plan hash value: 1520374851

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |      1 |00:00:00.01 |     552 |
|   1 |  SORT AGGREGATE                      |                  |      1 |      1 |      1 |00:00:00.01 |     552 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |      1 |    548 |    548 |00:00:00.01 |     552 |
|*  3 |    INDEX RANGE SCAN                  | IX_ORD_BIG_ST_DT |      1 |    548 |    548 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_ST"='CANCEL' AND "ORD_DT">=TRUNC(SYSDATE@!)-30)

[다음 날 · V$SQL]
 ... AND ORD_ST = 'CANCEL'      20,000  INDEX RANGE SCAN (IX_ORD_BIG_ST_DT)     552
 ... AND ORD_ST = 'PENDING'      3,000  TABLE ACCESS FULL                     5,830
 ... AND ORD_ST = 'COMPLETE'     3,001  TABLE ACCESS FULL                     5,830
 「취소 급증」 칸 하루 약 1억 1,660만 → 1,104만 블록 · 하드 파싱은 01:00 뒤 세 번

모범 답안 서술 ​

PL/SQL 정적 SQL은 SQL 안에 쓴 PL/SQL 이름을 모두 바인드 변수로 바꿔 넘긴다. 변수와 인자뿐 아니라 상수(CONSTANT)도, 다른 패키지의 상수도 마찬가지다. SQL 텍스트에 값이 남는 것은 리터럴뿐이다. 그래서 ORD_ST = PKG_CONST.c_st_cancel은 ORD_ST = :B1이 되었고, 세 분기의 문장은 대문자와 바인드 이름까지 정리되어 글자 하나 다르지 않은 텍스트가 됐다. 분기는 코드에만 남고, 공유 풀에서는 SQL_ID 셋이 하나로 합쳐졌다(EXECUTIONS 26,001, 자식 하나). 매일 01:00 통계 수집이 커서를 무효화하고, 그 뒤 처음 오는 호출은 02:00 일 마감 배치의 'COMPLETE'다. 자식 0은 그 값을 엿보고(Peeked Binds) 24,651건을 읽는 일로 보아 FULL 계획을 세웠다(E-Rows 24,651). 낮에 「취소 급증」 칸이 'CANCEL'을 넘겨도 같은 자식을 쓰므로 548건을 위해 5,830블록을 읽는다. 배포 전에는 'CANCEL' 문장이 따로 있어서 인덱스로 552블록이었다. 미결과 완료는 원래도 FULL이라 그대로이고 취소 칸만 느려졌다. 하루 2만 번이면 약 1,104만 블록이 1억 1,660만 블록으로 늘었다. 히스토그램이 있어서 IS_BIND_SENSITIVE = Y이지만, 세 값 모두 FULL로 5,830블록을 읽으니 일량이 같은 칸에 들어가 적응적 커서 공유(ACS)는 자식을 나누지 않는다(IS_BIND_AWARE = N). PL/SQL은 커서를 세션에 캐시해 두고 다시 실행만 하므로 파스 호출(PARSE_CALLS 91)도 적다. 세 안을 견주면 이렇다. 상태값마다 프로시저를 나누는 안은 효과는 같지만 부르는 쪽(화면 둘과 배치)을 모두 고쳐야 한다. 인자와 CASE를 그대로 두고 분기 안의 SQL에만 리터럴을 쓰는 안은 고칠 곳이 세 줄이고, 문장은 셋, 하드 파싱도 01:00 뒤 세 번뿐이다. 컴파일할 때 문장이 검사되고 테이블 의존성도 남으며, ELSE가 없는 CASE라 모르는 값은 CASE_NOT_FOUND로 막힌다. 허용 목록으로 검사한 값을 이어 붙이는 동적 SQL 안은 상태값이 늘어도 코드가 그대로라는 장점이 있지만, 컴파일 검사가 없고 허용 목록 검사가 빠지는 날 SQL 주입이 열리며 실행마다 파스 호출이 생긴다. 상태값은 셋이고 잘 바뀌지 않으므로 두 번째 안을 고른다. 상수는 CASE의 비교처럼 PL/SQL 안에서만 쓴다. 「취소 급증」 칸 한 번은 Buffers 5,830 → 552가 된다. 리터럴로 되돌린 이유는 주석으로 남기고, 정적 분석 도구에 이 SQL 조건들을 예외로 등록한다. 가이드 2조의 "상수로 쓴다"는 화면 입력을 이어 붙이지 말고 코드에서 문장을 고르라는 뜻이고, 값을 SQL 텍스트에 리터럴로 박으라는 말이다. PL/SQL의 CONSTANT는 그 상수가 아니라는 점을 조문에 덧붙여 적어 두어야 다음 코드 정리 때 같은 일이 생기지 않는다.

해설 ​

• 정적 SQL 안에 쓴 PL/SQL 이름은 무엇이든 바인드다. PLSQL_OPTIMIZE_LEVEL을 올려도 상수가 SQL 텍스트로 펼쳐지지 않는다. TKPROF나 V$SQL에서 :B1 같은 이름이 보이면 PL/SQL이 넘긴 값이다.
• 허용 목록 동적 SQL은 IF p_st NOT IN ('CANCEL', 'PENDING', 'COMPLETE') THEN RAISE_APPLICATION_ERROR(…); END IF; 뒤에 EXECUTE IMMEDIATE '… AND ORD_ST = ' || DBMS_ASSERT.ENQUOTE_LITERAL(p_st) INTO o_cnt, o_amt; 모양이 된다. 상태값이 수십 가지이고 자주 느는 코드라면 이쪽이 낫다. 허용 목록은 한 곳에서 관리한다.
• 바인드를 지키는 안도 있다. /*+ BIND_AWARE */는 처음부터 선택도마다 자식을 나누지만 어느 값이 어느 계획을 타는지가 문장 밖으로 숨는다(PARSE-002). 인덱스를 (ORD_ST, ORD_DT, ORD_AMT)로 만들면 세 값 모두 인덱스만 읽는 범위 스캔이 맞아(완료 30일도 리프 100여 블록) 바인드로 둬도 안전하지만, 100만 행 테이블에 인덱스가 하나 더 는다.
• 상수로 모으는 일 자체가 틀린 것은 아니다. 메시지, 로그, PL/SQL 안의 비교에는 상수가 맞다. 값마다 계획이 갈리는 코드값을 SQL 조건에 쓸 때만 예외다(교재 11화 SQL 코딩 가이드 2조와 5조).

관련 교재 ​

• 11화 · 바인드 피킹
• 11화 · ACS가 움직이는 때와 못 움직이는 때