정산 명세 파일 내려받기가 첫 줄을 받기까지 2초를 기다리고, 세션마다 PGA가 60MB를 넘는다

기초PLSQL한빛유통680 / 700

상황

정산팀의 파일 내려받기 도구는 SELECT … FROM TABLE(PKG_STLM_FILE.FN_LINES)로 은행 제출용 정산 명세 10만 줄을 받아 한 줄씩 파일에 쓴다. 줄의 모양(고정 길이, 자릿수, 검증 숫자)은 패키지의 PL/SQL 규칙이 만들고, 같은 패키지를 다른 제출 파일 세 개가 함께 쓴다. 내려받기를 누르면 2초 넘게 아무것도 오지 않다가 한꺼번에 들어온다. 월말에 네 명이 동시에 받던 날, DBA가 세션마다 PGA가 60MB를 넘었다고 알려 왔다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · STLM_SEQ · ORD_SEQ · CUS_ID · STLM_DT · STLM_AMT
SQL 타입 T_LINE_OBJ (STLM_SEQ NUMBER, LINE VARCHAR2(200))와 T_LINE_TAB(TABLE OF T_LINE_OBJ)
FN_FMT_LINE: 패키지 안의 PL/SQL 함수(SQL 없음) · 정산 한 건을 200자 한 줄로 만든다
파일의 줄 순서는 정해져 있지 않다(은행은 STLM_SEQ로 대조한다) · 내려받기 도구는 한 번에 500줄씩 가져온다

원본 SQL

sql

CREATE OR REPLACE PACKAGE BODY PKG_STLM_FILE IS
  FUNCTION FN_LINES RETURN T_LINE_TAB IS
    v_tab T_LINE_TAB := T_LINE_TAB();
  BEGIN
    FOR r IN (SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT
                FROM T_ORD_STLM) LOOP
      v_tab.EXTEND;
      v_tab(v_tab.COUNT) := T_LINE_OBJ(r.STLM_SEQ,
                              FN_FMT_LINE(r.STLM_SEQ, r.ORD_SEQ, r.CUS_ID, r.STLM_DT, r.STLM_AMT));
    END LOOP;
    RETURN v_tab;
  END;
  -- FN_FMT_LINE 생략
END;
/

-- 내려받기 도구
SELECT STLM_SEQ, LINE FROM TABLE(PKG_STLM_FILE.FN_LINES);

실행계획 학습용 예시

xplan

[내려받기 도구의 문장: DISPLAY_CURSOR (ALLSTATS LAST)]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |      1 |        |   100K |00:00:02.36 |       0 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| FN_LINES |      1 |   8168 |   100K |00:00:02.36 |       0 |
-------------------------------------------------------------------------------------------------------

[세션 통계: 내려받기 한 번]
  첫 페치까지                2.21 초
  전체 경과                  2.36 초
  session pga memory max     61.8 MB
  session logical reads      643   (함수 안 SQL이 T_ORD_STLM을 한 번 읽는다)
  FN_FMT_LINE 호출           100,000

문제

첫 줄이 2초 넘게 오지 않고 세션 메모리가 60MB를 넘는 이유를 함수의 모양으로 설명하시오.
줄 규칙(FN_FMT_LINE)은 그대로 두고, 첫 줄이 바로 오고 메모리가 줄 수와 상관없이 작게 유지되도록 함수를 고치시오. 고친 뒤에도 줄지 않는 일은 무엇인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

CREATE OR REPLACE PACKAGE BODY PKG_STLM_FILE IS
  -- 패키지 명세의 선언에도 PIPELINED 를 붙인다
  FUNCTION FN_LINES RETURN T_LINE_TAB PIPELINED IS
  BEGIN
    FOR r IN (SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT
                FROM T_ORD_STLM) LOOP
      -- 한 줄을 만들 때마다 바로 내보낸다 (모아 두지 않는다)
      PIPE ROW (T_LINE_OBJ(r.STLM_SEQ,
                  FN_FMT_LINE(r.STLM_SEQ, r.ORD_SEQ, r.CUS_ID, r.STLM_DT, r.STLM_AMT)));
    END LOOP;
    RETURN;                            -- 파이프라인 함수는 값 없이 끝낸다
  END;
  -- FN_FMT_LINE 그대로
