5분마다 도는 판매 변경분 연계가 바뀐 판매 몇 건을 보내려고 판매 테이블을 다 읽는다

중급INSERT유통34 / 700

상황

유통 본사는 판매가 등록되거나 고쳐지면 5분 안에 정보계로 보낸다. 연계 솔루션이 5분마다 아래 INSERT를 돌려 변경분을 연계 테이블에 담고, 다 보낸 뒤 제어 테이블의 마지막 연계 시각을 고친다. 변경분은 한 번에 몇 건뿐인데 이 INSERT가 연계 시간의 대부분을 차지한다. 지난달 DBA가 MOD_DTT에 인덱스를 만들었지만 계획은 그대로다. 개발사는 "SQL에 함수도 형변환도 없다"라고 한다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,511) · MOD_DTT DATE(수정 일시) · 5분 동안 바뀌는 판매는 보통 2 ~ 10건 (문제 설정: 교재의 CF10은 적재 때 MOD_DTT가 한꺼번에 찍혔다. 여기서는 판매를 등록하거나 고칠 때마다 시각이 찍히는 운영 테이블이라고 본다)
인덱스 IX_CF10_MOD (MOD_DTT): 지난달 생성 · BLEVEL 2 · 리프 약 1,330블록 · 판매가 바뀐 순서와 행이 놓인 순서가 비슷해 CF가 좋다 · 통계 최신
IF_CTL 연계 제어(연계 솔루션이 만든 테이블): IF_ID VARCHAR2(10) PK · LAST_TS TIMESTAMP(0) (마지막으로 보낸 시각, 소수 초 없음) · 연계마다 한 행
IF_SLE_OUT 연계 테이블: 보낼 판매를 담고, 보낸 뒤 비운다

원본 SQL

sql

INSERT INTO IF_SLE_OUT (SLE_NO, SLE_DT, SHP_NO, MOD_DTT)
SELECT a.SLE_NO, a.SLE_DT, a.SHP_NO, a.MOD_DTT
  FROM CF10 a
 WHERE a.MOD_DTT > (SELECT c.LAST_TS FROM IF_CTL c WHERE c.IF_ID = 'SLE')

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |            |      1 |        |      0 |00:00:00.07 |    4528 |
|   1 |  LOAD TABLE CONVENTIONAL      | IF_SLE_OUT |      1 |        |      0 |00:00:00.07 |    4528 |
|*  2 |   TABLE ACCESS FULL           | CF10       |      1 |  25000 |      6 |00:00:00.07 |    4513 |
|   3 |    TABLE ACCESS BY INDEX ROWID| IF_CTL     |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN         | IF_CTL_PK  |      1 |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(INTERNAL_FUNCTION("A"."MOD_DTT")> (SELECT "C"."LAST_TS" FROM "IF_CTL" "C" WHERE "C"."IF_ID"='SLE'))
   4 - access("C"."IF_ID"='SLE')

문제

SQL에 함수가 없는데도 2번 줄에 INTERNAL_FUNCTION이 붙은 이유를 테이블 정의로 설명하시오.
IX_CF10_MOD로 변경분만 찾아가도록 SQL을 고치고, 고친 SQL이 원본과 같은 판매를 고르는지 소수 초 관점에서 따져 설명하시오.
연계 솔루션 쪽에서 할 수 있는 근본 대책을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 서브쿼리 값(TIMESTAMP(0))을 DATE로 바꿔 비교한다 · 컬럼 MOD_DTT는 그대로 → IX_CF10_MOD 범위 스캔
--   LAST_TS는 소수 초가 없으므로 DATE로 바꿔도 값이 같다 (고르는 판매도 같다)
INSERT INTO IF_SLE_OUT (SLE_NO, SLE_DT, SHP_NO, MOD_DTT)
SELECT a.SLE_NO, a.SLE_DT, a.SHP_NO, a.MOD_DTT
  FROM CF10 a
 WHERE a.MOD_DTT > (SELECT CAST(c.LAST_TS AS DATE) FROM IF_CTL c WHERE c.IF_ID = 'SLE')

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |             |      1 |        |      0 |00:00:00.01 |      21 |
|   1 |  LOAD TABLE CONVENTIONAL              | IF_SLE_OUT  |      1 |        |      0 |00:00:00.01 |      21 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | CF10        |      1 |  25000 |      6 |00:00:00.01 |      10 |
|*  3 |    INDEX RANGE SCAN                   | IX_CF10_MOD |      1 |  25000 |      6 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID       | IF_CTL      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN                | IF_CTL_PK   |      1 |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."MOD_DTT"> (SELECT CAST("C"."LAST_TS" AS DATE) FROM "IF_CTL" "C" WHERE "C"."IF_ID"='SLE'))
   5 - access("C"."IF_ID"='SLE')

