감사용 판매상품 원장 추출이 정렬 때문에 TEMP를 46MB 쓰며 5초 가까이 첫 줄을 내지 못한다

실전SELECT유통123 / 700

상황

외부 감사인이 2년 치 판매상품 원장을 판매번호, 상품번호 순으로 달라고 했다. 감사 기간에는 하루에도 몇 번씩 조건을 바꿔 다시 뽑는다. 추출 프로그램은 결과를 받는 대로 파일에 쓰는데, 정렬이 끝날 때까지 첫 줄이 나오지 않아 프로그램이 수 초 동안 멈춘 듯 보인다. 배치 담당 오과장의 조건은 이렇다. "낮에는 POS 판매 등록이 같은 서버에서 돈다. TEMP를 많이 쓰거나 병렬로 도는 방법은 피하고 싶다."

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · 판매번호 순으로 쌓였다 · 컬럼은 모두 NOT NULL
인덱스 CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · CLUSTERING_FACTOR 8,211(테이블 블록 수와 거의 같다) · 통계 최신
세션의 정렬 작업 영역은 약 20MB까지다(PGA 자동 관리) · 원장 한 줄은 약 40바이트라 110만 줄이면 정렬할 양이 약 46MB다
POS 판매 등록은 판매마다 CF11에 INSERT한다

원본 SQL

sql

SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC
  FROM CF11
 ORDER BY SLE_NO, ITM_NO

실행계획 학습용 예시 · CF11_PK의 CLUSTERING_FACTOR는 판매번호 순 적재를 전제로 한 값

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1096K|00:00:04.71 |    8204 | 13996 |   5792 |          |          |
|   1 |  SORT ORDER BY     |      |      1 |   1096K|   1096K|00:00:04.71 |    8204 | 13996 |   5792 | 2241K (1)|    46M   |
|   2 |   TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.62 |    8204 |  8204 |      0 |          |          |
----------------------------------------------------------------------------------------------------------------------------

[세션 통계] 첫 행이 나오기까지 4.1초 · sorts (disk) 1 · physical writes direct temporary tablespace 5,792

문제

4.71초가 어디서 나오는지 실행계획의 Reads, Writes, Used-Tmp로 설명하시오.
대안 넷을 비교하시오. ① PK로 정렬을 대신한다(INDEX FULL SCAN) ② 작업 영역을 키워 메모리에서 정렬한다 ③ 병렬로 읽고 정렬한다 ④ 원장 컬럼을 다 담은 인덱스를 만든다. 각각의 Buffers, TEMP, 첫 줄이 나오는 시점, 오과장의 조건에 맞는지를 쓰시오.
하나를 골라 SQL을 작성하고, 그 방법이 기대는 전제와 그 전제가 깨지는 경우를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① PK 순서 = 정렬 순서 · 테이블도 PK 순서로 쌓여 있다 (CF 8,211) → 정렬 · TEMP 없이 바로 흘려보낸다
SELECT /*+ INDEX(b CF11_PK) */
       b.SLE_NO, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF11 b
 ORDER BY b.SLE_NO, b.ITM_NO;
-- 전제: CF11_PK 의 CLUSTERING_FACTOR 가 테이블 블록 수 근처일 것 (힌트 대장에 적고, 재적재 · 이관 뒤 다시 확인)

개선 후 실행계획 학습용 예시 · CF11_PK의 CLUSTERING_FACTOR는 판매번호 순 적재를 전제로 한 값

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |   1096K|00:00:01.93 |   12504 | 12481 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CF11    |      1 |   1096K|   1096K|00:00:01.93 |   12504 | 12481 |
|   2 |   INDEX FULL SCAN           | CF11_PK |      1 |   1096K|   1096K|00:00:00.48 |    4293 |  4290 |
-------------------------------------------------------------------------------------------------------

[세션 통계] 첫 행 0.01초 · sorts (disk) 0 · TEMP 0

모범 답안 서술