END;
/

-- 내려받기 도구의 문장은 그대로
SELECT STLM_SEQ, LINE FROM TABLE(PKG_STLM_FILE.FN_LINES);

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |      1 |        |   100K |00:00:02.19 |       0 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| FN_LINES |      1 |   8168 |   100K |00:00:02.19 |       0 |
-------------------------------------------------------------------------------------------------------

[세션 통계: 같은 10만 줄]
                            전 (컬렉션)    후 (PIPELINED)
  첫 페치까지                 2.21 초        0.01 초
  전체 경과                   2.36 초        2.19 초
  session pga memory max      61.8 MB         2.4 MB
  session logical reads           643            643
  FN_FMT_LINE 호출            100,000        100,000

모범 답안 서술

지금 함수는 보통 테이블 함수다. FOR 루프가 정산 10만 건을 모두 읽어 v_tab에 한 줄씩 담고, 루프가 끝난 뒤에야 RETURN v_tab으로 컬렉션을 통째로 돌려준다. SQL 쪽의 COLLECTION ITERATOR PICKLER FETCH는 그 컬렉션을 받은 다음에야 첫 줄을 내보낼 수 있다. 그래서 첫 페치까지 2.21초가 걸리고, 그동안 10만 줄(줄마다 200자)이 세션 메모리(PGA)에 쌓여 61.8MB가 된다. 넷이 동시에 받으면 그만큼씩 네 번 올라간다. PIPELINED로 선언하고 한 줄을 만들 때마다 PIPE ROW로 내보내면, SQL이 줄을 가져가는 대로 함수가 다음 줄을 만든다. 내려받기 도구가 500줄을 가져가면 함수도 그만큼 만들고 기다린다. 첫 페치는 0.01초에 오고, 메모리는 줄 수와 상관없이 2.4MB 안팎으로 일정하다. 동시에 여럿이 받아도 세션마다 PGA가 작게 유지되므로 PGA 한도(PGA_AGGREGATE_LIMIT)에 걸릴 걱정이 준다. 함수가 끝날 때는 값 없이 RETURN;으로 끝낸다. 줄지 않는 일도 있다. 정산 640블록을 한 번 읽는 일과 FN_FMT_LINE으로 줄 10만 개를 만드는 일은 그대로라 전체 경과는 2.36 → 2.19초로 비슷하다. 파이프라인 함수가 바꾸는 것은 일의 양이 아니라 결과를 넘기는 방식, 곧 첫 줄이 오는 시점과 메모리다.

해설

이름만 파이프라인인 함수: PIPELINED를 붙이고도 안에서 BULK COLLECT로 10만 행을 먼저 다 모은 뒤 루프에서 PIPE ROW하면 첫 줄과 메모리는 그대로다. 만드는 대로 바로 넘겨야 한다. 안쪽에서 묶어 읽고 싶다면 LIMIT 1000으로 나눠 읽고 덩어리마다 내보낸다.
소비자가 멈추면 함수도 멈춘다: WHERE ROWNUM <= 10처럼 앞의 몇 줄만 가져가면 함수는 뒤의 줄을 만들지 않고 NO_DATA_NEEDED 예외로 끝난다. 함수 안에 WHEN OTHERS 처리가 있다면 이 예외는 오류로 기록하지 않게 따로 다룬다.
SQL로 쓸 수 있으면 SQL이 낫다: 줄 규칙을 LPAD, TO_CHAR로 다 쓸 수 있다면 함수 없이 SELECT만으로 받는 편이 PL/SQL 호출 10만 번도 없앤다. 여기서는 같은 규칙을 다른 제출 파일 세 개가 함께 쓰므로 함수에 둔다.
계획의 E-Rows 8168은 옵티마이저가 테이블 함수의 건수를 모를 때 쓰는 기본값이다. 이 문장처럼 함수만 읽으면 상관없지만, 다른 테이블과 조인하면 계획을 망칠 수 있다(PLSQL-034).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황