정보계를 클라우드로 옮긴 뒤 월별 매장 주문 리포트 적재가 2초에서 53초가 됐다

실전INSERT버거584 / 700

상황

버거 본사 정보계 DB(RPT)는 POS 운영 DB를 DB 링크 @POS로 읽는다. 매달 초 「매장별 월 주문 건수」를 정보계 테이블 RPT_SHOP_MON에 적재한다(2013년이면 매장 200곳 × 12개월 = 2,400행). 지난달 정보계를 클라우드로 옮긴 뒤 이 적재가 2.2초에서 53초가 됐다. SQL은 한 글자도 바뀌지 않았다. 인프라팀은 "전용선 대역폭을 두 배로 늘리겠다"라고 하고, 선배는 DRIVING_SITE 힌트 한 줄이면 된다고 한다. 박팀장은 대안을 비교해 하나를 고르고 무엇을 잃는지도 적어 달라고 했다.

테이블과 인덱스

(POS 운영 DB) ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · SHOP_NO VARCHAR2(6) · 2013년 약 50만 건 · 인덱스 ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS, ORD_NO)(이 문제에서는 있다, 리프 약 5,000블록)
(정보계) SHOP_AREA 매장 권역: 200행 · 5블록 · SHOP_NO · AREA_CD(날마다 SHOP을 복제하며 권역을 붙인 표)
(정보계) RPT_MONTH 월 달력: 12행 · 4블록 · YM('201301' ~ '201312') · FROM_DT(그달 1일) · TO_DT(그달 말일)
(정보계) RPT_SHOP_MON 적재 대상: YM · SHOP_NO · AREA_CD · ORD_CNT · 인덱스 없음
정보계와 POS 운영 DB 사이의 왕복은 옮기기 전 0.4ms, 지금 11ms다. 운영 DB는 낮에 POS 결제를 받는다.

원본 SQL

sql

-- 매달 초 적재 (2016) : 매장 × 월마다 운영 DB에 건수를 묻는다
INSERT INTO RPT_SHOP_MON (YM, SHOP_NO, AREA_CD, ORD_CNT)
SELECT m.YM, s.SHOP_NO, s.AREA_CD,
       (SELECT COUNT(*)
          FROM ORD@POS o
         WHERE o.SHOP_NO = s.SHOP_NO
           AND o.ORD_DT BETWEEN m.FROM_DT AND m.TO_DT) AS ORD_CNT
  FROM RPT_MONTH m, SHOP_AREA s
 WHERE m.YM BETWEEN '201301' AND '201312';

실행계획 학습용 예시

xplan

[정보계 · DISPLAY_CURSOR · 적재 한 번]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |              |      1 |        |      0 |00:00:53.10 |    2523 |        |      |
|   1 |  LOAD TABLE CONVENTIONAL  | RPT_SHOP_MON |      1 |        |      0 |00:00:53.10 |    2523 |        |      |
|   2 |   REMOTE                  | ORD          |   2400 |      1 |   2400 |00:00:52.71 |       0 |    POS | R->S |
|   3 |   MERGE JOIN CARTESIAN    |              |      1 |   2400 |   2400 |00:00:00.01 |       9 |        |      |
|*  4 |    TABLE ACCESS FULL      | RPT_MONTH    |      1 |     12 |     12 |00:00:00.01 |       4 |        |      |
|   5 |    BUFFER SORT            |              |     12 |    200 |   2400 |00:00:00.01 |       5 |        |      |
|   6 |     TABLE ACCESS FULL     | SHOP_AREA    |      1 |    200 |    200 |00:00:00.01 |       5 |        |      |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(("M"."YM">='201301' AND "M"."YM"<='201312'))

Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - SELECT COUNT(*) FROM "ORD" "O" WHERE "O"."SHOP_NO"=:1 AND "O"."ORD_DT">=:2 AND "O"."ORD_DT"<=:3 (accessing 'POS' )

[정보계 세션 · V$SESSION_EVENT]
  SQL*Net message from dblink       4,801번 · 52.3 초 (평균 10.9 ms)
  SQL*Net message to dblink         4,801번 ·  0.02 초

