[Oracle] 병렬 프로세스 간 데이터 분배 비교 방식과 PQ_DISTRIBUTE 힌트 사용

병렬 SQL 사용방법 및 동작원리에 대한 이해

대용량 데이터의 조회와 가공을 동시에 여러 프로세스에서 수행해야 할 경우 다음 힌트를 사용한다.

SELECT /*+ FULL(테이블명) PARALLEL(테 이블명 병렬도) */ * FROM 테이블명 ; 예를 들어 데이터가 많은 전체 사원 테이 블을 조회할 경우 병렬 처리를 위한 SQL은 다음과 같이 작성한다.

SQL> select /*+ full(e) parallel(e 4) */ *
from employees e;

병렬 SQL을 수행하면 오라클은 <그림 1>과 같은 프로세스를 생성한다.

<그림 1>을 보면 /*+ full(e) parallel(e 4) */ 힌트에 의해 사원 테이블에서 데이터 를 추출할 4개의 병렬 서버 프로세스 (p000~p003)와 이들의 작업 범위를 할당 하고 관리 및 감독 할 Query Coordinator (QC) 1개가 생성된다는 것을 알 수 있다.

그러나 재처리 작업(JOIN, ORDER BY, GROUP BY)이 포함돼 있는 병렬 SQL은 지정한 병렬 서버 프로세스 수보다 2배 많이 생성된다. 예를 들어 ORDER BY 절이 포함된 병렬 SQL은 다음과 같다.

SQL> select /*+ full(e) parallel(e 4) */ *
from employees e;
order by e.name;

병렬 SQL을 수행하면 <그림 2>와 같은 프로세스가 생성된다. <그림 1>에서 본 것과 다르게 사원 테이블 데이터를 추출할 병렬 프로세스 그룹(p000~p003)에 이름 순서로 데이터를 정렬시킬 병렬 서버 프로세스 그룹(p004~p007)이 추가돼, 힌트로 지정한 4개보다 2배 더 많은 8개의 병렬 서버 프로세스가 생성됐다.

<그림 3>은 v$px_session 딕셔너리 뷰를 사용해 실제로 생성 돼 작업을 수행하는 프로세스 개수를 확인한 것이다.

DBMS을 모니터링하다 보면 서비스 응용 운영자나 개발자가 과도하게 병렬 프로세스를 수행하는 바람에 시스템 부하가 발생 해 서비스에 문제가 생기는 경우가 종종 있다. 이런 경우 대부 분 자기가 지정한 개수보다 많은 병렬 프로세스가 발생했기 때 문이다.

 

복잡한 병렬 SQL에 테이블 2개가 조인하면 어떻게 병렬 처리가 이뤄지는지 알아보자.

우선 양쪽 테이블에서 조인 조건에 만족하는 데이터를 찾아야 한다. 이때 조인 조건 데이터를 분배 및 비교하는 방식에는 세 가지, hash와 broadcase 그리고 partition이 있다.

 

HASH는 조인 조건 데이터를 HASH 함수를 적용한 결과값과 비교하는 방식으로, 양쪽 테이블 크기가 비슷한 경우 유리하다. BROASDCAST는 한쪽 전체 데이터를 비교할 쪽 전체 병렬 프로세스에게 넘긴다음 비교하는 방식으로, 작은 테이블을 경우 유리하지만 큰 테이블인 경우 많은 데이터를 다른 쪽 전체 병렬 프로세스에게 넘겨줘야 하므로 성능 문제가 발생한다. PARTITION은 조인 조건절의 컬럼이 파티션화된 테이블인 경우 파티션 키로 상호 비교하는 방식이다. 

 

이런 양쪽 프로세스들 간 분배방식은 해당 테이블에 통계정보가 있을 경우 옵티마이저가 최적화된 분배방식으로 실행계획을 수립한다. 그러나 데이터 전환 작업이나 배치 작업 시 임시로 생성돼 통계정보가 없는 테이블이나 통계정보가 있어도 옵티마이저가 잘못된 경우라면 잘못환 분배방식을 사용할 수 있다. 이 때 분배 방식을 명시적으로 변경할 수 있는 힌트가 바로 PQ_DISTRIBUTE이며 구문은 다음과 같다.

/*+ PQ_DISTRIBUTE( inner outer_distribution inner_distribution ) */

-- 일반적으로 자주 사용되는 분배방식 조합
- pq_distribute(inner hash hash) : 조인되는 양 쪽 테이블 크기가 큰 경우
- pq_distribute(inner broadcase none ) : 먼저 읽히는 테이블(OUTER 테이블)이 작은 경우
- pq_distribute(inner none broadcase ) : 나중에 읽히는 테이블(INNER 테이블)이 작은 경우

 

 

출처 : https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=236300