BULK COLLECT와 FORALL을 다 썼는데 recursive calls가 30만 번이다

기초PLSQL이커머스668 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스 추천팀은 매일 새벽 상품 조회 이력을 추천 엔진이 읽는 적재 테이블로 옮긴다. 신입 개발자가 교육에서 배운 대로 BULK COLLECT와 FORALL로 짰다며 리뷰를 부탁했다. 조민우가 한 번 돌리고 세션 통계를 떠 보니 recursive calls가 300,232였다. "천 건씩 묶었으면 몇백 번이어야 하지 않나요?"

테이블과 인덱스

product_views: 100,000행 · 367블록 · PK(view_id) · product_id · customer_id · view_date DATE(시분초 있음)
rec_view_feed(적재 대상, 이 문제용): view_id · product_id · customer_id · view_ts VARCHAR2(19) · day_tp VARCHAR2(10) · score NUMBER · load_dt DATE · 인덱스 없음 · 배치마다 TRUNCATE
PKG_REC_SCORE.DECAY(product_id, view_date): SQL 없이 패키지 배열로 최근성 가중치를 계산하는 PL/SQL 함수. 추천 화면도 같은 함수를 쓴다.

원본 SQL

sql

PROCEDURE LOAD_VIEW_FEED IS
  CURSOR c IS SELECT view_id, product_id, customer_id, view_date FROM product_views;
  TYPE t_num IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
  TYPE t_dt  IS TABLE OF DATE         INDEX BY PLS_INTEGER;
  TYPE t_vc  IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  v_id t_num;  v_pid t_num;  v_cid t_num;  v_dt t_dt;
  v_ts t_vc;   v_dy t_vc;    v_sc t_num;   v_ld t_dt;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE rec_view_feed';
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_id, v_pid, v_cid, v_dt LIMIT 1000;
    EXIT WHEN v_id.COUNT = 0;
    FOR i IN 1 .. v_id.COUNT LOOP
      SELECT TO_CHAR(v_dt(i), 'YYYY-MM-DD"T"HH24:MI:SS') INTO v_ts(i) FROM dual;
      SELECT DECODE(TO_CHAR(v_dt(i), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH'),
                    'SAT', 'WEEKEND', 'SUN', 'WEEKEND', 'WEEKDAY') INTO v_dy(i) FROM dual;
      SELECT SYSDATE INTO v_ld(i) FROM dual;
      v_sc(i) := PKG_REC_SCORE.DECAY(v_pid(i), v_dt(i));
    END LOOP;
    FORALL i IN 1 .. v_id.COUNT
      INSERT INTO rec_view_feed (view_id, product_id, customer_id, view_ts, day_tp, score, load_dt)
      VALUES (v_id(i), v_pid(i), v_cid(i), v_ts(i), v_dy(i), v_sc(i), v_ld(i));
  END LOOP;
  CLOSE c;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — LOAD_VIEW_FEED 한 번 (V$MYSTAT 증가분)]
  경과 시간                  1.02 ~ 1.08 초
  CPU used by this session   0.99 초
  recursive calls            300,232
  execute count              300,128
  session logical reads      2,436

[TKPROF — 문장별 요약 (sys=no)]
  SQL                                                            Execute    Fetch      rows   query   elapsed
  SELECT TO_CHAR(:B1 , 'YYYY-MM-DD"T"HH24:MI:SS') FROM DUAL      100,000  100,000   100,000       0      0.27
  SELECT DECODE(TO_CHAR(:B1 , 'DY', …), …) FROM DUAL             100,000  100,000   100,000       0      0.28
  SELECT SYSDATE FROM DUAL                                       100,000  100,000   100,000       0      0.25
  INSERT INTO REC_VIEW_FEED (…) VALUES (:B7 , …)                     100        0   100,000      19      0.09
  SELECT VIEW_ID, PRODUCT_ID, CUSTOMER_ID, VIEW_DATE FROM …            1      101   100,000     468      0.02
  (DUAL 세 문장의 계획은 FAST DUAL · Buffers 0)

문제