[POS 운영 DB · 원격 문장의 V$SQL]
  SELECT COUNT(*) FROM "ORD" "O" WHERE ...   EXECUTIONS 2,400 · BUFFER_GETS 7,212 · ELAPSED 0.21 초 · INDEX RANGE SCAN (ORD_X02)

[옮기기 전 같은 적재]  2.2 초 · message from dblink 4,801번 · 1.9 초

문제

적재가 2.2초에서 53초가 된 이유를 계획의 Starts와 대기 통계로 설명하시오. 대역폭 증설안을 평가하시오.
결과가 바뀌지 않게 적재를 고치시오. 선배의 DRIVING_SITE 안이 이 문장에서 통하는지도 쓰시오.
고른 안과 다른 안(대역폭 증설, DRIVING_SITE, 운영 DB 쪽 요약 복제)을 견주어 각각 무엇을 잃는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 운영 DB에 한 번만 묻는다 : 2013년을 (월, 매장)으로 묶은 2,400행 남짓을 한 번에 받는다
INSERT INTO RPT_SHOP_MON (YM, SHOP_NO, AREA_CD, ORD_CNT)
SELECT m.YM, s.SHOP_NO, s.AREA_CD, NVL(c.ORD_CNT, 0)        -- 주문이 없는 매장 · 달은 원래처럼 0
  FROM RPT_MONTH m
 CROSS JOIN SHOP_AREA s
  LEFT JOIN (SELECT SUBSTR(ORD_DT, 1, 6) AS YM, SHOP_NO, COUNT(*) AS ORD_CNT
               FROM ORD@POS
              WHERE ORD_DT BETWEEN '20130101' AND '20131231'
              GROUP BY SUBSTR(ORD_DT, 1, 6), SHOP_NO) c
    ON c.YM = m.YM AND c.SHOP_NO = s.SHOP_NO
 WHERE m.YM BETWEEN '201301' AND '201312';
-- 운영 DB의 결제가 한산한 새벽(05:30)에 돌린다

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

xplan

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |              |      1 |        |      0 |00:00:00.61 |    2531 |          |        |      |
|   1 |  LOAD TABLE CONVENTIONAL  | RPT_SHOP_MON |      1 |        |      0 |00:00:00.61 |    2531 |          |        |      |
|*  2 |   HASH JOIN RIGHT OUTER   |              |      1 |   2400 |   2400 |00:00:00.58 |       9 | 1.4M (0) |        |      |
|   3 |    REMOTE                 |              |      1 |   2400 |   2400 |00:00:00.55 |       0 |          |    POS | R->S |
|   4 |    MERGE JOIN CARTESIAN   |              |      1 |   2400 |   2400 |00:00:00.01 |       9 |          |        |      |
|*  5 |     TABLE ACCESS FULL     | RPT_MONTH    |      1 |     12 |     12 |00:00:00.01 |       4 |          |        |      |
|   6 |     BUFFER SORT           |              |     12 |    200 |   2400 |00:00:00.01 |       5 |          |        |      |
|   7 |      TABLE ACCESS FULL    | SHOP_AREA    |      1 |    200 |    200 |00:00:00.01 |       5 |          |        |      |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."YM"(+)="M"."YM" AND "C"."SHOP_NO"(+)="S"."SHOP_NO")
   5 - filter(("M"."YM">='201301' AND "M"."YM"<='201312'))

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT SUBSTR("ORD_DT",1,6),"SHOP_NO",COUNT(*) FROM "ORD" "ORD" WHERE "ORD_DT">='20130101' AND
       "ORD_DT"<='20131231' GROUP BY SUBSTR("ORD_DT",1,6),"SHOP_NO" (accessing 'POS' )

[POS 운영 DB]  EXECUTIONS 1 · BUFFER_GETS 5,138 (INDEX FAST FULL SCAN ORD_X02 + HASH GROUP BY) · 0.29 초
[정보계 세션]  SQL*Net message from dblink 27번 · 0.33 초 · 적재 53.1 → 0.61 초 · 결과 2,400행 (원래 판과 MINUS 0건)

모범 답안 서술

