공통 조회 엔진이 오전 DB CPU의 41%를 쓰는데, 엔진이 실행하는 조회는 모두 몇 블록짜리다

실전PLSQL유통590 / 700

상황

유통 본사 레거시에는 2009년에 만든 「공통 조회 엔진」 PKG_QRY.RUN이 있다. 화면은 조회 번호와 파라미터 문자열을 넘기고, 엔진은 SQL 저장소 테이블 T_QRY_REPO에서 문장을 꺼내 DBMS_SQL로 파싱하고, 파라미터를 이름으로 바인드해 실행한 뒤 REF CURSOR로 돌려준다. SQL을 한곳에서 관리하고 감사하려고 만든 구조라 화면 40여 개가 이 엔진만 쓴다. 오전 10 ~ 11시 DB CPU의 41%가 이 엔진에서 나온다. 그런데 엔진이 실행하는 조회는 대부분 PK로 몇 블록을 읽는 문장이다. 개발팀은 엔진을 없애자고 하고 감사 담당은 지키자고 한다. 박팀장이 튜닝 쪽 의견을 숫자로 달라고 했다.

테이블과 인덱스

T_QRY_REPO SQL 저장소(이 문제에서 쓰는 표): 312행 · QRY_ID PK · SQL_TXT · USE_YN · 감사 담당만 고칠 수 있다
호출 상위 6개 조회가 전체 호출의 92%다. Q017 매장 재고 한 건(CF18 PK, 2블록) · Q021 상품 현재가(CF05 PK, 3블록) · Q030 영수증 머리(CF10_PK, 4블록) · Q031 영수증 상품(CF11_PK, 1 ~ 4행) · Q044 상품 한 건(CF01 PK) · Q052 발주 진행 상태(CF12 PK)
화면 서버(Java)는 요청마다 {call PKG_QRY.RUN(?, ?, ?)}를 준비하고 닫는다(문장 캐시 없음). session_cached_cursors = 50 · DB 서버는 4코어

원본 SQL

sql

-- PKG_QRY.RUN (2009) : 저장소의 SQL을 꺼내 부를 때마다 파싱하고 실행해 REF CURSOR로 돌려준다
PROCEDURE RUN(p_qry_id IN VARCHAR2, p_params IN VARCHAR2, rc OUT SYS_REFCURSOR) IS
  v_sql VARCHAR2(4000);
  c     INTEGER;
  n     INTEGER;
BEGIN
  SELECT SQL_TXT INTO v_sql FROM T_QRY_REPO WHERE QRY_ID = p_qry_id AND USE_YN = 'Y';

  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, v_sql, DBMS_SQL.NATIVE);
  FOR p IN (SELECT REGEXP_SUBSTR(p_params, '[^;]+', 1, LEVEL) AS kv      -- 'shp=3;itm=1042;dt=20230601'
              FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(p_params, ';') + 1) LOOP
    DBMS_SQL.BIND_VARIABLE(c, ':' || SUBSTR(p.kv, 1, INSTR(p.kv, '=') - 1),
                              SUBSTR(p.kv, INSTR(p.kv, '=') + 1));
  END LOOP;
  n  := DBMS_SQL.EXECUTE(c);
  rc := DBMS_SQL.TO_REFCURSOR(c);
END;

실행계획 학습용 예시

xplan

[10 ~ 11시 · V$SYSSTAT 증가분 (인스턴스)]
  PKG_QRY.RUN 호출                    1,094,400
  execute count                       4,391,200   (호출당 4: 호출 블록 · 저장소 SELECT · 파라미터 분해 · 대상 SQL)
  parse count (total)                 2,190,300   (호출당 2: 호출 블록 · DBMS_SQL.PARSE)
  parse count (hard)                        118
  session cursor cache hits           2,104,900
  DB CPU                                1,980 초   (그중 PKG_QRY 계열 810초 · 41%)

[V$SQL · Q017 대상 SQL]
  SELECT INV_QTY FROM CF18 WHERE SHP_NO = :shp AND ITM_NO = :itm AND INV_BS_DT = :dt
  PARSE_CALLS 367,210 · EXECUTIONS 367,210 · 한 번 BUFFER_GETS 2 · 한 번 CPU 0.03 ms

[ASH · 10 ~ 11시 · 화면 서버 세션]
  CPU 74% · library cache: mutex X 9% · cursor: pin S 5%

[DBMS_HPROF · PKG_QRY.RUN 한 번 (Q017) · 평균]
  DBMS_SQL.PARSE                          0.21 ms
  파라미터 분해 (REGEXP + CONNECT BY)       0.18 ms
  그 밖의 PL/SQL                           0.12 ms
  DBMS_SQL.EXECUTE                        0.06 ms   (대상 SQL이 블록을 읽는 몫 0.03 ms 포함)
  DBMS_SQL.BIND_VARIABLE × 3              0.05 ms
  저장소 SELECT                            0.04 ms
  DBMS_SQL.TO_REFCURSOR                   0.03 ms
  합계                                    0.69 ms   (+ 호출 블록의 파싱과 실행 약 0.05 ms)

