서술형문제 6 야간배치 SQL 튜닝

주문, 배송, 고객 정보를 읽어 주문배송 테이블에 입력하는 야간 배치(Batch) 프로그램을 튜닝하려고 한다. 대상 주문 데이터는 2016년 6월부터 8월까지 3개월치다. 월별 주문건수는 1,000만건이다. 월별 배송건수는 900만건이다. 배송은 주문이 완료된 후에 시작된다. 고객 수는 500만명이다. 

 

<파티션 구성>

주문 : 주문인자 기준 월단위 Range 파티션

배송 : 배송일자 기준 월단위 Range 파티션

 

<인덱스 구성>

주문 테이블

주문_PK : 주문번호

주문_N1 : 주문상태코드 + 주문일자 (Local Partition)

주문_N2 : 주문고객번호 + 주문일자 (Local Partition)

 

배송 테이블

배송_PK : 배송번호

배송_N1 : 주문번호 + 배송일자 (Local Partition)

배송_N2 : 배송일자 + 배송상태코드 (Local Partition)

 

고객 테이블

고객_PK : 고객번호

고객_N1 : 고객명 + 고객번호

 

 

아래 병렬 SQL과 예상실행계획을 분석해 가장 빠르게 수행할 수 있도록 SQL을 재작성하시오.

- 옵티마이저 힌트 변경이 필요하면 SQL문장에 의해 정확히 기술

- 야간 배치용 SQL이므로 다른 트랜잭션에 의한 동시 DML 없음

- 세션 파라미터 변경이 필요하면, 설정 값 제시

- 인덱스 구성 변경이 필요하면, 변경값 제시

- 파티션 구성은 변경 불가

- 시스템 운영정책상 허용된 최대 Parallel Degree = 4

 

SQL

insert into 주문배송 t
select /*+ leading(o) use_nl(d) index(d) full(o) parallel(o 4) */
o.주문번호, o.주문일자, o.주문상품수,o.주문상태코드,o.주문고객번호
,(select 고객명 from 고객 where 고객번호 = o.주문고객번호) 고객명
,d.배송번호,d.배송일자,d.배송상태코드,d.배송업체번호,d.배송기사연락처
from 주문 o, 배송 d
where o.주문일자 between '20160601' and '20160831'
adn o.주문번호 = d.주문번호

실행계획
-----------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Pstart | Pstop | TQ   | IN-OUT |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |      |    30M |      |        |      |        |
|   1 |  LOAD TABLE CONVENTIONAL           |주문배송  |       |       |        |      |        |
|   2 |   TABLE ACCESS BY INDEX ROWID      | 고객   |     1 |       |        |      |        |
|  *3 |    INDEX UNIQUE SCAN               | 고객_PK|     1 |       |        |      |        |
|   4 |   PX COORDINATOR                   |      |       |       |        |      |        |
|   5 |    PX SEND QC (RANDOM)             |:TQ10000|  30M|       |        | Q1,00|   P->S |
|   6 |     NESTED LOOPS                   |      |    30M|       |        | Q,100|   PCWP |
|   7 |      NESTED LOOPS                  |      |    30M|       |        | Q,100|   PCWP |
|   8 |       PX BLOCK ITERATOR            |      |    30M|    62 |     64 | Q,100|   PCWP |
|  *9 |        TABLE ACCESS FULL           | 주문   |    30M|    62 |     64 | Q,100|   PCWP |
|  10 |       PARTITION RANGE ALL          |      |      1|     1 |     65 | Q,100|   PCWP |
| *11 |        INDEX RANGE SCAN            |배송_N1 |      1|     1 |     65 | Q,100|   PCWP |
|  12 |       TABLE ACCESS BY LOCAL INDEX ROWID|배송  |    1|     1 |      1 | Q,100|   PCWP |
-----------------------------------------------------------------------------


Predicate Information (identified by operation id):
----------------------------------------------------------

3 - accesss("고객번호"=:B1)
9 - filter("O"."주문일자"<='20160831')
11 - access("O"."주문번호"="D"."주문번호")

 

Tuning Point

 

1.다른 트랙잭션에 의한 동시 DML이 없는 야간 배치용 SQL이므로 병렬 DML 활용이 가능합니다. 병렬로 Insert 하려면 우선 아래와 같이 Parallel DML을 활성화해야 합니다

alter session enable parallel dml;

 

Oracle 11gR2부터는 enable_pallel_dml 힌트도 제공됩니다.

파라미터를 활성화한 상태에서 insert 바로 뒤에 parallel 힌트를 추가하면 됩니다.

3,000만건 정도라면 Direct Path Load 기능만 활용해도 충분히 빠르게 Insert할 수 있습니다. Direct Path Load 기능을 사용하려면 insert 바로 뒤에 append 힌트를 추가하면 됩니다. parellel insert는 따로 append힌트를 추가하지 않아도 기본적으로 Direct Path Load방식으로 작동합니다.

 