원인: 스칼라 서브쿼리는 바깥 행마다 실행된다. 바깥은 12개월 × 200개 매장 = 2,400행이라 2번 줄 REMOTE의 Starts가 2,400이고, 원격 문장이 운영 DB에서 2,400번 실행됐다. 한 번은 ORD_X02로 3블록, 2,400번을 다 합쳐도 7,212블록, 0.21초다. 나머지 52초는 정보계가 링크 너머의 답을 기다린 SQL*Net message from dblink(4,801번)다. 실행 한 번에 묻고 받는 왕복이 두 번씩이다. 옮기기 전에는 왕복 한 번이 0.4ms라 4,801번이 1.9초였고, 지금은 11ms라 같은 4,801번이 52초다. SQL과 계획은 그대로이고 왕복 한 번의 지연만 27배가 됐다. 대역폭 증설: 원격 문장이 돌려주는 것은 건수 하나라 보내는 양은 수십 KB다. 시간을 정하는 것은 양이 아니라 왕복 횟수와 왕복 한 번의 지연이므로, 대역폭을 두 배로 늘려도 거의 줄지 않는다. 고친 판: 운영 DB에 2,400번 묻지 않고 한 번만 묻는다. 2013년 주문을 (월, 매장)으로 묶는 인라인 뷰는 원격 객체만 쓰므로 GROUP BY까지 통째로 운영 DB로 보내진다. 운영 DB는 ORD_X02를 한 번 훑어(5,138블록, 0.29초) 2,400행 남짓을 돌려주고, 정보계는 그것을 달력과 매장에 해시 조인으로 붙인다. 링크 대기는 4,801 → 27번, 적재는 53.1 → 0.61초다. 원래 판은 주문이 없는 매장과 달에도 COUNT(*)가 0을 돌려주므로, 고친 판도 아우터 조인과 NVL(c.ORD_CNT, 0)으로 2,400행을 모두 남긴다. 두 판을 MINUS로 대조하면 0건이다. DRIVING_SITE: 이 힌트는 조인을 원격 쪽에서 하게 하는 힌트인데, INSERT … SELECT에서는 무시된다. 행을 넣는 곳이 정보계이므로 문장은 정보계가 몰기 때문이다. 리포트가 SELECT였다면 통했겠지만, 그때는 정보계의 달력과 매장 표를 운영 DB로 끌어가 운영 DB가 조인과 2,400번의 건수 세기를 맡는다. 고른 안의 대가와 다른 안: 고친 판은 운영 DB가 한 번에 5천 블록 남짓을 읽고 묶는 일을 떠안는다. 짧지만 POS 결제가 몰리는 낮에는 돌리지 않고 새벽에 돌린다. 운영 DB 쪽에 월 요약을 구체화 뷰(MV)로 만들어 두고 정보계가 그것만 가져가는 방법도 있다. 링크 너머의 일은 가장 작지만, 운영 DB에 갱신(refresh) 부담과 관리할 객체가 하나 늘고 요약이 갱신 주기만큼 늦다. 한 달에 한 번 도는 적재에는 지나치다.

해설

원격 문장의 실행 횟수는 REMOTE 줄의 Starts로 읽는다. 로컬의 NL 조인 inner나 스칼라 서브쿼리 안에 원격 테이블이 있으면 바깥 행마다 링크를 오간다(교재 06화의 Starts와 같은 셈이다).
원격 쪽에서 묶은 결과를 받는 것이 가장 흔한 처방이다. 인라인 뷰 안의 객체가 모두 같은 원격 DB에 있으면 오라클은 그 뷰를 통째로 원격 문장으로 보낸다. 로컬 테이블이 섞이면 조건과 집계를 다 보내지 못할 수 있으니 Remote SQL Information에서 확인한다.
반대로 운영 DB에서 INSERT INTO RPT_SHOP_MON@RPT SELECT …로 밀어 넣는 방법도 있다. 집계가 운영 DB 안에서 끝나지만, 잡이 운영 DB에 생기고 원격 테이블에 넣는 DML의 제약(분산 트랜잭션)을 함께 떠안는다.
같은 문제는 화면에서도 생긴다. DB 링크 너머의 작은 코드 테이블을 행마다 조인하는 화면이 있으면, 그 표를 로컬에 복제해 두는 편이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황