문제

2블록짜리 조회 하나에 DB CPU가 0.7ms 넘게 드는 이유를 위 숫자로 설명하시오. 파스 호출이 엔진 호출의 두 배인 이유도 쓰시오.
감사 요구(SQL을 한곳에서 관리)를 지키면서 파스 호출과 CPU를 줄일 방법을 정하고 SQL로 쓰시오. 대안 셋(① 엔진이 파싱한 커서를 세션에 보관 ② 상위 조회를 정적 SQL 패키지로 ③ 화면이 SQL을 직접 실행)을 견주시오.
고른 안의 대가와, 엔진을 통째로 없애자는 개발팀 안을 평가하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 호출 상위 6개(전체 호출의 92%)만 정적 SQL 패키지로 옮긴다 · 나머지 306개 조회는 엔진 그대로
CREATE OR REPLACE PACKAGE PKG_QRY_HOT AS          -- 패키지 변수는 두지 않는다 (상태가 없어야 재컴파일 때 ORA-04068이 없다)
  PROCEDURE INV_QTY  (p_shp IN NUMBER, p_itm IN NUMBER, p_dt IN VARCHAR2, o_qty OUT NUMBER);      -- Q017
  PROCEDURE CUR_UPRC (p_itm IN NUMBER, p_kn IN VARCHAR2, p_dt IN VARCHAR2, o_uprc OUT NUMBER);    -- Q021
  PROCEDURE RCPT_ITEMS(p_sle_no IN VARCHAR2, rc OUT SYS_REFCURSOR);                              -- Q031 (여러 행)
  -- Q030 · Q044 · Q052 도 같은 모양
