매장 여섯 곳의 일 마감 프로시저가 판매 50만 건을 열두 번 읽고, 마감일은 엉뚱한 모양으로 저장된다

실전PLSQL유통26 / 700

상황

유통 본사의 「일 마감」 프로시저는 매일 새벽 매장마다 전날 판매 건수와 금액을 세어 마감 테이블에 넣는다. 2019년에 작성됐고, 변수 타입은 손으로 적었다. 올해 스케줄러 서버를 바꾼 뒤로 본사 리포트에 지난달 마감이 하루도 나오지 않는다. 리포트는 CLOSE_DT LIKE '202311%'로 마감을 찾는다. 새 스케줄러 서버의 세션은 NLS_LANG=KOREAN_KOREA라서 NLS_DATE_FORMAT이 RR/MM/DD다. 오과장은 원인을 찾는 김에 프로시저의 TKPROF도 떠 왔다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,511) · SLE_DT VARCHAR2(8) · SHP_NO NUMBER(5) · 하루 약 700건(매장마다 약 140건)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO)
CF07 매장: 6행(판매가 있는 매장은 5곳) · SHP_NO NUMBER(5)
T_SHP_DAY_CLOSE 일 마감(이 문제에서 쓰는 파생 테이블): CLOSE_DT VARCHAR2(8) ('YYYYMMDD') · SHP_NO NUMBER(5) · SLE_CNT · SLE_AMT · 하루 6행
인덱스 IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CF 9,921 · 통계 최신

원본 SQL

sql

PROCEDURE P_DAY_CLOSE(p_base_dt IN DATE) IS          -- 스케줄러가 TRUNC(SYSDATE) - 1을 넘긴다
  v_ymd  NUMBER(8) := TO_NUMBER(TO_CHAR(p_base_dt, 'YYYYMMDD'));
  v_cnt  NUMBER;
  v_amt  NUMBER;
BEGIN
  FOR s IN (SELECT TO_CHAR(SHP_NO) AS shp_no FROM CF07) LOOP
    -- ① 매장의 그날 판매 건수
    SELECT COUNT(*) INTO v_cnt
      FROM CF10
     WHERE SLE_DT = v_ymd
       AND SHP_NO = s.shp_no;

    -- ② 매장의 그날 판매 금액
    SELECT NVL(SUM(b.SLE_QTY * b.SLE_UPRC), 0) INTO v_amt
      FROM CF10 a, CF11 b
     WHERE a.SLE_DT = v_ymd
       AND a.SHP_NO = s.shp_no
       AND b.SLE_NO = a.SLE_NO;

    -- ③ 마감 저장
    INSERT INTO T_SHP_DAY_CLOSE (CLOSE_DT, SHP_NO, SLE_CNT, SLE_AMT)
    VALUES (p_base_dt, s.shp_no, v_cnt, v_amt);
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 · CF10 규모는 교재 03화 실측

xplan

[TKPROF 요약 · P_DAY_CLOSE(DATE '2023-11-30') 한 번]
① SELECT COUNT(*) FROM CF10 WHERE SLE_DT = :B2 AND SHP_NO = :B1
     Execute 6 · Fetch 6 · query 27,066 · rows 6
② SELECT NVL(SUM(B.SLE_QTY * B.SLE_UPRC), 0) FROM CF10 A, CF11 B WHERE A.SLE_DT = :B2 AND A.SHP_NO = :B1 AND B.SLE_NO = A.SLE_NO
     Execute 6 · Fetch 6 · query 29,886 · rows 6
③ INSERT INTO T_SHP_DAY_CLOSE (CLOSE_DT, SHP_NO, SLE_CNT, SLE_AMT) VALUES (:B4, :B3, :B2, :B1)
     Execute 6 · query 0 · current 18 · rows 6

[① 1번 매장 실행 때 · DBMS_XPLAN.DISPLAY_CURSOR]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.03 |    4511 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |    4511 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |   1000 |    141 |00:00:00.03 |    4511 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SHP_NO"=TO_NUMBER(:B1) AND TO_NUMBER("SLE_DT")=:B2))

[마감 테이블 · 새 서버에서 돈 2023-11-30분]
CLOSE_DT  SHP_NO  SLE_CNT     SLE_AMT
--------  ------  -------  ----------
23/11/30       1      141   1,826,500
23/11/30       2      139   1,794,000
...

문제

①, ②, ③에서 일어나는 묵시적 형변환을 모두 찾아, 각각 컬럼 쪽에서 일어나는지 변수 쪽에서 일어나는지, 그래서 성능이나 데이터에 무슨 일이 생기는지 설명하시오.
같은 결과를 내면서 컬럼 쪽에서 형변환이 일어나지 않도록 프로시저를 고치시오.
같은 실수를 컴파일 단계에서 잡는 방법과, 이미 잘못 저장된 마감 행의 처리를 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_DAY_CLOSE(p_base_dt IN DATE) IS
  v_ymd  CF10.SLE_DT%TYPE := TO_CHAR(p_base_dt, 'YYYYMMDD');   -- 컬럼 타입 그대로 · 포맷은 밝힌다
  v_cnt  NUMBER;
  v_amt  NUMBER;
BEGIN
  FOR s IN (SELECT SHP_NO FROM CF07) LOOP                      -- 매장 번호도 숫자 그대로
    SELECT COUNT(*) INTO v_cnt
      FROM CF10
     WHERE SLE_DT = v_ymd
       AND SHP_NO = s.SHP_NO;

    SELECT NVL(SUM(b.SLE_QTY * b.SLE_UPRC), 0) INTO v_amt
      FROM CF10 a, CF11 b
     WHERE a.SLE_DT = v_ymd
       AND a.SHP_NO = s.SHP_NO
       AND b.SLE_NO = a.SLE_NO;

    INSERT INTO T_SHP_DAY_CLOSE (CLOSE_DT, SHP_NO, SLE_CNT, SLE_AMT)
    VALUES (v_ymd, s.SHP_NO, v_cnt, v_amt);                     -- 'YYYYMMDD' 문자열로 저장
  END LOOP;
  COMMIT;
