DW로 넘기는 판매상품 파일 두 개, ARRAYSIZE를 올리자 한쪽만 논리 읽기가 줄었다

기초SELECT유통579 / 700

상황

유통 본사의 야간 스크립트는 SQL*Plus로 판매상품(CF11)을 파일 두 개로 내려 DW에 넘긴다. [E1]은 테이블 순서 그대로, [E2]는 DW 요청대로 상품번호 순으로 정렬한 파일이다. 둘 다 40초 안팎이 걸린다. DBA가 스크립트 맨 위에 SET ARRAYSIZE 1000 한 줄을 넣어 시험해 보니 둘 다 빨라졌다. 그런데 [E1]은 논리 읽기가 9분의 1로 줄었고 [E2]는 한 블록도 줄지 않았다. 조민우가 물었다. "같은 테이블을 같은 크기로 받았는데 왜 한쪽만 줄어요?"

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 통계 최신
스크립트는 배치 서버에서 돌고 DB 서버와의 왕복은 0.4ms다. 지금은 ARRAYSIZE를 정하지 않았다(SQL*Plus 기본값 15).
[E2]의 정렬은 메모리 안에서 끝난다(work area가 충분하다).

원본 SQL

sql

-- dw_cf11.sql (2014) : 배치 서버에서 sqlplus -s pup/***@DIST @dw_cf11.sql
SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 200 TRIMSPOOL ON TERMOUT OFF

SPOOL /dw/in/cf11_all.dat
-- [E1] 판매상품 전체 (순서 상관없음)
SELECT SLE_NO || '|' || ITM_NO || '|' || SLE_QTY || '|' || SLE_UPRC
  FROM CF11;
SPOOL OFF

SPOOL /dw/in/cf11_by_item.dat
-- [E2] 상품번호 순 (DW 적재 순서)
SELECT ITM_NO || '|' || SLE_NO || '|' || SLE_QTY || '|' || SLE_UPRC
  FROM CF11
 ORDER BY ITM_NO, SLE_NO;
SPOOL OFF
EXIT

실행계획 학습용 예시 · 정렬 유무에 따른 차이는 교재 09화 실측과 같은 모양

xplan

[지금 · ARRAYSIZE 15 (기본값) · 세션 통계 증가분]
          경과      SQL*Net roundtrips   consistent gets   sorts (memory)
  [E1]    38.2 초        73,117              81,329               0
  [E2]    41.6 초        73,117               8,207               1

[DBA 시험 · SET ARRAYSIZE 1000]
  [E1]     4.1 초         1,100               9,309               0
  [E2]     7.3 초         1,100               8,207               1

[E1 계획 · ARRAYSIZE 15]
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   1096K|00:00:01.02 |   81312 |
|   1 |  TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:01.02 |   81312 |
------------------------------------------------------------------------------------

[E2 계획 · ARRAYSIZE 15 (1000일 때도 Buffers는 같다)]
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1096K|00:00:03.12 |    8204 |          |
|   1 |  SORT ORDER BY     |      |      1 |   1096K|   1096K|00:00:03.12 |    8204 |   46M (0)|
|   2 |   TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.21 |    8204 |          |
-----------------------------------------------------------------------------------------------

문제

[E1]과 [E2]의 왕복 수가 둘 다 73,117인 이유와, ARRAYSIZE를 올렸을 때 [E1]만 consistent gets가 준 이유를 위 숫자로 설명하시오.
스크립트를 고치시오. [E2]의 consistent gets를 더 줄일 방법이 있는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- dw_cf11.sql : 한 번에 1,000행씩 받는다 (SQL*Plus 상한은 5000)
SET ARRAYSIZE 1000
SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 200 TRIMSPOOL ON TERMOUT OFF

SPOOL /dw/in/cf11_all.dat
SELECT SLE_NO || '|' || ITM_NO || '|' || SLE_QTY || '|' || SLE_UPRC
  FROM CF11;
SPOOL OFF

SPOOL /dw/in/cf11_by_item.dat
SELECT ITM_NO || '|' || SLE_NO || '|' || SLE_QTY || '|' || SLE_UPRC
  FROM CF11
 ORDER BY ITM_NO, SLE_NO;
SPOOL OFF
EXIT

개선 후 실행계획 학습용 예시 · 정렬 유무에 따른 차이는 교재 09화 실측과 같은 모양

xplan

[E1 · ARRAYSIZE 1000]
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   1096K|00:00:00.64 |    9301 |
|   1 |  TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.64 |    9301 |
------------------------------------------------------------------------------------

[E2 · ARRAYSIZE 1000]  계획은 그대로 · Buffers 8,204 · Used-Mem 46M (0)

[두 파일 · 세션 통계]
          경과     roundtrips   consistent gets
  [E1]    38.2 → 4.1 초   73,117 → 1,100    81,329 → 9,309
  [E2]    41.6 → 7.3 초   73,117 → 1,100     8,207 → 8,207

모범 답안 서술

CF11 1,096,708행을 SQL*Plus 기본값대로 15행씩 받으므로 페치가 약 73,114번이고, 페치 한 번이 왕복 한 번이라 두 파일 모두 왕복이 73,117번이다. 왕복 한 번에 0.4ms면 왕복만 29초다. 40초 안팎의 대부분이 DB 일이 아니라 오가는 데 쓰였다. [E1]은 정렬이 없는 FULL이라 오라클은 읽으면서 15행씩 바로 내준다(부분범위 처리). 15행을 내주고 멈췄다가 다음 페치에서 읽던 블록을 다시 잡으므로 페치마다 한 블록씩 더 읽는다. 81,329 − 8,204 = 73,125가 페치 수와 거의 같은 것이 그 흔적이다. ARRAYSIZE를 1000으로 올리면 다시 잡는 횟수가 1,097번으로 줄어 9,309가 된다. [E2]는 SORT ORDER BY가 첫 행을 내주기 전에 테이블 8,204블록을 다 읽고 정렬을 끝낸다. 그 뒤의 페치는 메모리에 있는 정렬 결과를 넘겨줄 뿐이라 블록을 다시 잡지 않는다. 그래서 ARRAYSIZE와 상관없이 8,207 그대로이고 바뀌는 것은 왕복뿐이다. 이 8,204는 테이블을 한 번 처음부터 끝까지 읽는 최소치라 블록은 더 줄일 거리가 없다. [E2]에서 더 줄일 것은 정렬 시간(약 3초)인데, DW가 적재하면서 정렬할 수 있다면 ORDER BY를 빼는 것을 DW와 다시 협의한다. 스크립트 맨 위에 SET ARRAYSIZE 1000을 넣는다. 왕복은 73,117 → 1,100이 되고 [E1]은 38.2 → 4.1초, [E2]는 41.6 → 7.3초가 된다. SQL*Plus의 상한은 5000이지만, 1,000을 넘기면 왕복은 1,100에서 거의 줄지 않고 한 번에 받는 행만큼 메모리만 커진다.

해설

같은 셈이 교재 09화에 있다. 6월 27,390건을 ARRAYSIZE 15로 받으면 왕복이 1,827번이고, 정렬이 없을 때만 consistent gets가 7,641 → 5,854로 줄었다(교재 09화 §11).
스크립트를 DB 서버 안에서 돌리면 왕복 한 번의 지연은 줄지만 횟수는 그대로다. 횟수를 먼저 줄인다.
[E2]의 정렬을 인덱스로 대신하려면 (ITM_NO, SLE_NO) 순서의 인덱스가 있어야 한다. 그러면 정렬은 사라지지만 110만 행을 인덱스 순서로 테이블에 찾아가므로 FULL 8,204블록보다 훨씬 많이 읽는다. 전부 내려받는 파일에서는 FULL과 정렬이 맞다.
배치 단계의 경과 시간과 세션 통계의 roundtrips를 함께 적어 두자. SQL*Net message from client는 Idle 대기라 DB 쪽 보고서에서는 잘 보이지 않는다(교재 15화 T3 리포트의 호출 통계 칸).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 정렬 유무에 따른 차이는 교재 09화 실측과 같은 모양 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 정렬 유무에 따른 차이는 교재 09화 실측과 같은 모양 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황