천 건씩 묶었는데도 recursive calls가 30만 번인 이유를 TKPROF로 설명하시오.
적재 결과가 바뀌지 않게 프로시저를 고치시오. PL/SQL로 옮길 때 그대로 옮길 수 없는 식이 있다면 어떻게 바꿨는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE LOAD_VIEW_FEED IS
  CURSOR c IS SELECT view_id, product_id, customer_id, view_date FROM product_views;
  TYPE t_num IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
  TYPE t_dt  IS TABLE OF DATE         INDEX BY PLS_INTEGER;
  TYPE t_vc  IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  v_id t_num;  v_pid t_num;  v_cid t_num;  v_dt t_dt;
  v_ts t_vc;   v_dy t_vc;    v_sc t_num;
  v_now DATE := SYSDATE;                 -- 배치 시각은 한 번만 (원래는 원소마다 SELECT SYSDATE FROM dual)
  v_wd  VARCHAR2(3);
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE rec_view_feed';
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_id, v_pid, v_cid, v_dt LIMIT 1000;
    EXIT WHEN v_id.COUNT = 0;
    FOR i IN 1 .. v_id.COUNT LOOP        -- 식은 PL/SQL 안에서 계산한다 (SQL 엔진으로 건너가지 않는다)
      v_ts(i) := TO_CHAR(v_dt(i), 'YYYY-MM-DD"T"HH24:MI:SS');
      v_wd    := TO_CHAR(v_dt(i), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH');
      v_dy(i) := CASE WHEN v_wd IN ('SAT', 'SUN') THEN 'WEEKEND' ELSE 'WEEKDAY' END;  -- DECODE 는 PL/SQL 식에서 못 쓴다
      v_sc(i) := PKG_REC_SCORE.DECAY(v_pid(i), v_dt(i));
    END LOOP;
    FORALL i IN 1 .. v_id.COUNT
      INSERT INTO rec_view_feed (view_id, product_id, customer_id, view_ts, day_tp, score, load_dt)
      VALUES (v_id(i), v_pid(i), v_cid(i), v_ts(i), v_dy(i), v_sc(i), v_now);
  END LOOP;
  CLOSE c;
  COMMIT;
END;

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

xplan

[세션 통계 — 고친 판 한 번]
  경과 시간                  0.19 ~ 0.22 초
  recursive calls            232
  execute count              128
  session logical reads      2,431

[TKPROF]  DUAL 문장 0개 · INSERT Execute 100 (rows 100,000) · 구동 커서 Fetch 101
(적재 결과는 load_dt 말고는 원래 판과 같다: view_ts · day_tp · score MINUS 0 / 0)

모범 답안 서술

TKPROF 위의 세 줄은 SELECT … FROM DUAL이고 Execute가 각각 100,000이다. 조회 이력 한 건마다 dual 조회 세 개가 실행되어 10만 × 3 = 30만 번 PL/SQL에서 SQL 엔진으로 건너갔다. FAST DUAL이라 Buffers는 0이지만 한 번 건너갈 때마다 실행 호출이 하나씩 생기고, 세 문장의 elapsed만 0.8초로 경과 시간의 대부분이다. BULK COLLECT와 FORALL이 묶어 주는 것은 가져오기(Fetch 101)와 넣기(INSERT Execute 100)뿐이다. 그 사이 루프 안에 쓴 SQL은 원소마다 따로 나간다. 세 식은 SQL이 아니라 PL/SQL 식으로 계산한다. TO_CHAR는 PL/SQL에서 그대로 쓸 수 있다. DECODE는 SQL에서만 쓰는 함수라 PL/SQL 식에 쓰면 컴파일 오류(PLS-00204)가 나므로 CASE로 바꾼다. SYSDATE는 루프 밖에서 한 번만 구해 배치 시각으로 쓴다. 그러면 recursive calls는 300,232 → 232, 경과는 1.05초 → 0.2초로 준다. 논리 읽기는 2,436 → 2,431로 거의 같다. 줄어든 것은 읽은 블록이 아니라 엔진 사이를 오간 횟수다. 바뀌는 것이 하나 있다. 원래는 원소마다 SYSDATE를 다시 구해 load_dt가 1초 남짓 사이에서 조금씩 달랐는데, 고친 판은 모두 배치 시작 시각 하나다. 적재 시각으로는 이쪽이 맞는 뜻이지만, 추천팀에 바뀐다는 사실을 알린다.

해설

다른 정답: 형식 바꾸기와 요일 구분을 구동 커서의 SELECT 목록으로 옮겨도 된다(SELECT view_id, …, TO_CHAR(view_date, …), DECODE(…) FROM product_views). SQL 엔진이 행을 읽으면서 계산하므로 건너가는 일이 없고, 이때는 DECODE를 그대로 쓸 수 있다.
PKG_REC_SCORE.DECAY는 루프 안에 남겨도 된다. PL/SQL에서 PL/SQL 함수를 부르는 것은 엔진을 건너가지 않는다. 이 함수를 INSERT 문 안에서 부르면 반대로 SQL에서 PL/SQL로 10만 번 건너간다(교재 15화 §1.1, 약 0.53µs씩).
SELECT … INTO … FROM dual은 옛 코드에 흔하다. 오래된 PL/SQL에서 쓸 수 없던 SQL 함수를 쓰려고 들어간 습관이다. 지금은 대부분의 SQL 함수를 PL/SQL에서 바로 쓸 수 있고, 안 되는 것은 DECODE와 집계 함수, 분석 함수 정도다.
Buffers가 0인 SQL은 실행계획이나 읽은 블록 수로는 보이지 않는다. 그래서 PL/SQL 배치를 볼 때는 recursive calls와 TKPROF의 Execute 수를 함께 본다. 원소 수의 배수로 늘어나는 문장이 루프 안에 숨은 SQL이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황