4. PQ_DISTRIBUTE 힌트

1. pq_distribute 힌트의 용도

pq_distribute 힌트를 사용해 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있다.
Parallel Query Distribution => 병렬 쿼리 분배라는 의미

pq_distribte 힌트를 사용할 경우

• 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때

• 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때

• 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정시키고자 할 때

• 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때

pq_distribute 힌트는 분배와 조인으로 봤을 때 조인에 앞서 데이터를 분배하는 과정에만 관여하는 힌트이다.
pq_distrubute(x hash hash) 일 때, hash로 데이터를 재분배하면 끝나는 것이지 이후에, 해시조인을 하는 게 아니라는 뜻이다.

2. 구문 이해하기

select /*+ pq_distribute (table,                  -- inner 테이블명, 또는 alias
					outer_distribution,    -- outer 테이블의 distribution 방식
                    inner_distribution)    -- inner 테이블의 distribution 방식
                    */ *                 

pq_distribute 힌트도 ordered 나 leading 힌트에 의해 먼저 처리되는 outer 테이블을 기준으로 그 집합과 조인하는 inner 테이블을 첫 번째 인자로 지정하면 된다.

조인 순서를 먼저 고정시키는 것이 중요하므로 ordered 나 leading 힌트를 같이 사용하는 것이 중요하다.

3. 분배방식 지정

• pq_distribute(inner, none, none)

  Full Partition Wise 조인으로 유도할 때 사용한다.
  양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝 돼 있을 때만 사용가능하다.

• pq_distribute(inner, partition, none)

  Partial Partition Wise 조인으로 유도할 때 사용한다.
  outer 테이블을 inner 테이블 파티션 기준에 따라 파티셔닝하라는 의미이다.
  inner 테이블이 조인 키 컬럼에 파티셔닝 돼 있을 때만 사용가능하다.

• pq_distribute(inner, none, partition)

  Partial Partition Wise 조인으로 유도할 때 사용한다.
  inner 테이블을 outer 테이블 파티션 기준에 따라 파티셔닝하라는 의미이다.
  outer 테이블이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때만 사용가능하다.

• pq_distribute(inner, hash, hash)

  조인 키 컬럼을 해시 함수에 적용하고 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝하라는 의미이다.

• pq_distribute(inner, broadcast, none)

  outer 테이블을 broadcast 하라는 의미이다.

• pq_distribute(inner, none, broadcast)

  inner 테이블을 broadcast 하라는 의미이다.

---

5. 병렬 처리에 관한 기타 상식

1. Direct Path I/O

오라클은 병렬 방식으로 Full Scan 할 때 버퍼 캐시를 거치지 않고 곧바로 PGA 영역으로 읽어들이는 Direct Path I/O 방식을 사용한다.
병렬도를 2로 뒀을 때 쿼리 속도는 2배보다 훨씬 더 향상되는 이유이다.

2. 병렬 DML

병렬 처리가 가능해지려면 쿼리, DML, DDL을 수행하기 전에 각각 아래와 같은 명령을 수행해야 한다.

alter session enable parallel query; -- 병렬 쿼리 활성화
alter session enable parallel dml;   -- 병렬 dml 활성화
alter session enable parallel ddl;   -- 병렬 ddl 활성화

병렬 쿼리와 병렬 ddl은 기본적으로 활성화 돼있어서 괜찮지만 병렬 dml은 사용자가 명시적으로 활성화해 주어야 한다.

오라클은 9iR1까지 병렬 DML은 파티션 기반 Granule 이었다.
한 세그먼트를 두 개 이상 프로세스가 동시에 갱신할 수 없었고, 파티션되지 않은 테이블이라면 병렬로 갱신할 수 없었다.

오라클은 9iR2부터 병렬 DML이 블록 기반 Granule로 바뀌었는데, 주의할 점은 병렬 DML을 수행할 때 Exclusive 모드 TM Lock이 걸린다는 것이다.