END;

-- 컴파일할 때 잡는다 : 컬럼 타입과 다른 변수로 비교하면 PLW-07204 경고
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:PERFORMANCE';
ALTER PROCEDURE P_DAY_CLOSE COMPILE;

-- 잘못 저장된 마감 행 : 'RR/MM/DD' 모양만 골라 바로잡는다 (건수를 먼저 세어 본다)
UPDATE T_SHP_DAY_CLOSE
   SET CLOSE_DT = TO_CHAR(TO_DATE(CLOSE_DT, 'RR/MM/DD'), 'YYYYMMDD')
 WHERE CLOSE_DT LIKE '__/__/__';

개선 후 실행계획 학습용 예시 · CF10 규모는 교재 03화 실측

xplan

[① 1번 매장 · 고친 뒤]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      1 |00:00:00.01 |      19 |
|   1 |  SORT AGGREGATE                      |                |      1 |      1 |      1 |00:00:00.01 |      19 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    137 |    141 |00:00:00.01 |      19 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    685 |    706 |00:00:00.01 |       5 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SHP_NO"=:B1)
   3 - access("SLE_DT"=:B2)

[TKPROF 요약 · 고친 뒤]
① query 114 (6번 × 19) · ② query 2,934 · ③ current 18  →  SELECT 일량 56,952 → 3,048

모범 답안 서술

형변환은 네 군데에서 일어난다. PL/SQL 변수는 SQL 안에서 바인드 변수가 되므로 변수의 타입이 곧 바인드의 타입이다. ①과 ②의 SHP_NO = s.shp_no는 숫자 컬럼과 문자 변수의 비교다. 오라클은 문자 쪽을 숫자로 바꾸는데 문자 쪽은 변수이므로 "SHP_NO"=TO_NUMBER(:B1)이 된다. 컬럼은 그대로라 괜찮다. ①과 ②의 SLE_DT = v_ymd는 문자 컬럼과 NUMBER 변수의 비교다. 이번에는 문자 쪽이 컬럼이라 TO_NUMBER("SLE_DT")=:B2가 되어 IX_CF10_SLE_DT를 쓰지 못한다. 그래서 매장 여섯 곳마다 ①과 ②가 판매 50만 건을 한 번씩, 모두 12번 다 읽는다(query 27,066 + 29,886 = 56,952). ③은 DATE 값 p_base_dt를 VARCHAR2(8) 컬럼에 넣는다. 오라클은 세션의 NLS_DATE_FORMAT으로 문자열을 만들므로 새 서버(RR/MM/DD)에서는 '23/11/30'이 저장된다. 여덟 글자라 에러도 나지 않는다. 리포트의 CLOSE_DT LIKE '202311%'에 걸리지 않으니 지난달 마감이 하나도 안 나온다. 예전 서버는 NLS 형식이 YYYYMMDD여서 우연히 맞았을 뿐이다. 고치는 방법은 변수를 컬럼 타입으로 선언하는 것이다. v_ymd를 CF10.SLE_DT%TYPE으로 선언하고 TO_CHAR(p_base_dt, 'YYYYMMDD')로 포맷을 밝혀 채운다. 커서도 SHP_NO를 숫자 그대로 가져온다. 그러면 ①은 access("SLE_DT"=:B2)로 그날 706건만 읽고(4,511 → 19), SELECT 일량은 56,952 → 3,048블록으로 준다. ③에도 v_ymd를 넣어 마감일이 늘 '20231130' 모양으로 저장된다. 같은 실수는 컴파일할 때 잡을 수 있다. PLSQL_WARNINGS = 'ENABLE:PERFORMANCE'로 다시 컴파일하면 컬럼 타입과 다른 변수로 비교하는 줄마다 PLW-07204 경고가 나온다. 이미 'RR/MM/DD' 모양으로 저장된 마감 행은 모양으로 골라 'YYYYMMDD'로 바로잡고, 그 전에 건수를 세어 리포트 담당자와 맞춰 본다.

해설

변수 쪽 변환과 컬럼 쪽 변환: 같은 프로시저 안에서도 SHP_NO는 괜찮고 SLE_DT는 망가진다. 둘 다 "문자와 숫자 비교"지만 문자 쪽이 변수냐 컬럼이냐가 다르다. 점검할 때는 변환이 붙은 자리를 Predicate Information에서 본다.
%TYPE이 규칙인 이유: 컬럼 타입이 바뀌면 변수도 따라 바뀐다. 손으로 적은 NUMBER(8)은 "날짜 숫자"라는 뜻으로는 맞아 보여도, 비교할 때마다 컬럼 쪽이 바뀌는 원인이 된다.
③은 성능 문제가 아니라 데이터 문제다. DATE를 문자 컬럼에 넣거나 문자를 DATE와 비교하는 코드는 세션 NLS에 따라 결과가 바뀐다. 서버를 옮길 때 NLS를 예전과 맞추는 것은 임시 처방이고, 코드에서 포맷을 밝히는 것이 답이다.
루프를 없애고 INSERT … SELECT … GROUP BY SHP_NO 한 문장으로 바꾸면 판매를 하루치 한 번만 읽는다. 반복 처리를 한 문장으로 바꾸는 일은 DML 단원의 주제다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · CF10 규모는 교재 03화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · CF10 규모는 교재 03화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황