파티션 레벨로 동작하는 오퍼레이션을 Partition-Wise 오퍼레이션이라고 한다. 일반 쿼리는 기본적으로 Partition-Wise 오퍼레이션으로 동작하는 반면, 병렬 쿼리는 힌트를 통해 Partition-Wise 오퍼레이션을 유도해야 한다. 크기가 균등한 다수의 파티션을 조회하는 경우 Partition-Wise 오퍼레이션을 통해 병렬 쿼리의 성능을 개선할 수 있다. 관련 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2)
PARTITION BY RANGE (c1) (
PARTITION p1 VALUES LESS THAN (1000)
, PARTITION p2 VALUES LESS THAN (2000)
, PARTITION p3 VALUES LESS THAN (MAXVALUE))
AS
SELECT ROWNUM AS c1, 1 AS c2 FROM XMLTABLE ('1 to 3000');
아래 쿼리는 파티션 키가 아닌 c2 열로 그룹핑했다. 테이블 레벨로 그룹핑이 수행된다.
-- 2-1 SELECT c2, COUNT(c1) FROM t1 GROUP BY c2; -------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | HASH GROUP BY | | 1 | | | -- ! | 2 | PARTITION RANGE ALL| | 1 | 1 | 3 | | 3 | TABLE ACCESS FULL | T1 | 3 | 1 | 3 | --------------------------------------------------------------
아래 쿼리는 파티션 키인 c1 열로 그룹핑했다. 파티션 레벨로 그룹핑이 수행된 것을 확인할 수 있다. (실행 계획 2번 HASH GROUP BY 오퍼레이션의 Start가 3임) Partition-Wise 오퍼레이션으로 동작한 것이다.
-- 2-2 SELECT c1, COUNT(c2) FROM t1 GROUP BY c1; ------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE ALL| | 1 | 1 | 3 | | 2 | HASH GROUP BY | | 3 | | | -- ! | 3 | TABLE ACCESS FULL| T1 | 3 | 1 | 3 | -------------------------------------------------------------
아래 병렬 쿼리는 블록 그래뉼로 동작한다. 데이터 분배량을 감소시키기 위해 GBY_PUSHDOWN 쿼리 변환이 발생했다.
-- 2-3 SELECT /*+ PARALLEL(T1 2) */ c1, COUNT(c2) FROM t1 GROUP BY c1; --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | | 1 | PX COORDINATOR | | 1 | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | | | Q1,01 | P->S | QC (RAND) | | 3 | HASH GROUP BY | | 0 | | | Q1,01 | PCWP | | -- ! | 4 | PX RECEIVE | | 0 | | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 0 | | | Q1,00 | P->P | HASH | | 6 | HASH GROUP BY | | 0 | | | Q1,00 | PCWP | | -- ! | 7 | PX BLOCK ITERATOR | | 0 | 1 | 3 | Q1,00 | PCWC | | |* 8 | TABLE ACCESS FULL| T1 | 0 | 1 | 3 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------
12.2 버전부터 USE_PARTITION_WISE_GBY 힌트를 사용하면 파티션 그래뉼로 그룹핑을 수행할 수 있다. 병렬 서버 간의 데이터가 분배가 발생하지 않았다. (P->P 분배가 없음)
-- 2-4 SELECT /*+ PARALLEL(T1 2) USE_PARTITION_WISE_GBY */ c1, COUNT(c2) FROM t1 GROUP BY c1; --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | | 1 | PX COORDINATOR | | 1 | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION RANGE ALL| | 0 | 1 | 3 | Q1,00 | PCWC | | | 4 | HASH GROUP BY | | 0 | | | Q1,00 | PCWP | | -- ! | 5 | TABLE ACCESS FULL | T1 | 0 | 1 | 3 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------
DISTINCT도 GROUP BY 절과 유사하게 동작한다. 아래 쿼리는 SELECT 절에 파티션 키가 아닌 c2 열만 기술했다. 테이블 레벨로 중복 값이 제거된다.
-- 3-1 SELECT DISTINCT c2 FROM t1; -------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | HASH UNIQUE | | 1 | | | -- ! | 2 | PARTITION RANGE ALL| | 1 | 1 | 3 | | 3 | TABLE ACCESS FULL | T1 | 3 | 1 | 3 | --------------------------------------------------------------
아래 쿼리는 SELECT 절에 파티션 키인 c1 열을 추가했다. 파티션 레벨로 중복 값이 제거된다.
-- 3-2 SELECT DISTINCT c1, c2 FROM t1; ------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE ALL| | 1 | 1 | 3 | | 2 | HASH UNIQUE | | 3 | | | -- ! | 3 | TABLE ACCESS FULL| T1 | 3 | 1 | 3 | -------------------------------------------------------------
아래 병렬 쿼리는 블록 그래뉼로 동작한다. GROUP BY와 마찬가지로 GBY_PUSHDOWN 쿼리 변환이 발생했다.
-- 3-3 SELECT /*+ PARALLEL(T1 2) */ DISTINCT c1, c2 FROM t1; --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | | 1 | PX COORDINATOR | | 1 | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | | | Q1,01 | P->S | QC (RAND) | | 3 | HASH UNIQUE | | 0 | | | Q1,01 | PCWP | | -- ! | 4 | PX RECEIVE | | 0 | | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 0 | | | Q1,00 | P->P | HASH | | 6 | HASH UNIQUE | | 0 | | | Q1,00 | PCWP | | -- ! | 7 | PX BLOCK ITERATOR | | 0 | 1 | 3 | Q1,00 | PCWC | | |* 8 | TABLE ACCESS FULL| T1 | 0 | 1 | 3 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------
12.2 버전부터 USE_PARTITION_WISE_DISTINCT 힌트를 사용하면 파티션 그래뉼로 중복 값을 제거할 수 있다.
-- 3-4 SELECT /*+ PARALLEL(T1 2) USE_PARTITION_WISE_DISTINCT */ DISTINCT c1, c2 FROM t1; --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | | 1 | PX COORDINATOR | | 1 | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION RANGE ALL| | 0 | 1 | 3 | Q1,00 | PCWC | | | 4 | HASH UNIQUE | | 0 | | | Q1,00 | PCWP | | -- ! | 5 | TABLE ACCESS FULL | T1 | 0 | 1 | 3 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------
마지막으로 분석 함수를 살펴보자. 아래 쿼리는 ROW_NUMBER 함수를 파티션 키가 아닌 c2 열로 정렬했다. 테이블 레벨로 순번이 생성된다.
-- 4-1 SELECT ROW_NUMBER () OVER (ORDER BY c2) FROM t1; -------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | WINDOW SORT | | 1 | | | -- ! | 2 | PARTITION RANGE ALL| | 1 | 1 | 3 | | 3 | TABLE ACCESS FULL | T1 | 3 | 1 | 3 | --------------------------------------------------------------
아래 쿼리는 ROW_NUMBER 함수를 파티션 키인 c1 열로 파티셔닝했다. 파티션 레벨로 순번이 생성된다.
-- 4-2 SELECT ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) FROM t1; ------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE ALL| | 1 | 1 | 3 | | 2 | WINDOW SORT | | 3 | | | -- ! | 3 | TABLE ACCESS FULL| T1 | 3 | 1 | 3 | -------------------------------------------------------------
아래 병렬 쿼리는 블록 그래뉼로 동작한다. PQ_DISTRIBUTE_WINDOW 방식으로 데이터가 분배된다.
-- 4-3 SELECT /*+ PARALLEL(T1 2) */ ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) FROM t1; -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | | 1 | PX COORDINATOR | | 1 | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | | | Q1,01 | P->S | QC (RAND) | | 3 | WINDOW SORT | | 0 | | | Q1,01 | PCWP | | -- ! | 4 | PX RECEIVE | | 0 | | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 0 | | | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 0 | 1 | 3 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| T1 | 0 | 1 | 3 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------
18.1 버전부터 USE_PARTITION_WISE_WIF 힌트를 사용하면 파티션 그래뉼로 분석 함수를 수행할 수 있다.
-- 4-4 SELECT /*+ PARALLEL(T1 2) USE_PARTITION_WISE_WIF */ ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) FROM t1; --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | | 1 | PX COORDINATOR | | 1 | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION RANGE ALL| | 0 | 1 | 3 | Q1,00 | PCWC | | | 4 | WINDOW SORT | | 0 | | | Q1,00 | PCWP | | -- ! | 5 | TABLE ACCESS FULL | T1 | 0 | 1 | 3 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------
'Oracle > Tuning' 카테고리의 다른 글
| IOT Secondary 인덱스 (0) | 2018.10.08 |
|---|---|
| MBRC에 따른 physical read 성능 (0) | 2018.09.29 |
| 일별 배치 튜닝 사례 (0) | 2018.06.21 |
| TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 #2 (0) | 2018.06.21 |
| HASH JOIN BUFFERED 오퍼레이션 (0) | 2018.06.20 |