성능은 빨라져도 해당 테이블을 다른 트랜잭션이 DML을 수행하지 못하게 되므로 트랜잭션이 빈번한 주간에 병렬 DML을 사용해서는 안된다.

3. 병렬 인덱스 스캔

Index Fast Full Scan이 아닌 한 인덱스는 기본적으로 병렬로 스캔할 수 없다.
파티션된 인덱스일 때는 병렬 스캔이 가능하고, 파티션 기반 Granule이므로 병렬도는 파티션 개수 이하로만 지정할 수 있다.

4. 병렬 NL 조인

병렬 NL 조인은 Outer 테이블과 Inner 테이블이 둘 다 초대용량 테이블이고, Outer 테이블에 사용된 특정 조건의 선택도가 매우 낮은데 그 컬럼에 대한 인덱스가 없고, 수행빈도가 낮으며 Inner 쪽 조인 컬럼에는 인덱스가 있는 경우 사용하면 좋다.

5. 병렬 쿼리와 스칼라 서브쿼리

스칼라 서브쿼리를 기술하는 위치에 따라 QC가 수행하기도 하고 병렬 서버가 수행하기도 하며, 이는 병렬 쿼리 수행 속도에 큰 영향을 미친다.
병렬 처리 효과를 높이려면 부분범위처리, 전체범위처리 여부에 따라 스칼라 서브쿼리 위치를 옮기거나 아예 일반 조인문으로 변환하는 튜닝을 실시해야 한다.

6. 병렬 쿼리와 사용자 정의 함수

SQL 수행 결과는 병렬로 수행하는지와 상관없이 항상 일관된 결과를 보장해야 한다.
그런데 패키지 세션 변수를 참조하는 함수는 병렬로 실행했을 때 일관성이 보장되지 않기 때문에 오라클은 기본적으로 병렬 수행을 거부한다.

그럼에도, 사용자가 병렬 수행을 원할 때 사용하는 키워드가 parallel_enable이다.
이 키워드를 사용하면 함수 수행 결과가 달리질 수 있기 때문에, 세션 변수를 참조 하지 않았다면 굳이 이 키워드를 사용하지 않아야 한다.
사용하지 않아도 병렬 수행이 가능하기 때문이다.

7. 병렬 쿼리와 ROWNUM

병렬 쿼리, 병렬 DML 문장에 rownum을 사용하는 순간 병렬 처리에 제약을 받게 되므로 주의가 필요하다.

8. 병렬 처리 시 주의사항

병렬 쿼리의 적절한 사용 기준

• 동시 사용자 수가 적은 애플리케이션 환경 (야간 배치 프로그램, DW, OLAP)에서 직렬로 처리할 때보다 성능 개선 효과가 확실할 때

• OLTP성 환경이라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스 사용량을 감소시킬 수 있을 때

주의사항

• 병렬도는 반드시 지정하자.

• 실행계획에 P->P가 나타날 경우 지정한 병렬도의 2배수만큼 병렬 프로세스가 필요한 것이다.

• 쿼리 작성 시 병렬도를 모두 같게 지정하는 것이 바람직하다.

• parallel 힌트 사용 시 반드시 Full 힌트도 함께 사용
  => 인덱스 스캔이 선택될 경우 parallel 힌트가 무시될 수 있다.

• parallel_index 힌트 사용 시 반드시 index 또는 index_ffs 힌트를 함께 사용
  => Full Table Scan 이 선택될 경우 parallel_index 힌트 무시됨

• 병렬 DML 시 Exclusive 모드 TM Lock이 걸리므로 업무 트랜잭션이 발생하는 주간에는 자제

• 테이블이나 인덱스를 빠르게 생성하려고 parallel 힌트를 썼다면 작업 완료후 noparallel 힌트로 원상복구 시켜놔야 한다.

• 부분범위처리 방식으로 조회하면서 병렬 쿼리를 사용한 경우에는 필요한 만큼 데이터를 Fetch 한 후 곧바로 커서를 닫아 주어야 한다.