2.인덱스를 이용한 NL조인은 소량 데이터를 조인하는데 적합합니다. 수십만 건 이상 데이터를 조인할 때는 캐시히트율이 좋지 않는 한 결고 빠른 성능을 기대할 수 없습니다. 3,000만건에 이르는 데이터를 조인하면서 캐시히트율이 좋기를 기대할 수는 없습니다. 따라서 주문,배송을 Full scan과 해시 조인으로 유도해야 합니다.

특히 주문 테이블은 주문일자 기준으로 월단위 Range 파티션이 된 상태입니다. 다른 조건절 없이 주문일자(파티션키)조건만으로 3개월치를 조회하는 데 인덱스를 이용할 이유가 없습니다. (파티션되어서 해당 월로 모여있기 때문입니다)

 

3. 배송 테이블은 배송일자 기준으로 Range 파티션된 상태인데, 배송일자가 조건절에 없습니다. 따라서 Full SCAN으로 처리한다면 전체 파티션을 읽어야 합니다. 3,000만건 조인하기 위해서 수십억 건을 읽어야 할 수도 있습니다.

실행계획 10번 라인 PARTTITION RANGE ALL을 통해 이 사실을 알 수 있습니다.

배송은 주문이 완료된 후에 시작된다는 데서 힌트를 얻을 수 있습니다.

배송일자는 주문일자보다 크고 주문일자는 '20160601'보다 큽니다. 따라서 배송일자도 '20160601'보다 큽니다.

배송일자로 조건절을 추가해주면 전체 파티션을 읽지 않아도 됩니다.

설령 이 조건절을 추가하지 않아 전체 파티션을 읽더라도 인덱스를 이용한 NL조인보다 Full SCAN과 해시조인이 빠릅니다.

 

4.스칼라 서브쿼리는 NL조인과 같은 방식으로 작동합니다. 스칼라 서브쿼리는 입력 값과 결과 값을 PGA에 캐싱한다는 점이 다릅니다. 따라서 입력 값 종류가 적을 때 실제 조인 횟수를 줄여줌으로써 빠른 성능을 기대할 수 있습니다.

여기서는 고객 수가 500만명이라고 명시했습니다. 스칼라 서브쿼리 캐싱효과가 도움이 되지 않는 상황입니다(고객수가 많아서)

따라서 고객에 대한 스칼라 서브쿼리를 일반 조인문으로 변경한 후 해시 조인으로 유도해야 합니다.

 

5.해시조인이라고 해서 항상 FULL SCAN으로 처리해야 하는것은 아니지만 고객 테이블에는 조인 외에 다른 조건절이 없으므로 FULL SCAN을 피할 수 없습니다.

그리고 고객명과 고객번호만 읽으면 되는 상황이므로 테이블 전체를 스캔할 필요없이 고객_N1 인덱스를 Fast Full Scan 방식으로 처리하면 됩니다. 인덱스 전체를 스캔하므로 인덱스 컬럼순서 변경을 불필요합니다.

 

6.온라인 트랜잭션이 없는 야간 배치용 SQL이고, 3,000만건에 이르는 대용량 데이터를 조인해야 하므로 병렬처리를 활용하지 않을 이유가 없습니다. 주문,배송 테이블에 parallel 힌트를 사용하면 됩니다. 고객 테이블은 인덱스만 읽도록 유도했으므로 병렬 처리를 위해 parallel_index힌트를 사용해야 합니다.

(최근 버전에서는 테이블이나 인덱스명을 지정하지 않고 Drgree만 지정한 parallel 힌트를 사용할 수 있습니다.

 

정답

alter session enable parallel dml;

 

insert /*+ parallel(t 4) */ into 주문배송 t
select /*+ leading(d) use_hash(o) use_hash(c) full(d) full(o) index_ffs(c) parallel(d 4) parallel(o 4) parallel_index(c 4) */
o.주문번호, o.주문일자, o.주문상품수,o.주문상태코드,o.주문고객번호,c.고객명
,d.배송번호,d.배송일자,d.배송상태코드,d.배송업체번호,d.배송기사연락처
from 주문 o, 배송 d ,고객 c
where o.주문일자 between '20160601' and '20160831'
and o.주문번호 = d.주문번호

and o.주문고객번호 = c.고객번호

and d.배송일자 >= '20160601'

 

이번문제는 풀면서 거의 다 틀렸던 문제입니다. 문제집의 마지막 문제인 만큼 어려운 문제였습니다.

지문의 힌트들을 거의 다 사용하면서 풀어야 하는 SQL이었습니다.

 

그리고 해시조인의 순서는 작은집합 -> 큰집합 순으로 되게 작성하면 됩니다 배송이 900만건 주문이 1,000만건이므로 d -> o 순이 되게 작성하였습니다.

 

그리고 해시조인은 수행빈도가 낮고 쿼리의 수행시간이 오래걸리는 대용량 SQL에서 사용 적합합니다.

해시조인은 조건절이 모두 equal(=)일때 사용하면 효과가 좋습니다.