🖥️ 들어가며
📌 배치 프로그램이란
- 사용자와 상호작용 없이 대량의 데이터를 처리하는 일련의 작업들을 묶어
- 정기적으로 반복수행하거나 정해진 규칙에 따라 자동수행하는 프로그램을 말합니다.
📌 특징에 따른 배치 프로그램 구분
- 정기 배치: 정해진 시점에 실행 (주로 야간)
- 이벤트성 배치: 사전에 정의해 둔 조건에 충족될 경우 자동 수행
- On-Demand 배치: 사용자의 명시적인 요구가 있을 때마다 실행
✏️ 1. 배치 프로그램 튜닝 개요
- 배치 환경 변화
- 과거: 일(Daily)/월(Monthly) 배치 작업 위주, 야간에 생성된 데이터 → 주간 업무에 활용, 온라인과 배치 프로그램 구분이 비교적 명확했습니다.
- 현재: 시간(Houly)/분(Minutely) 배치 작업, On-Demand 배치를 제한적이나마 허용합니다.
- 성능 개선 목표 설정
- 온라인 프로그램(OLTP): 전체 처리속도 또는 최초 응답속도 최적화를 목표로 설정합니다. (ALL ROWS/FIRST ROWS)
- 배치 프로그램: 항상 전체 처리속도 최적화를 목표로 설정합니다.
- 배치 프로그램 구현 패턴과 튜닝 방안
- 절차형 프로그램
- Application의 커서를 열고 → 루프 내에서 다른 SQL 반복 처리 형태
- 반복적인 DBMS Call 발생, Random Access 위주, 동일 데이터를 중복 액세스
- 집합적(=One SQL) 프로그램
- One SQL로 구성하거나 집합적으로 정의된 여러 SQL을 단계적으로 실행합니다.
- RDB는 집합적으로 처리해야 성능이 좋습니다.
- 성능 측면에선 One SQL 위주의 프로그램이 월등하나,
⚠️ 변경이 쉽지 않고, 데이터가 다르게 나올 수 있으므로 주의해야 합니다.
- 절차형 프로그램
✏️ 2. 병렬 처리 활용
- 병렬 처리: SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스(또는 스레드)가 동시에 처리하는 것을 의미합니다.
- 여러 프로세스가 동시에 작업하므로 대용량 데이터 처리 시, 수행 속도를 극적으로 단축시킬 수 있습니다.
| 대분류 | 중뷴류 | PQ_DISTRIBUTE (분배) 방식 |
| Single(단일) 테이블 | 일반적인 병렬 | - |
| SORT(Order By) 또는 Group By 병렬 | Ragne / Hash | |
| 병렬 조인 | Partitioned Table - Full Partition Wise Join - Partial Partition Wise Join |
- Key |
| Non Partitioned Table - Dynamic Partiton Wise Join |
Hash / Broadcast |
|
| Parallel - Non Parallel Join | Hash / Broadcast |
| PQ_DISTRIBUTE 분배 방식 | 분배 방식별 특징 |
| Range | 값의 범위에 따른 분배 |
| Hash | 해시 함수를 통한 균등 분배 |
| Broadcast | 모든 프로세스에 동일 데이터 전송 |
| Key (=Partition) | 특정 키 값을 기준으로 분배 |
| Round-Robin | 순차적 순환 분배 |
✏️ 2-1. Single 테이블
📍 SQL을 보고 → 아래와 같은 그림을 그리고 → 실행계획을 유추할 수 있어야 합니다.
📍 Server Process의 개수
- PtoP 有: Parallel Degree * 2배
- PtoP 無: Parallel Degree 만큼만
- Single 테이블 예시 (1) 일반적인 병렬
- Parallel (A 2): 테이블을 2개의 병렬 프로세스로 처리 필요
- 테이블에서 읽은 Data → 1:1 매칭되는 Server Process에 보내고
- P001, P002 두 Server Process는 병렬로 Data를 읽고 → QC에게 전달합니다.
- P: Parallel Process
S: Single Process - ⭐⭐⭐ Single 테이블의 일반적인 병렬 SQL에서는 PtoS가 발생합니다. (PtoP 미발생)
- Single 테이블 예시 (2) SORT(Order By) 또는 Group By 병렬
- Parallel (A 2): 테이블을 2개의 병렬 프로세스로 처리 필요
- Order By SAL: 정렬해야 하므로 테이블에서 읽은 Data → Data가 속해야 하는 Server Process에 보내야 합니다.
- 즉, P001에서 읽을 데이터 중 1~50 사이는 P003으로 / 51~100 사이는 P004로 보내야 합니다.
- P001, P002 두 Server Process는 병렬로 Data를 읽고 → Data를 Server Process로 보내고(이때 교차(Cross)데이터 이동 발생 가능)* → P003, P004에서 각 1~50 / 51~100 정렬한 뒤 → QC로 전달합니다.
- *PQ_DISTRIBUTE
- Data를 Server Process로 재분배하는 방식은 다음과 같습니다.
- Range / Hash / Broadcast / Key / Round-Robin
- ⭐⭐⭐ Single 테이블의 SORT(Order By) 또는 Group By 병렬에서는 PtoP, PtoS가 발생합니다.
✏️ 2-2. 병렬 조인 유형
- 종류: Full/Partial/Dynamic Partition Wise Join
- Full Partition Wise Join: 두 테이블 모두 Join 컬럼을 기준으로 Partitioning 되어 있는 경우
- Partial Partition Wise Join: 하나의 테이블만 Join 컬럼을 기준으로 Partitioning 되어 있는 경우
- Dynamic Partition Wise Join:두 테이블 모두 Join 컬럼을 기준으로 Partitioning 되어 있지 않은 경우
- Full Partition Wise Join
- Full Partition Wise Join 예시 (1)
- Parallel(E 2) Parallel(D 2): E테이블과 D테이블 각 2개 병렬 프로세스 필요
- Full(E) Full(D), where E.deptno = D.deptno and E.in_dt = D.in_DT
- 두 테이블이 조인 컬럼(deptno) 기준으로 파티션되어 있으므로
- 각 테이블의 1번 파티션에서 읽은 Data → P001 Server Process로
- 각 테이블의 2번 파티션에서 읽은 Data → P002 Server Process로 보내고
- P001, P002 → QC로 전달합니다.
- P: Parallel Process
S: Single Process - ⭐⭐⭐ Full Partition Wise Join에서 PtoS가 발생합니다. (PtoP 미발생)
- Partial Partition Wise Join
- Partial Partition Wise Join (1)
- Parallel(E 2) Parallel(D 2): E테이블과 D테이블 각 2개 병렬 프로세스 필요
- Full(E) Full(D), where E.deptno = D.deptno and E.in_dt = D.in_DT
PQ_DISTRIBUTE(E PARTION NONE)
- E: inner 테이블
- PARTITON: outer 테이블인 D의 distribution 방식이 Partition
- NONE: inner 테이블인 E의 distribution 방식은 None
- outer 테이블인 D(Dept)가 Partiton 기준으로 분배해야 합니다.
- D 테이블: 2개의 병렬 프로세스로 Data를 읽고 → 각 P003, P004 Server Process로
E 테이블:
1번 파티션에서 읽은 데이터 → P001 Server Process로,
2번 파티션에서 읽은 데이터 → P002 Server Process로 보내고 - D 테이블을 읽은 P003, P004 Server Process는 Partition 기준으로 재분배해야 하기 때문에
→ P003, P004의 Data를 특정 키 값을 기준으로
→ E 테이블을 읽은 P001, P002에게 재분배합니다. (이때 데이터 교차이동 발생 가능)
- P001, P002 → QC로 전달합니다.
- ⭐⭐⭐ Partial Partition Wise Join에서 PtoP, PtoS가 발생합니다.
- Full Partition Wise Join (이어서)
- Full Partition Wise Join 예시 (2)
- Parallel(E 2) Parallel(D 2): E테이블과 D테이블 각 2개 병렬 프로세스 필요
- PQ_DISTRIBUTE(E HASH HASH)
- E: inner 테이블
- HASH: outer 테이블인 D의 distribution 방식이 Hash이고
- HASH: intter 테이블인 E의 distribution 방식이 Hash입니다.- D 테이블의 1번 파티션에서 읽은 Data → P001
D 테이블의 2번 파티션에서 읽은 Data → P002 - E 테이블의 1번 파티션에서 읽은 Data → P001
E 테이블의 2번 파티션에서 읽은 Data → P002 - P001, P002 Server Proccess의 데이터를 → P003, P004에 균등 분배합니다. (Hash)
- D 테이블의 1번 파티션에서 읽은 Data → P001
- 이후 P003, P004 Server Process → QC로 전달합니다.
- ⭐⭐⭐ Full Partition Wise Join - PQ_DISTRIBUTE(E HASH HASH): PtoP 2번, PtoS가 발생합니다.
- Partial Partition Wise Join (이어서)
- Partial Partition Wise Join 예시 (2)
- Parallel(E 2) Parallel(D 2): E테이블과 D테이블 각 2개 병렬 프로세스 필요
- PQ_DISTRIBUTE(E BROADCAST NONE)
- E: inner 테이블
- BROADCAST: outer 테이블인 D의 distribution 방식이 Broadcast (모든 프로세스에 동일 데이터 전송)
- NONE: inner 테이블인 E의 distribution 방식은 None
- D 테이블의 Data를 P001, P002에서 병렬로 읽고
- E 테이블의 1번 파티션 Data → P003에서 읽고
E 테이블의 2번 파티션 Data → P004에서 읽고 - P001, P002가 읽은 Data를 → 모든 프로세스(P003, P004)에 전송합니다. (이때 데이터 교차이동 발생 가능)
- 이후 P003, P004 → QC로 전달합니다.
- ⭐⭐⭐ Partial Partition Wise Join - PQ_DISTRIBUTE(E BROADCAST NONE): PtoP, PtoS 발생합니다.
✏️ 2-3. 병렬 조인 정리
| 구분 | 설명 |
| Full Partition Wise Join | - 두 테이블 모두 조인 컬럼 기준 파티셔닝 - 데이터 재분배 필요 (PtoP) |
| Partial Partition Wise Join | - 하나의 테이블만 조인 컬럼 기준 파티셔닝 - 비파티션 테이블을 → 파티션 테이블 기준으로 데이터 재분배 필요 |
| Dynamic Partition Wise Join | - 양쪽 모두 비파티션 테이블 - 주로 두 테이블 모두 대용량 - 임시 테이블 스페이스를 많이 사용 - 두 테이블 모두 전체 범위 처리 - 조인 컬럼의 데이터 분포가 균일해야 효과적 (Hash 파티셔닝 특징) - 조인 방식: NL, Sort Merge, Hash 모두 가능하나 주로 Hash 조인 수행 |
| Broadcast | - 한쪽 테이블의 데이터양이 매우 적은 경우 (불필요한 temp 공간 사용을 피하기 위함) - 큰 테이블은 부분범위 처리 가능 - Broadcast 후 조인 방식: NL, Sort Merge, Hash 모두 가능 |
- 병렬처리 활용
- Parallel + Full 힌트
- Parallel 힌트 사용 시, 반드시 Full 힌트도 함께 사용하는 습관 필요합니다.
- 옵티마이저가 Index Scan을 선택하면 Parallel 힌트는 무시되기 때문입니다.
- parallel_index + index_ffs 힌트
- parallel_index 힌트 사용 시, 반드시 index_ffs 힌트도 함께 사용하는 습관이 필요합니다.
- 옵티마이저가 Full Table Scan을 선택하면 Parallel 힌트는 무시되기 때문입니다.
- 병렬 DML
- 병렬 DML 수행 시, alter session enable parallel dml 사전 수행 필요합니다.
- Parallel + Full 힌트
-- Parallel + Full 힌트
SELECT /*+ full(o) parallel(o 4)*/
count(*) 주문건수, sum(주문수량) 주문수량, sum(주문금액) 주문금액
FROM 주문 o
WHERE 주문일시 between '20100101' and '20100131' ;
-- parallel_index + index_ffs 힌트
SELECT /*+ index_ffs(o 주문_idx) parallel_index(o 주문_idx 4)*/
count(*) 주문건수
FROM 주문 o
WHERE 주문일시 between '20100101' and '20100131';
-- 병렬 dml 수행 시, 사전 수행: alter session enable parallel dml
alter session enable parallel dml;
alter table t nologging;
INSERT /*+ parallel(t, 2)*/ INTO t
SELECT /*+ full(src) paralle(src, 2)*/
FROM src;
✏️ 3. Query Coordinator와 병렬 서버 프로세스
- QC
- Query Coordinator
- 병렬 SQL문을 발행한 세션입니다.
(Server Process가 하지 않는 일은 QC가 한다고 봐도 무방함) - QC의 역할
- 병렬도(DOP: Degree Of Parallelism), 오퍼레이션 종류에 따라 하나 또는 2개의 병렬 서버 집합(Server set) 할당
- 서버 풀(Parallel Executing Server Pool)로부터 필요한만큼 서버 프로세스를 확보 및 부족분 신규 생성
- 병렬 서버에 작업 할당
- 병렬로 처리하도록 사용자가 지시하지 않는 테이블은 QC가 직접 처리
- 각 병렬 서버로부터의 산출물을 통합하는 작업 (PtoS)
- 최종 결과집합을 사용자에게 전송, DML일 경우 갱신 건수 집계 및 사용자에게 전송
- Query 결과를 전송하는 단계에서 수행되는 스칼라서브쿼리도 QC가 수행
- Server Process
- 실제 작업을 수행하는 개별 세션입니다.
✏️ 4. 데이터 재분배
- 병렬 서버 프로세스간 데이터 재분배 방식 5가지
- 1. Range
- 2. Hash
- 3. Broadcast: Dynamic Partition Wise Join 또는 Partial Partition Wise Join 시 활용
- 4. Key: Partition Wise Join시 활용
- 5. Round-Robin: Insert SELECT시 활용
- Intra-Operation Parallelism과 Inter-Operation Parallelism
- Intra: 각 프로세스간 독립적으로 작업 발생 (프로세스간 통신 X)
- Inter: 각 프로세스간 통신 발생
- PtoP, StoP, PtoS
| 데이터 재분배 방식 | 상세 설명 |
| Range | 1. order by 또는 sort group by를 병렬로 처리할 때 사용합니다. 2. 정렬 작업을 맡은 두 번째 서버 집합의 프로세스별로 처리 범위를 지정합니다. 3. 예를 들어 A-G, H-M, H-S, T-Z를 지정한 뒤 → 데이터를 읽는 첫 번째 서버 집합이 → 두 번째 서버 집합의 정해진 프로세스에게 정렬 키 값에 따라 분배하는 방식입니다. 4. QC: 각 서버 프로세스에게 작업범위를 할당합니다. (정렬 작업에 직접 참여 X) → 정렬이 완료되고 나면 순서대로 결과를 받아서 사용자에게 전송합니다. |
| Hash | 1. 조인이나 hash group by를 병렬로 처리할 때 사용합니다. 2. 조인 키나 group by 키값을 해시 함수에 적용하고 → 리턴된 값에 따라서 데이터를 분배하는 방식입니다. 3. PtoP 뿐만 아니라 StoP 방식으로 이루어질 수 있습니다. |
| Broadcast | 1. 병렬조인에서 크기가 매우 작은 테이블이 있을 때 사용합니다. 2. QC 또는 첫 번째 서버 집합에 속한 프로세스들이 각각 읽은 데이터를 → 두 번째 서버 집합에 속한 모든 병렬 프로세스에게 전송하는 방식입니다. 3. PtoP 및 StoP 방식으로 이루어집니다. |
| Key | 1. 특정 컬럼들을 기준으로 테이블 또는 인덱스를 파티셔닝할 때 사용하는 분배 방식입니다. 2. 자주 사용하는 케이스 - Partial Partition Wise Join시 - CTAS 문장으로 파티션 테이블을 만들 때 (9i) - 병렬로 글로벌 파티션 인덱스를 만들 때 ⚠️ 비파티션 인덱스를 만들 때는 Range 방식을 활용합니다. |
| Round-Robin | 1. 파티션 키, 정렬 키, 해시 함수 등에 의존하지 않고 반대편 병렬 서버에 무작위로 데이터를 분배할 때 사용합니다. 2. Insert SELECT 시 활용합니다. |
✏️ 5. PQ_DISTRIBUTE 힌트
- 조인되는 양쪽 테이블의 파티션 구성, 데이터 크기 등에 따라 병렬 조인을 수행하는 옵션은 달라질 수 있습니다.
- 대개 옵티마이저 선택이 최적이라할 수 있지만, 그렇지 않은 경우가 존재합니다.
- 이때 pq_distribute 힌트를 사용해 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있습니다.
- 힌트 사용 케이스
- 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
- 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때
- 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정하고 싶을 때
- 기타 여러 사유로 데이터 분배 방식을 변경하고 싶을 때
- 힌트 사용 준비
- 병렬 방식으로 조인 수행을 위해선 프로세스들이 서로 독립적으로 작업할 수 있도록 사전 준비 작업이 필요합니다.
- 먼저 데이터를 적절히 배분하는 작업 선행이 필요합니다.
- 병렬 쿼리: 분할&정복(Divide&Conquer) 원리에 기초합니다.
- 병렬 조인: 분배&조인(Distribute&Join) 원리가 작동합니다.
- pq_distribute 힌트: 조인에 앞서 데이터를 분배(Distribute)하는 과정에만 관여합니다.
- ⚠️ 조인을 결정하는 힌트가 아닙니다.
- 힌트 사용법
- 힌트 케이스 1. pq_distribute(inner, none, none)
- 조인 방식: Full-Partition Wise Join 유도 시 사용
- 작동 요건: 양쪽 테이블 모두 조인 컬럼에 같은 기준으로 파티셔닝되어 있을 때만
- 📍Partition Wise Join
- 조인에 참여하는 두 테이블을 조인컬럼(deptno)에 대해 같은 기준으로 파티셔닝하고
- 각 파티션 짝(Partition Pair)끼리 독립적으로 (Intra-operation parallelism)으로 조인을 수행합니다.
파티션 짝을 구성한 뒤 병렬 프로세스끼리 서로 데이터를 주고 받으며 통신할 필요가 없으므로(Inter-operation parallilism) 병렬 조인 성능을 크게 향상시킬 수 있습니다. - Full Partition Wise Join
: 양쪽 테이블 모두 사전에 미리 파티셔닝되어 있어, 곧바로 Partition Wise Join 하는 경우 - Partial Partition Wise Join
: 한쪽만 파티셔닝되어 있어, 나머지 한쪽을 실행시점에 동적으로 파티셔닝 후 Partition Wise Join 하는 경우
- 힌트 케이스 2. pq_distribute(inner, partition, none)
- 조인 방식: Partial-Partition Wise Join 유도 시 사용
- 작동 요건: outer 테이블이 inner 테이블 파티션 기준에 따라 파티셔닝 하라
=> inner 테이블이 조인 키 컬럼에 대해 파티셔닝되어 있는 경우에만
- 힌트 케이스 3. pq_distribute(inner, none, partition)
- 조인 방식: Partial-Partition Wise Join 유도 시 사용
- 작동 요건: inner 테이블이 outer 테이블 파티션 기준에 따라 파티셔닝 하라
=> outer 테이블이 조인 키 컬럼에 따라 파티셔닝되어 있는 경우에만
- 힌트 케이스 4. pq_distribute(inner, hash, hash)
- 힌트 의미: 조인 키 컬럼에 해시 함수 적용 → 반환된 값을 기준으로 양쪽 테이블을 동적 파티션 하라
- 조인되는 테이블 둘다 파티셔닝해서, 파티션 짝(Partition Pair)를 구성한 뒤 Partition Wise Join 수행
- 힌트 케이스 5. pq_distribute(inner, broadcast, none)
- outer 테이블을 Broadcast 하라
- 힌트 케이스 6. pq_distribute(inner, none, broadcast)
- inner 테이블을 Broadcast 하라
✏️ 6. 병렬처리 시 주의사항
- 병렬 쿼리를 과도하게 사용하면 시스템을 마비 시킬 수 있습니다.
- 병렬 처리를 남발하면 안 됩니다.
- ex: 야간배치 10분 → 5분 이내로 단축 목표 bad (30시간 → 4시간 good) - 무조건 병렬도를 높인다고 성능이 선형적으로 좋아지는 것이 아닙니다.
- 성능 개선 효과가 확실한 최소한의 병렬도를 지정하려는 노력이 필요합니다.
- 시스템 리소스 사용이 최대한 필요한 시점에 적합합니다.
- ex: 데이터 마이그레이션(모든 프로그램 중단 후 이행 프로그램에만 자원 집중) - 병렬 DML 수행 시, Exclusive Mode Table Lock으로 트랜잭션이 활발한 주간에 사용을 금지합니다.
- 병렬 처리 기법 사용 기준
- 동시 사용자 수가 적은 애플리케이션 환경 (전체 CPU의 50% 정도를 점유할 수 있을 때)
- 야간 배치 프로그램, DW, OLAP 등 직렬로 처리할 때보다 성능 개선 효과가 확실할 때
- 작은 테이블은 병렬 처리 대상에서 제외
- OLTP성 시스템 환경
- 작업을 조기 완료하여 직렬 처리보다 전체적인 시스템 리소스 사용률 감소가 가능할 때(CPU, Memory 등)
- 단, 수행 빈도가 높지 않을 경우
- 병렬 튜닝의 핵심 원리
- 병렬 프로세스간 통신량 최소화
- 데이터를 병렬로 처리하는 중 발생하는 병목 구간 해소
- 성능 효과를 체감할 수 있는 최소한의 병렬 프로세스 할당
📒 정리하면
- 배치 프로그램의 특성: 사용자와 상호작용 없이 대량의 데이터를 정기/이벤트/온디맨드 방식으로 처리, 전체 처리속도 최적화가 핵심 목표입니다.
- 구현 방식: 절차형 프로그램보다 집합적 One SQL 처리의 성능이 우수합니다. (Random Access 감소, 중복 액세스 방지)
- 병렬 처리 유형: Full/Partial/Dynamic Partition Wise Join으로 분류, 파티션 구성에 따라 데이터 분배 방식이 결정됩니다.
- 데이터 분배 방식: Hash/Range/Broadcast/Key/Round-Robin 5가지 유형으로, 조인 키/그룹핑/정렬 요건에 따라 선택합니다.
- 병렬 조인 최적화: PQ_DISTRIBUTE 힌트로 파티션 활용도를 증대할 수 있습니다. (예: Broadcast-모든 프로세스에 동일 데이터 전송, Hash-균등 분배)
- 리소스 관리: 병렬도(DOP)는 성능 대비 최소한으로 설정, 과도한 병렬화는 시스템 리소스 고갈을 유발합니다.
- 실행 계획 제어: Full 힌트와 병렬 힌트 병행 사용(인덱스 스캔 방지), parallel_indx시 index_ffs 필수 적용
- 병렬 DML 주의사항: ALTER SESSION ENABLE PARALLEL DML 선행 필요, Exclusive Lock으로 주간 사용을 제한합니다.
- 적용 환경: 야간 배치/DW/OLAP 시스템에서 효과적, OLTP 환경에서는 수행 빈도와 리소스 사용률을 고려해야 합니다.
- 병렬 튜닝 원칙: 병렬 프로세스 간 통신량 최소화, 데이터 병목 구간 해소, Intra-Operation 병렬화를 우선으로 합니다.
반응형