2번 줄 FULL은 8,204블록을 0.62초에 읽었다. 나머지 4초 남짓은 1번 줄 정렬에서 나온다. 정렬할 양이 약 46MB인데 작업 영역은 약 20MB라서, 다 담지 못한 부분을 TEMP에 써 두고(Writes 5,792) 다시 읽어(Reads 13,996 가운데 5,792) 합쳤다. Used-Mem의 (1)은 한 번 나눠 정렬한 one-pass라는 뜻이다. 정렬은 모든 행을 다 받아야 첫 줄을 낼 수 있으므로, 추출 프로그램은 4초 넘게 첫 줄을 기다렸다. 대안을 비교한다. ① PK로 정렬을 대신한다: CF11_PK는 (SLE_NO, ITM_NO)로 정렬 키와 같고 컬럼이 NOT NULL이라 모든 행이 들어 있다. INDEX FULL SCAN으로 키 순서대로 읽으면 정렬이 필요 없다. 걱정은 행마다 테이블로 가는 것인데, 테이블이 판매번호 순으로 쌓여 CF가 8,211(테이블 블록 8,204와 거의 같다)이다. 인덱스 순서대로 찾아가도 블록을 8,211번만 바꿔 타므로 4,293 + 8,211 = 12,504블록이다. Buffers는 FULL보다 4,300블록 많지만 TEMP를 쓰지 않고 첫 줄이 바로 나온다(4.71 → 1.93초). 오과장의 조건에 맞는다. 대가는 블록을 한 블록씩 읽는다는 것이고, INDEX FULL SCAN은 병렬로 나눌 수도 없다. ② 작업 영역을 키운다(세션을 수동 관리로 바꾸고 SORT_AREA_SIZE를 64MB 이상으로): 메모리 안에서 정렬해 TEMP가 사라지고 약 2초가 된다. 하지만 첫 줄은 여전히 정렬이 끝난 뒤에 나오고, 낮 시간 서버 메모리를 한 세션이 64MB 넘게 잡는다. 감사 기간에 여러 번 뽑으면 그만큼 겹친다(교재 09화 §6). ③ 병렬(DOP 4)로 읽고 정렬한다: 시간은 줄지만 PX 서버 8개와 서버마다의 정렬 영역을 쓰고, 결과를 순서대로 받으려면 마지막에 코디네이터 한 곳으로 모아야 해서 파일 쓰기는 여전히 한 줄씩이다. 오과장의 조건에 어긋난다. ④ 원장 컬럼을 다 담은 인덱스 (SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC): INDEX FULL SCAN 하나로 끝나(리프 약 6,000블록) 가장 싸다. 하지만 감사 때 쓰는 추출 하나 때문에 110만 행 인덱스를 두면 POS 판매 등록마다 인덱스를 하나 더 고쳐야 하므로 과하다. ①을 고른다. 이 방법이 기대는 전제는 'CF11이 판매번호 순으로 쌓여 있다'는 것이다. 서버 교체나 이관 때 ORDER BY 없이 다시 적재하면(버거원 ORD_RANDOM처럼) CF가 행 수 근처로 나빠지고, 같은 힌트가 110만 번의 랜덤 액세스(약 110만 블록)로 바뀐다. 그래서 힌트 대장에 이 전제와 확인 방법(USER_INDEXES의 CLUSTERING_FACTOR가 테이블 블록 수 근처인지)을 적고, 재적재와 이관 절차서에 판매번호 순 적재를 넣는다.

해설

FFS로는 이 일을 할 수 없다. 블록이 놓인 순서대로 읽어 키 순서를 보장하지 않으므로 정렬이 다시 붙는다. 정렬을 대신하는 것은 리프를 키 순서로 따라가는 INDEX FULL SCAN뿐이다.
같은 INDEX(b CF11_PK) 힌트가 건수를 셀 때는 나쁜 선택이다(ACC-035). 순서가 필요 없는 일에 INDEX FULL SCAN을 쓰면 순서를 지키는 비용(한 블록씩 읽기)만 치른다. 스캔 방식은 그 SQL에 순서가 필요한지로 고른다.
감사인에게 정렬된 파일이 꼭 필요한지 묻는 것도 방법이다. 받는 쪽에서 정렬할 수 있다면 FULL로 8,204블록만 읽어 흘려보내면 된다(교재 09화 화면 요구 재협상서).
테이블이 정렬 키 순서로 쌓여 있으면 인덱스 순서로 찾아갈 때 오라클이 이웃 블록을 미리 묶어 읽기도 해서, 실제 I/O 요청은 블록 수보다 적어질 수 있다(교재 04화 §2의 ORD).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · CF11_PK의 CLUSTERING_FACTOR는 판매번호 순 적재를 전제로 한 값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · CF11_PK의 CLUSTERING_FACTOR는 판매번호 순 적재를 전제로 한 값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황