판매상품 110만 행 이관 단계만 47초, 직접 경로에 인덱스도 없는데 느리다

기초INSERT유통479 / 700

상황

본사 레거시 유통 차세대 이관 리허설이다. 판매상품(CF11) 1,096,708행을 새 판매상품 테이블 SLE_ITM_N으로 옮기면서 판매상품 번호 SLE_ITM_ID를 시퀀스로 매긴다. 다른 여섯 단계는 1 ~ 3초인데 이 단계만 47.6초다. 이관 담당: "직접 경로로 넣고 인덱스도 없는데 왜 느리죠?" 시퀀스는 이관 스크립트가 바로 앞에서 기본값으로 만든다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록
SLE_ITM_N 새 판매상품 테이블: 비어 있다. 인덱스, 제약, 트리거는 없다(PK는 적재 뒤에 만든다).
시퀀스 SQ_SLE_ITM: 이관 뒤에는 새 판매 화면이 한 건씩 번호를 받는다. 번호는 대리키라 빈 번호가 있어도 된다(업무 확인).
이관 리허설 DB는 단일 인스턴스, NOARCHIVELOG다.

원본 SQL

sql

-- 이관 단계 ⑤ 판매상품
CREATE SEQUENCE SQ_SLE_ITM START WITH 1 INCREMENT BY 1;

INSERT /*+ APPEND */ INTO SLE_ITM_N (SLE_ITM_ID, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
SELECT SQ_SLE_ITM.NEXTVAL, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC
  FROM CF11;
COMMIT;

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:47.61 |    170K |
|   1 |  LOAD AS SELECT                  | SLE_ITM_N  |      1 |        |      0 |00:00:47.61 |    170K |
|   2 |   OPTIMIZER STATISTICS GATHERING |            |      1 |   1096K|   1096K|00:00:46.88 |    164K |
|   3 |    SEQUENCE                      | SQ_SLE_ITM |      1 |        |   1096K|00:00:46.52 |    164K |
|   4 |     TABLE ACCESS FULL            | CF11       |      1 |   1096K|   1096K|00:00:00.21 |    8153 |
-----------------------------------------------------------------------------------------------------------

[세션 통계 — 단계 ⑤ 한 번]
  경과 시간                 47.61 초
  CPU used by this session  31.2 초
  recursive calls          110,214
  session logical reads    170,480
  redo size                 41.2 MB

[TKPROF — 재귀 SQL]
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute  54836      9.84      21.40          0          0     109672       54836

[DBA_SEQUENCES] SQ_SLE_ITM · CACHE_SIZE 20 · ORDER_FLAG N · LAST_NUMBER 1096721

문제

직접 경로로 넣었는데도 47.6초와 redo 41.2MB가 나온 이유를 실행계획 3번 줄과 TKPROF로 설명하시오.
이관 단계를 고치시오. 고친 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 이관 동안에는 번호를 크게 묶어 꺼낸다 (SEQ$ 갱신 54,836번 → 110번)
CREATE SEQUENCE SQ_SLE_ITM START WITH 1 INCREMENT BY 1 CACHE 10000;

INSERT /*+ APPEND */ INTO SLE_ITM_N (SLE_ITM_ID, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
SELECT SQ_SLE_ITM.NEXTVAL, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC
  FROM CF11;
COMMIT;

-- 이관이 끝나면 화면용 크기로 돌린다 (재시작 때 잃는 번호를 줄인다)
ALTER SEQUENCE SQ_SLE_ITM CACHE 1000;

-- 다른 답: 시퀀스 없이 SELECT ROWNUM, … 으로 매기고, 시퀀스는 적재 뒤 START WITH 1096709 로 만든다

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:01.92 |    8820 |
|   1 |  LOAD AS SELECT                  | SLE_ITM_N  |      1 |        |      0 |00:00:01.92 |    8820 |
|   2 |   OPTIMIZER STATISTICS GATHERING |            |      1 |   1096K|   1096K|00:00:01.31 |    8483 |
|   3 |    SEQUENCE                      | SQ_SLE_ITM |      1 |        |   1096K|00:00:00.98 |    8483 |
|   4 |     TABLE ACCESS FULL            | CF11       |      1 |   1096K|   1096K|00:00:00.21 |    8153 |
-----------------------------------------------------------------------------------------------------------

[세션 통계] 경과 47.61 → 1.92 초 · update seq$ 54,836 → 110번 · recursive calls 110,214 → 약 280
            redo 41.2 → 0.3 MB

모범 답안 서술

직접 경로 적재는 제대로 돌았다(1번 줄 LOAD AS SELECT). 시간을 먹은 것은 3번 줄 SEQUENCE다. 시퀀스를 기본값(CACHE 20)으로 만들었기 때문에 번호를 20개 꺼내 쓸 때마다 딕셔너리 테이블 SEQ$의 그 시퀀스 행을 고친다. 1,096,708행이면 54,836번이다. TKPROF의 update seq$ Execute 54,836이 그 흔적이고, 갱신마다 재귀 트랜잭션으로 블록을 고치고 커밋하므로 SEQUENCE 줄의 Buffers 164K와 CPU 31초가 여기서 나왔다. redo 41.2MB도 데이터가 아니라 SEQ$ 갱신에서 나왔다. 데이터 블록은 직접 경로로 쓰고 NOARCHIVELOG라 redo를 거의 만들지 않으므로, 남은 redo는 54,836번의 딕셔너리 갱신 몫이다. 고친 방법은 이관 동안 시퀀스를 CACHE 10000으로 만드는 것이다. 번호 1만 개를 한 번에 꺼내 두므로 SEQ$ 갱신이 54,836번 → 110번으로 줄고, 경과는 47.6초 → 약 1.9초, redo는 41.2 → 0.3MB가 된다. 이관이 끝나면 새 판매 화면이 쓰기 좋은 크기(CACHE 1000)로 돌려 둔다. 대가는 빈 번호다. 캐시에 꺼내 둔 번호는 인스턴스를 재시작하거나 시퀀스가 공유 풀에서 밀려나면 사라지므로 최대 9,999개가 빈 번호가 될 수 있다. SLE_ITM_ID는 대리키라 빈 번호를 허용한다고 업무가 확인했으므로 감수할 수 있다. 시퀀스 없이 SELECT ROWNUM, …으로 번호를 매기고 적재 뒤 시퀀스를 1,096,709부터 시작하게 만드는 방법도 있다. 이때는 SEQ$ 갱신이 아예 없다.

해설

함정: "번호가 빠지면 안 되니 NOCACHE" 같은 판단을 이관에 옮기면 110만 번 SEQ$를 고친다. 그리고 NOCACHE도 ROLLBACK되면 빈 번호를 만든다(LOCK-010).
단일 인스턴스에서 ORDER는 성능 차이가 거의 없지만 RAC에서는 노드끼리 순서를 맞추느라 비싸다. 이관 시퀀스에는 필요 없다.
화면 하나가 한 건씩 받는 시퀀스는 동시 세션 수와 초당 건수로 CACHE를 정하고(LOCK s1), 배치가 한 번에 수십만 개를 받는 시퀀스는 받는 개수로 정한다. 같은 시퀀스를 둘이 쓰면 배치 앞뒤로 CACHE를 바꾸는 이 문제의 방법이 단순하다.
ALTER SEQUENCE … CACHE는 DDL이라 그 시퀀스를 쓰는 커서가 무효가 된다. 화면이 쓰기 전에 바꾼다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황