5,826블록짜리 테이블을 파일로 내리는데 논리 읽기가 100만이 넘는다

기초PLSQL한빛유통666 / 700

상황

회계팀은 분기마다 외부 감사인에게 「주문 원장 추출 파일」을 보낸다. 2020년에 강도현이 만든 프로시저가 T_ORD_BIG 100만 건을 한 줄씩 파일에 쓴다(UTL_FILE). 이번 분기에는 감사 일정이 당겨져서 파일을 하루에도 몇 번씩 다시 뽑는다. DBA가 한 번 돌린 세션 통계와 TKPROF를 보내 왔다. "테이블은 5,826블록인데 논리 읽기가 100만이 넘어요. 파일 하나 쓰는데 왜 이렇게 읽죠?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · 통계 최신
파일은 디렉터리 객체 AUDIT_DIR에 쓴다. 한 줄은 약 60바이트다.
프로시저는 기본값(PLSQL_OPTIMIZE_LEVEL = 2)으로 컴파일되어 있다.

원본 SQL

sql

PROCEDURE EXP_ORD_AUDIT(p_file VARCHAR2) IS
  CURSOR c IS
    SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
      FROM T_ORD_BIG;
  r  c%ROWTYPE;
  f  UTL_FILE.FILE_TYPE;
BEGIN
  f := UTL_FILE.FOPEN('AUDIT_DIR', p_file, 'w', 32767);
  OPEN c;
  LOOP
    FETCH c INTO r;                          -- 한 건씩
    EXIT WHEN c%NOTFOUND;
    UTL_FILE.PUT_LINE(f, r.ORD_SEQ || '|' || r.CUS_ID || '|' ||
                         TO_CHAR(r.ORD_DT, 'YYYYMMDD') || '|' || r.ORD_ST || '|' || r.ORD_AMT);
  END LOOP;
  CLOSE c;
  UTL_FILE.FCLOSE(f);
END;

실행계획 학습용 예시

xplan

[세션 통계 — EXP_ORD_AUDIT 한 번 (V$MYSTAT 증가분)]
  경과 시간                  2.86 ~ 2.97 초
  CPU used by this session   2.74 초
  session logical reads      1,000,015
  recursive calls            1,000,008
  execute count              6

[TKPROF — 구동 커서]
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD_BIG

call     count       cpu    elapsed       disk      query    current        rows
------- -------  -------- ---------- ---------- ---------- ----------  ----------
Parse         1      0.00       0.00          0          0          0           0
Execute       1      0.00       0.00          0          0          0           0
Fetch   1000001      1.21       1.24          0    1000003          0     1000000
------- -------  -------- ---------- ---------- ---------- ----------  ----------
total   1000003      1.21       1.24          0    1000003          0     1000000

Rows (1st)  Row Source Operation
----------  ---------------------------------------------------
   1000000  TABLE ACCESS FULL T_ORD_BIG (cr=1000003 pr=0 pw=0)

문제

논리 읽기가 테이블 블록 수의 170배가 넘는 이유를 TKPROF의 Fetch 줄로 설명하시오.
파일 내용이 바뀌지 않게 프로시저를 고치시오. 커서 FOR 루프로 바꾸는 안과 비교해 어느 쪽을 고를지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE EXP_ORD_AUDIT(p_file VARCHAR2) IS
  CURSOR c IS
    SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
      FROM T_ORD_BIG;
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  v  t_rows;
  f  UTL_FILE.FILE_TYPE;
BEGIN
  f := UTL_FILE.FOPEN('AUDIT_DIR', p_file, 'w', 32767);
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 1000;      -- 천 건씩 한 번에 (Fetch 1,000,001 → 1,001)
    EXIT WHEN v.COUNT = 0;                       -- %NOTFOUND 로 나가면 마지막 덩어리를 버릴 수 있다
    FOR i IN 1 .. v.COUNT LOOP
      UTL_FILE.PUT_LINE(f, v(i).ORD_SEQ || '|' || v(i).CUS_ID || '|' ||
                           TO_CHAR(v(i).ORD_DT, 'YYYYMMDD') || '|' || v(i).ORD_ST || '|' || v(i).ORD_AMT);
    END LOOP;
  END LOOP;
  CLOSE c;
  UTL_FILE.FCLOSE(f);