모범 답안 서술

SQL에는 함수가 없지만 비교하는 두 값의 타입이 다르다. 왼쪽 a.MOD_DTT는 DATE이고, 오른쪽 서브쿼리가 돌려주는 c.LAST_TS는 연계 솔루션이 만든 TIMESTAMP(0) 컬럼이다. DATE와 TIMESTAMP를 비교하면 오라클은 DATE 쪽을 TIMESTAMP로 바꾸므로, 판매 테이블의 컬럼 MOD_DTT가 INTERNAL_FUNCTION으로 감싸졌다. 그래서 IX_CF10_MOD에서 범위를 지목하지 못하고, 5분마다 판매 50만 건을 다 읽어 몇 건을 고른다(Buffers 4,528). 하루 288번이면 130만 블록이다. 서브쿼리 쪽 값을 CAST(c.LAST_TS AS DATE)로 바꾸면 비교가 DATE끼리가 되어 컬럼은 그대로 남는다. 서브쿼리는 한 번만 계산되어 범위의 시작점이 되고, IX_CF10_MOD에서 마지막 연계 이후에 바뀐 판매만 찾아간다(Buffers 4,528 → 21). 고르는 판매도 같다. LAST_TS는 TIMESTAMP(0)이라 소수 초가 없으므로 DATE로 바꿔도 값이 그대로다. 만약 LAST_TS에 소수 초가 있었다면, DATE로 바꿀 때 초 단위로 맞춰지면서 경계의 1초 안에 바뀐 판매가 빠지거나 두 번 보내질 수 있어 따로 따져야 한다. 근본 대책은 연계 솔루션 쪽이다. 제어 시각을 DATE 컬럼에 SYSDATE로 기록하게 하면 SQL에 손대지 않아도 된다. 더 나은 방법은 연계를 마친 시각 대신 이번에 보낸 판매의 MOD_DTT 최댓값을 기록하는 것이다. 그러면 비교하는 두 값의 타입이 같아지고, INSERT가 도는 동안 바뀐 판매를 다음 회차에서 놓치는 일도 줄어든다.

해설

SQL 문장만 봐서는 안 보이는 형변환: 바인드 타입(IDX-022, IDX-023)처럼, 비교 상대가 다른 테이블의 컬럼이면 그 컬럼의 타입이 변환을 정한다. 조건 양쪽의 타입을 테이블 정의에서 확인한다.
서브쿼리 값은 실행 전에 알 수 없어서 E-Rows가 5%(25,000)로 잡힌다. 그래도 IX_CF10_MOD의 CF가 좋아 옵티마이저는 인덱스를 고른다. 계획이 흔들린다면 연계 솔루션이 시각을 먼저 읽어 DATE 바인드로 넘기게 한다.
5분 주기 연계처럼 자주 도는 SQL은 한 번에 읽는 양이 작아 보여도 하루 합계로 본다. 4,528블록 × 288번은 하루 130만 블록이고, 고친 뒤에는 6천 블록 남짓이다.
연계 솔루션이 만든 테이블이라 컬럼 타입을 직접 바꾸기 어렵다면, 고친 SQL을 연계 솔루션의 SQL 설정에 넣는 것이 가장 빠른 길이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황