END PKG_QRY_HOT;
/
CREATE OR REPLACE PACKAGE BODY PKG_QRY_HOT AS
  PROCEDURE INV_QTY(p_shp IN NUMBER, p_itm IN NUMBER, p_dt IN VARCHAR2, o_qty OUT NUMBER) IS
  BEGIN
    SELECT INV_QTY INTO o_qty                       -- 정적 SQL : 변수는 바인드로 · 커서는 PL/SQL이 캐시
      FROM CF18
     WHERE SHP_NO = p_shp AND ITM_NO = p_itm AND INV_BS_DT = p_dt;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN o_qty := NULL;          -- 엔진도 0행이면 빈 결과였다
  END;

  PROCEDURE CUR_UPRC(p_itm IN NUMBER, p_kn IN VARCHAR2, p_dt IN VARCHAR2, o_uprc OUT NUMBER) IS
  BEGIN
    SELECT ITM_UPRC INTO o_uprc
      FROM (SELECT ITM_UPRC FROM CF05
             WHERE ITM_NO = p_itm AND UPRC_KN_CD = p_kn AND APL_DT <= p_dt
             ORDER BY APL_DT DESC)
     WHERE ROWNUM = 1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN o_uprc := NULL;
  END;

  PROCEDURE RCPT_ITEMS(p_sle_no IN VARCHAR2, rc OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN rc FOR SELECT ITM_NO, SLE_QTY, SLE_UPRC FROM CF11 WHERE SLE_NO = p_sle_no ORDER BY ITM_NO;
  END;
END PKG_QRY_HOT;
/
-- 저장소의 6행에는 '정적 패키지 PKG_QRY_HOT로 옮김 (배포 번호)'을 비고로 남긴다 · 감사 대상 목록은 그대로

// 화면 서버 : 문장 캐시를 켜서 호출 블록의 파스 호출도 없앤다
props.setProperty("oracle.jdbc.implicitStatementCacheSize", "30");
CallableStatement cs = conn.prepareCall("{call PKG_QRY_HOT.INV_QTY(?, ?, ?, ?)}");

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

xplan

[10 ~ 11시 · 옮긴 뒤 (학습용 예시)]
  상위 6개 호출 (1,006,800)        호출당 실행 2 (호출 블록 · 대상 SQL) · 파스 호출 0 (Q031만 REF CURSOR를 열 때 1)
  나머지 엔진 호출 (87,600)         그대로 · 호출당 파스 2
  parse count (total)               2,190,300 → 약 34만
  execute count                     4,391,200 → 약 236만
  DB CPU (엔진과 옮긴 조회 몫)        810 초 → 약 150 초   (Q017 한 번 0.74 ms → 0.08 ms)
  ASH library cache: mutex X        9% → 1%

[의존 관계 · USER_DEPENDENCIES]
  PKG_QRY_HOT → CF18, CF05, CF10, CF11, CF01, CF12   (테이블을 바꾸면 어느 화면 조회가 걸리는지 바로 보인다)

모범 답안 서술

어디에 쓰나: HPROF를 보면 Q017 한 번에 0.69ms를 쓰는데, 대상 SQL이 블록 2개를 읽는 몫은 0.03ms뿐이다. 나머지는 엔진의 일이다. 호출마다 저장소에서 문장을 꺼내고(0.04ms), DBMS_SQL.PARSE로 파싱하고(0.21ms), 파라미터 문자열을 정규식과 CONNECT BY로 쪼개고(0.18ms), 이름으로 바인드한 뒤 실행해 REF CURSOR로 바꾼다. 파스 호출은 엔진 호출의 두 배(2,190,300)다. 엔진이 부를 때마다 DBMS_SQL.PARSE를 하고, 화면 서버도 요청마다 호출 블록 {call PKG_QRY.RUN…}을 준비하고 닫으므로 그것도 매번 파싱된다. 세션 커서 캐시가 대부분을 받아 주지만(2,104,900) 파스 호출 자체는 남고, 같은 문장을 여러 세션이 동시에 찾느라 library cache: mutex X가 9%다. 하드 파싱은 118번뿐이라 리터럴 문제는 아니다. 왜 지금 구조로는 못 줄이나: 파싱을 한 번만 하려면 파싱한 커서를 두고 값만 바꿔 다시 실행해야 한다. 그런데 엔진은 결과를 넘기려고 DBMS_SQL.TO_REFCURSOR를 부르고, 이렇게 넘긴 커서는 더는 DBMS_SQL로 다시 쓸 수 없다. 그래서 부를 때마다 새로 열고 파싱할 수밖에 없다. 대안 비교: ① 엔진이 조회 번호별로 파싱한 커서를 패키지 변수에 보관하는 안은 REF CURSOR를 포기하고 결과를 DBMS_SQL.COLUMN_VALUE로 꺼내 넘기도록 엔진과 화면 40개를 모두 고쳐야 한다. 세션마다 커서가 최대 312개까지 열려 open_cursors와 메모리를 쓰고, 패키지에 상태가 생겨 누가 엔진을 재컴파일하면 모든 세션이 ORA-04068을 만난다. ③ 화면이 SQL을 직접 실행하면 문장 캐시로 파스 호출이 거의 0이 되지만, SQL이 저장소 밖 자바 소스로 흩어져 감사 요구를 정면으로 어긴다. ② 상위 조회만 정적 SQL 패키지로 옮기는 안이 둘 사이다. 고른 안: ②를 고른다. 호출의 92%가 6개 조회에 몰려 있으므로 312개를 다 바꿀 까닭이 없다. 6개만 PKG_QRY_HOT의 정적 SQL로 옮기고 나머지 306개는 엔진에 둔다. 정적 SQL은 PL/SQL이 변수를 바인드로 바꾸고 커서를 캐시하므로, 두 번째 호출부터는 파스 호출 없이 실행만 한다. 화면 서버는 문장 캐시를 켜서 호출 블록의 파스 호출도 없앤다. Q017 한 번의 CPU는 0.74 → 0.08ms, 오전 파스 호출은 219만 → 약 34만, 엔진과 옮긴 조회의 CPU는 810 → 약 150초가 된다. 감사 요구와 대가: 옮긴 6개는 패키지 소스로 관리되고 배포 기록이 남는다. 정적 SQL은 컴파일할 때 컬럼과 테이블이 검사되고 USER_DEPENDENCIES에 의존 관계가 남아, 테이블을 바꾸면 어느 조회가 걸리는지 저장소보다 더 정확히 보인다. 저장소의 6행에는 옮겼다는 비고를 남겨 감사 목록은 그대로 둔다. 대가는 상위 조회를 고칠 때 저장소 한 줄이 아니라 패키지 배포가 필요하다는 점과, 화면 6곳의 호출 방식을 바꿔야 한다는 점이다. 개발팀의 '엔진을 통째로 없애자'는 안은 312개를 모두 옮기는 일인데, 나머지 8%의 호출을 위해 그만큼 바꿀 값어치가 없고 감사 요구와도 부딪힌다.

해설

동적 SQL의 값: 이 엔진이 비싼 것은 동적 SQL이라서가 아니라 부를 때마다 파싱하고 바인드를 문자열로 풀기 때문이다. 같은 동적 SQL이라도 커서를 보관해 다시 실행하면 파스 호출은 한 번이다(교재 11화 §2의 ④).
패키지에 변수를 두지 않는 것이 중요하다. 상수 하나로도 패키지 상태가 생겨 재컴파일 때 그 세션의 상태가 모두 버려진다(교재 15화 §6).
화면 서버의 문장 캐시는 연결마다 따로 있다. 캐시 크기는 한 연결이 되풀이해 쓰는 호출 블록 수보다 크게 잡는다(PARSE-015).
바인드 값을 문자열로만 넘기는 엔진은 NUMBER 컬럼에도 VARCHAR2 바인드를 건다. 이번 조회들은 바인드 쪽이 바뀌어 인덱스를 탔지만, 문자 컬럼에 숫자 바인드를 거는 반대 경우는 컬럼이 가공된다(IDX 단원 묵시적 형변환).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황