일반적인 의미의 BATCH(배치) 프로그램은 일련의 작업을 하나의 작업 단위로 묶어 일괄처리하는 프로그램을 말합니다. 온라인 실시간 프로그램도 여러 작업을 묶어 처리하는 경우가 있는데 두 프로그램의 차이점은 사용자와의 상호작용 여부입니다.
배치 프로그램의 특징은 다음과 같습니다.
사용자와 상호작용 없이 대량의 데이터를 처리하는 일련의 작업을 묶어 정기적으로 반복 수행하거나 정해진 규칙에 따라 자동으로 수행하는 프로그램으로 수행되는 주기는 월/주/일 단위가 보통이고 실시간으로 처리되는 경우도 있습니다. 실시간 배치는 보통 비동기 방식으로 처리가 완료됐다는 신호를 받은 사용자가 결과를 확인하는 방식입니다.
위와 같은 특징을 고려해 배치 프로그램을 다음과 같이 구분할 수 있습니다.
- 정기배치 : 정해진 시험에 실행
- 이벤트성 배치 : 사전에 정의해 둔 조건이 충족되면 자동으로 실행
- ON-DEMAND 배치 : 사용자의 명시적인 요구가 있을때마다 실행
성능개선목표 설정
배치 프로그램의 성능 목표와 튜닝 기법은 OLTP와는 달라야 합니다. 온라인 프로그램은 때에 따라 전체 처리속도와 최초 응답속도 최적화를 목표로 선택하지만 배치 프로그램은 항상 전체 처리속도 최적화를 목표로 해야합니다. 자원경합이 극심한 상황에서는 프로그램이 정상적으로 진행하기 어렵습니다. 자원 사용측면에서 병렬도를 최소화하는 것이 유리합니다.
배치 프로그램 구현 패턴과 튜닝 방안
절차형으로 작성된 프로그래밍
애플리케이션 커서를 열고 루프 내에서 또 다른 SQL이나 서브 프로시저를 호출하면서 같은 처리를 반복하는 형태의 프로그램
ONE SQL 위주 프로그램
집합적으로 정의된 여러 SQL을 단계적으로 실행
ONE SQL 위주의 프로그램이 성능면에서는 월등합니다. 절차형을 작성된 프로그램은 다음과 같은 비효율이 있습니다.
- 반복적인 데이터베이스 Call 발생
- RANDOM I/O 위주
- 동일 데이터를 중복 엑세스
이런 단점에도 불구하고 ONE SQL 위주의 프로그램은 구현하기 어렵기 때문에 절차형으로 작성된 프로그램의 튜닝 방안을 찾으려고 노력해야합니다.
절차형 프로그램 튜닝방안
병목을 일으키는 SQL을 찾아 I/O 튜닝을 진행합니다. 인덱스를 재구성하고 엑세스 경로를 최적화합니다. 병렬로 작동하도록 PARALLEL을 활용합니다. 메인 SQL이 읽는 데이터 범위를 달리하는 동일한 프로그램을 동시에 여러개 수행합니다. ARRAY PROCESSING으로 전체 CALL의 수를 낮추고 가능하다면 ONE SQL위주 프로그램으로 구현합니다.
ONE SQL 프로그램 튜닝방안
병목을 일으키는 오퍼레이션을 찾아 I/O 튜닝합니다. FULL TABLE SCAN 방식으로 처리하고 해시 조인방식으로 처리합니다. 입시테이블, 파티션, 병렬 처리를 활용합니다.
병렬처리활용
병렬 처리란 SQL문이 수행해야할 작업 범위를 여러 개의 작은 단위로 나눠 여러 프로세스(또는 쓰레드)에서 동시에 처리하는 것을 말합니다. 대용량 데이터를 처리할때 수행속도를 단축시킬 수 있습니다. 아래는 힌트를 통해 병렬처리를 유도하는 방법입니다.
-- ORACLE 에서 병렬처리를 활용하는 방법은 다음과 같다.
SELECT /*+ FULL(O) PARALLEL(O,4) */
COUNT(*) 주문건수, sum(주문수량) 주문수량, sum(주문금액) 주문금액
FROM 주문 o
WHERE 주문일시 Between '20100101' and '20101231'
PARALLEL 을 유도할 때는 반드시 FULL 힌트도 함께 사용해야 합니다. 옵티마이저에 의해 인덱스 스캔이 선택되면 PARALLEL 힌트가 무시되기 때문입니다.
반대로 PARALLEL_INDEX 힌트를 사용할때는 반드시 INDEX RANGE SCAN 또는 INDEX FAST FULL SCAN으로 유도해야 합니다. 옵티마이저가 FULL TABLE SCAN을 선택하면 PARALLEL_INDEX 힌트가 무시되기 때문입니다.
SELECT /*+ index_ffs(o, 주문_idx) PARALLEL_index(o, 주문_idx, 4) */
COUNT(*) 주문건수, sum(주문수량) 주문수량, sum(주문금액) 주문금액
FROM 주문 o
WHERE 주문일시 Between '20100101' and '20101231'ORACLE 내부 병렬처리 프로세스 및 PQ_DISTRIBUTE 힌트
QUERY CORDINATOR 와 병렬 서버 프로세스
QUERY CORDINATOR(=QC)는 병렬 SQL 문을 발행한 세션이고 병렬 서버 프로세스는 실제 작업을 수행하는 개별 세션입니다.
QC의 역할은 다음과 같습니다.
- 병렬 SQL이 실행되면 QC는 사용자가 지정한 병렬도와 오퍼레이션 종류에 따라 하나 또는 두개의 병렬서버 집합을 할당합니다. 서버 POLL로 부터 필요한만큼 서버 프로세스를 확보하고 부족분은 새로 생성합니다.
- QC는 각 병렬 서버에게 작업을 할당합니다. 작업을 지시하고 제대로 진행되는지 감독합니다.
- 병렬로 처리하도록 사용자가 지시하지 않은 테이블을 QC가 직접 처리합니다.
- QC는 각 병렬 서버의 산출물을 통합하는 작업을 수행합니다.
- QC는 쿼리의 최종결과집합을 사용자에게 전송하며, DML일 경우 갱신 건수를 집계해서 전송합니다. 쿼리 결과를 전송하는 단계에서 수행 되는 스칼라 서브쿼리도 QC가 수행합니다.
SELECT /*+ORDERED USE_HASH(E) FULL NOPARALLEL(D) PARALLEL(E 4)*/
COUNT(*), MIN(SAL), AVG(SAL), SUM(SAL)
FROM DEPT D, EMP E
WHERE D.LOC = 'CHICAGO'
AND E.DEPTNO = D.DEPTNO
-- EXECUTION PLAN
-- SELECT STATEMENT
-- PX CORDINATOR
-- PX SEND QC(RANDOM) :TQ10002
-- SORT AGGREATE
-- HASH JOIN
-- BUFFER SORT
-- PX RECEIVE
-- PX SEND HASH
-- TABLE ACCESS FULL DEPT :TQ10000
-- PX RECIEVE
-- PX SEND HASH :TQ10001
-- PX BLOCK ITERATOR
-- TABLE ACCESS FULL EMP
DEPT 테이블을 직렬로 읽어 병렬 서버에 전송하는 8~9번 오퍼레이션은 QC의 몫입니다.
INTRA-OPERATION PARALLELISM & INTER-OPERATION PARALLELISM
SELECT /*+FULL(고객) parallel(고객 4)*/ *
FROM 고객
ORDER BY 고객명
서로 베타적인 범위를 독립적으로 동시에 처리하는 것을 INTRA-OPERATION PARALLELISM 이라고 합니다. 동일한 집합의 파티션끼리는 서로 데이터를 주고받지않습니다.
반면 다른 집합에 데이터를 분배하거나 정렬된 겨로가를 QC로 전송하는 작업을 동시에 진행하는 것을 INTRA-OPERATION PARALLELISM 라고 하고 이때는 프로세스간 통신이 발생합니다.
테이블 큐
INTRA-OPERATION PARALLELISM은 동일한 병렬 서버집합에 속한 여러 프로세스가 처리범위를 달리하면서 작업을 진행하는 것이므로 동일 집합내에서는 절대 프로세스 간 통신이 발생하지 않습니다. 반면 INTER-OPERATION PARALLELISM은 프로세스간 통신이 발생하고, 메시지 또는 데이터를 전송하기 위한 통신 체널이 필요합니다.
쿼리 서버 집합 간 또는 QC 쿼리 서버 집합간 데이터 전송을 위해 연결된 파이프라인을 테이블 큐라고 합니다. 각 테이블 큐에 부여된 이름을 테이블 큐 식별자라고 합니다.
select /*+ordered use_hash(e) full(d) noparallel(d) full(e) parallel(e 2)
pdd_distribute(e broadcast none)*/ *
from dept d, emp e
where d.deptno = e.deptno
order by e.name
쿼리 서버 집합 간 INTER-OPERATION PARALLELISM이 발생할 때는 사용자가 지정한 병렬도의 배수 만큼의 서버 프로세스가 필요합니다. 또한 테이블 큐에는 병렬도의 제곱만큼의 파이프라인이 필요합니다.
테이블 큐 생산자-소비자 모델
테이블 큐에는 생산자와 소비자가 존재합니다. 테이블을 직렬로 읽어 분배하는 테이블 큐에서는 QC가 생성자이고 서버집합이 소비자입니다. 조인의 경우 두개의 서버집합이 각각 생산자-소비자 가 되고 최종결과를 전송하는 테이블 큐에서는 서버집합이 생산자, QC가 소비자가 됩니다. INTER-OPERATION PARALLELISM이 나타나면, 소비자 서버집합은 FROM 절에 테이블 큐를 참조하는 서브 SQL로 작업을 수행합니다.
병렬 실행계획에서 생산자와 소비자 식별
아래와 같은 병렬처리 실행계획이 있다고 가정합니다.
SELECT STATEMENT
PXCORDINATOR
PX SEND QC(ORDER) :TQ10002 --- 3
SORT ORDER BY
PX RECEIVE
PX SEND RANGE :TQ10001 --- 2
HASH JOIN
BUFFER SORT
PX RECEIVE
PX SEND BROADCAST :TQ10000 --- 1
TABLE ACCESS FULL DEPT
PX BLOCK ITERATOR
TABLE ACCESS FULL EMP
각 오퍼레이션이 어떤 서버집합에 속한 병렬 프로세스에 의해 수행되는지는 TQ 칼럼에 보이는 서버 집합 식별자를 통해 확인할 수 있습니다.
- QC가 DEPT 테이블을 읽어 첫 번째 서버집합(Q1, 01)에게 전송합니다.
- 첫 번째 서버집합(Q1, 01)은 EMP 테이블을 병렬로 읽으면서 QC에게 받은 DEPT 테이블과 조인합니다.
- 두 번째 서버집합(Q1, 02)는 전송받은 래코드를 정렬하고 QC에 전달합니다.
생산자에서 소비자로 데이터 재분배가 일어날때마다 테이블 큐 :TQXXXXXX 형태가 표시됩니다.
IN-OUT 오퍼레이션
OPEPRATION NAME IN-OUT
SELECT STATEMENT
PX COORDINATOR
PX SEND QC(RANDOM) :TQ10003 P-S
VIEW PCWP
WINDOW SORT PUSHED RANK PCWP
PX RECIEVE PCWP
PX SEND HASH :TQ10002 P-P
WINDOW CHILD PUSHED RANK PCWP
HASH JOIN PCWP
PX RECEIVE PCWP
PX SEND BROADCAST :TQ10001 P-P
HASH JOIN PCWP
BUFFER SORT PCWP
PX RECEIVE PCWP
PX SEND BROADCAST :TQ10000 S-P
TABLE ACCESS FULL 조직 SERIAL(BLANK)
PX BLOCK ITERATOR PCWP
TABLE ACCESS FULL 계약마스터 PCWP
PX BLOCK ITERATOR PCWC
TABLE ACCESS FULL 계약변경이력 PCWP
- SERIAL : 직렬처리
- S-P : qc가 처리한 결과를 병렬 서버 프로세스에게 전달
- P-S : 병렬 서버 프로세스가 처리한 결과를 QC에 전달
- P-P : 두 개의 병렬 서버 프로세스 집합이 처리, 지정한 병렬도의 2배 만큼 병렬 프로세스 생성
- PCWP : 병렬 서버 프로세스 집합이 현재 스텝과 그 부모 스탭을 모두 처리
- PCWC : 병렬 서버 프로세스 집합이 현재 스탭과 그 자식 스텝 모두 처리
S-P, P-S, P-P 는 프로세스 간 통신이 발생합니다. PCWP, PCWC는 프로세스 간 통신이 발생하지 않고, 각 병렬 서버가 독립적으로 여러 스텝을 처리합니다. 하위 스탭의 출력 값이 상위 스텝의 입력 값으로 사용됩니다. P-P, P-S, PCWP, PCWC는 병렬 오퍼레이션이고 S-P는 직렬 오퍼레이션입니다.
데이터 재분배
병럴 서버 프로세스 간 데이터를 재분배하는 방식에는 5가지가 있습니다.
RANGE
ORDER BY, SORT GROUP BY를 병렬로 처리할 때 사용됩니다. 정렬 작업을 맡은 두 번째 서버집합의 프로세스마다 처리범위를 지정하고 나서 데이터를 읽는 첫 번째 서버 집합이 두 번째 서버집합의 정해진 프로세스에게 정렬 키 값에 따라 분배하는 방식입니다.
HASH
조인이나 HAHS GROUP B를 병렬로 처리할 때 사용합니다. 조인 키나 GROUP BY 키 값을 해시함수로 적용하고 리턴된 값에 따라 데이터를 분배합니다.
BROADCAST
QC 또는 첫 번째 서버집합에 속한 프로세스들이 각각 읽은 데이터를 두 번째 서버집합에 속한 모든 병렬 프로세스에 전달하는 방식입니다. 병렬 조인에서 크기가 매우 작은 테이블이 있을 때 사용되며 p-p 뿐아니라 S-P 방식으로도 이뤄집니다.
KEY
특정 칼럼을 기준으로 테이블 또는 인덱스 파티셔닝을 할때 사용하는 분배 방식입니다.
ROUND-ROBIN
파티션 키, 정렬 키, 해시 함수등에 의존하지 않고 반대편 병렬 서버에 무작위로 데이터를 분배할 때 사용합니다.
PQ_DISTRIBUTE 힌트
용도
조인하는 양쪽 테이블의 파티션 구성, 데이터 크기등에 따라 병렬 조인을 수행하는 옵티마이저의 선택이 달라집니다. 이때 PQ_DISTRIBUTE 힌트를 활용해 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있습니다.
- 옵티마이저가 파티션된 테이블을 적절히 확인하지 못하고 동적 재분할을 시도
- 기존 파티션 키를 무시하고 다를 키 값으로 동적 재분할을 시도할때
- 통계적보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정할때
- 기타 여러가지 이유로 데이터 분배방식을 변경하려고 할때
병렬 방식으로 조인을 수행하기 위해서는 프로세스가 독립적으로 작업할 수 있도록 사전준비작업이 필요합니다. 프로세스 별로 데이터를 적절히 분배하는 선행작업이 필요합니다.
병렬 쿼리는 DIVIDE & CONQUER 원리에 기초합니다. 하지만 PQ_DISTRIBUTE 힌트는 조인에 앞서 데이터를 분배하는 과정에만 관여하는 힌트입니다. 아래 실행계획은 양쪽 모두 HASH 방식으로 분배했지만 소트머지조인으로 수행됐습니다. 즉, 데이터를 재분배하기 위해 해시 함수를 사용하는 것일 뿐 조인방식과는 무관합니다.
SELECT /*+ordered use_merge(e) parallel(d 4) parallel(e 4) pq_distribute(e hash hash)*/ *
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DPETNO
-- EXECUTION PLAN
SELECT STATEMENT
PX COORDINATOR
PX SEND QC (RANDOM) :TQ10002
MERGE JOIN
SORT JOIN
PX RECIEVE
PX SEND HASH :TQ10000
PX BLOCK ITERATOR
TABLE ACCESS FULL DEPT
SORT JOIN
PX RECIEVE
PX SEND HASH :TQ10001
PX BLOCK ITERATOR
TABLE ACCESS FULL EMPPQ_DISTRUBTE 사용법
PQ_DISTRIBUTE(TABLE, OUTER_DISTRIBUTION, INNER_DISTRIBUTION)
- TABLE : INNER 테이블명 OR ALIAS
- OUTER_DISTRIBUTION : OUTER TABLE DISTRIBUTION 방식
- INNER_DISTRIBUTION : INNER TABLE DISTRIBUTION 방식
PQ_DISTRIBUTION 힌트로 지정할 수 있는 데이터 분배 방식과 특징은 다음과 같습니다.
PQ_DISTRIBUTE(INNER, NONE, NONE)
FULL_PARTITION WISE JOIN으로 유도합니다. 양쪽 테이블 모두 조인 칼럼에 대해 같은 기준으로 파티셔닝 돼있어야 합니다.
PARTITON WISE JOIN은 조인에 참여하는 두 테이블을 조인 칼럼에 대해 같은 기준으로 파티셔닝하고 각 파티션 끼리 독립적으로 조인을 수행합니다. 파티션 짝을 구성하고 나면 병렬 프로세스끼리 서로 통신할 필요가 없어 성능이 증가합니다.
양쪽 테이블이 사전에 파티셔닝돼있어 곧바로 PARTITION WISE JOIN하는 경우를 FULL PARTITION WISE JOIN이라 하고, 한 쪽만 파티셔닝 돼있어 나머지 한쪽을 실행 시점에 동적으로 파티셔닝하고 PARTITION WISE JOIN 하는 경우를 PARTIAL PARTITION WISE JOIN이라고 합니다.
PQ_DISTRIBUTE(INNER, PARTITION, NONE)
PARTIAL PARTITION WISE JOIN으로 유도할때 사용합니다. OUTER 테이블을 INNER 테이블의 파티션 기준에 따라 파티셔닝 합니다. INNER 테이블이 조인 키 칼럼에 대해 파티셔닝돼 있을 때만 작동합니다.
PQ_DISTRIBUTE(INNER, NONE, PARTITION)
PARTIAL PARTITION WISE JOIN으로 유도할때 사용합니다. INNER 테이블을 OUTER 테이블의 파티션 기준에 따라 파티셔닝 합니다. OUTER 테이블이 조인 키 칼럼에 대해 파티셔닝돼 있을 때만 작동합니다.
PQ_DISTRIBUTE(INNER, HASH, HASH)
조인 키 칼럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔넝합니다. 조인되는 테이블을 둘 다 파티셔닝해서 파티션 짝을 구성하고 PARTITION WISE JOIN을 수행합니다.
PQ_DISTRIBUTE(INNER, BROADCAST, NONE)
OUTER TABLE을 BROADCAST 합니다
PQ_DISTRIBUTE(INNER, NONE, BROADCAST)
INNER TABLE을 BROADCAST 합니다.
PQ_DISTRIBUTE 힌트를 이용한 튜닝
INSERT /*+APPEND*/ INTO 상품기본이력(...)
SELECT /*+ORDERED PARALLEL A(,16) PARALLEL(B,16) PARALLEL(C,16) PARALLEL(D,16)
PQ_DISTRIBUTE(B, NONE, PARTITION)
PQ_DISTRIBUTE(C, NONE, BROADCAST)
PQ_DISTRIBUTE(D, HASH, HASH)
*/
....
FROM 상품기본이력임시 a, 상품 b, 코드상세 c, 상품상세 d
WHERE a.상품번호 = b.상품번호
AND ...
-- OPERATION
INSERT STATEMENT
LOAD AS SELECT
HASH JOIN
HASH JOIN OUTER
HASH JOIN
PARTITION HASH ALL
TABLE ACCESS FULL 상품기본이력임시
TABLE ACCESS FULL 상품
TABLE ACCESS FULL 코드상세
TABLE JOIN FULL 상품상세병렬 처리시 주의사항
병렬처리를 과도하게 사용하면 시스템이 마비될 수 있습니다. 적절한 사용기준은 다음과 같습니다.
- 동시 사용자가 적은 애플리케이션 환경에서 직렬로 처리할 때보다 성능 개선 효과가 확실할때
- OLTP성 시스템 환경이더라도 직렬로 처리할때보다 전체적인 시스템 리소스 사용률을 감소시킬 수 있을때
특정 소수의 배치작업이 과도한 병렬처리를 시도한다면 CPU, 메모리, 디스크 자원에 대한 경합을 높혀 전체 배치 수행시간이 늘어날 수 있습니다. 성능 개선효과가 확실한 최소한의 병렬도를 지정하려는 노력이 필요합니다.
'DB > SQL튜닝' 카테고리의 다른 글
| SQL 튜닝 이론 정리1 (1) | 2024.05.30 |
|---|---|
| SQL튜닝 - 고급 SQL 기법을 활용한 성능개선 (0) | 2024.05.16 |
| SQL튜닝 - 파티셔닝 (0) | 2024.05.03 |
| SQL튜닝 - 데이터베이스 CALL 최소화방안 (1) | 2024.05.03 |
| SQL 튜닝 - DML 튜닝 (0) | 2024.05.02 |