END;

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

xplan

[세션 통계 — 고친 판 한 번]
  경과 시간                  1.68 ~ 1.74 초   (남은 시간은 대부분 UTL_FILE 쓰기와 줄 만들기)
  CPU used by this session   1.61 초
  session logical reads      6,841
  recursive calls            1,009

[TKPROF — 구동 커서]
call     count       cpu    elapsed       disk      query    current        rows
------- -------  -------- ---------- ---------- ---------- ----------  ----------
Parse         1      0.00       0.00          0          0          0           0
Execute       1      0.00       0.00          0          0          0           0
Fetch      1001      0.17       0.18          0       6829          0     1000000

(파일은 원래 판과 같다: 1,000,000줄 · 바이트 수 같음 · diff 0)

모범 답안 서술

TKPROF의 구동 커서 줄은 Fetch 1,000,001, rows 1,000,000이다. 페치 한 번에 한 행씩 가져왔다는 뜻이다. 명시적 커서의 FETCH c INTO r는 PLSQL_OPTIMIZE_LEVEL 2에서도 한 건씩 가져온다. 컴파일러가 100건씩 자동으로 묶어 주는 것은 커서 FOR 루프뿐이다. 페치할 때마다 PL/SQL에서 SQL 엔진으로 한 번 건너가고(recursive calls 1,000,008), 같은 블록을 페치마다 다시 잡으므로 논리 읽기가 블록 수(5,826)가 아니라 행 수만큼(query 1,000,003) 나온다. FETCH c BULK COLLECT INTO v LIMIT 1000으로 천 건씩 받으면 Fetch는 1,000,001 → 1,001, 논리 읽기는 1,000,015 → 6,841, 구동 커서의 시간은 1.24초 → 0.18초로 준다. 전체 경과는 2.9초 → 1.7초다. 남은 시간은 대부분 UTL_FILE로 100만 줄을 쓰는 몫이라 SQL 쪽에서 더 줄일 것은 없다. 루프를 빠져나가는 조건은 v.COUNT = 0으로 둔다. c%NOTFOUND를 FETCH 바로 뒤에서 보면 1,000건이 안 되는 마지막 덩어리를 쓰지 않고 나간다. 커서 FOR 루프(FOR r IN c LOOP)로 바꿔도 레벨 2에서는 100건씩 자동으로 가져온다(Fetch 10,001). 코드가 짧은 것은 장점이지만, 묶음이 컴파일 설정에 달려 있다(레벨 1로 컴파일하면 다시 한 건씩 가져온다). 행이 많은 추출이므로 LIMIT를 코드에 적는 쪽을 고른다. LIMIT 없이 BULK COLLECT하면 100만 행이 한꺼번에 세션 메모리(PGA)에 올라가므로 1,000 안팎으로 나눠 받는다.

해설

함정: EXIT WHEN c%NOTFOUND를 FETCH … BULK COLLECT 바로 뒤에 두면 마지막 덩어리를 버린다. 오늘은 100만 건이 1,000으로 나누어떨어져 파일이 맞게 나오지만, 주문이 한 건만 늘어도 마지막 1줄이 빠진다. COUNT = 0으로 나가거나 %NOTFOUND 검사를 루프 끝으로 옮긴다.
LIMIT는 100 ~ 1,000이면 충분하다. 교재 15화의 측정에서 1,000을 넘기면 왕복은 거의 줄지 않고 메모리만 커졌다(LIMIT 100,000에서 세션 PGA 약 34MB).
같은 원리가 클라이언트 쪽에도 있다. SQL*Plus의 ARRAYSIZE가 그것이고, 배열 크기는 언제나 rows ÷ Fetch로 읽는다(교재 15화 §4.2, 09화).
페치를 묶어도 recursive calls가 1,000 남짓 남는 것은 덩어리마다 한 번씩 SQL 엔진에 가기 때문이다. 루프 안에 SQL이 없는 추출이라 이 숫자가 바닥이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황