14S_그래뉼과 Distribute (by kkabong) [2015.01.13]
병렬처리 그래뉼과 Distribute
- 파티션과 병렬처리 시 성능에 영향을 미치는 요소
Granules 단위
- Block-Range Granules : 블록기반의 그래뉼로 처리하는 기본적인 처리 방식
 |
- Partition Granules : 동일한 파티션 키를 가지고 있는 테이블이나 Reference partition인 경우 Partition-Wise join으로 처리될 때 발생 (partition pruning)
 |
실행계획에 PX BLOCK ITERATOR (블록 그래뉼) | PX PARTITION (파티션 그래뉼)로 표현됨
partition wise join test
SELECT /*+ parallel */
c.cust_last_name
, COUNT(*)
FROM customers_part c
, sales_part s
WHERE c.time_id = s.time_id
AND s.time_id BETWEEN TO_DATE('01-01-1990', 'DD-MM-YYYY') AND TO_DATE('01-12-2000', 'DD-MM-YYYY')
GROUP BY c.cust_last_name
HAVING COUNT(*) > 100;
-- PQ_DISTRIBUTE(s, none, none )으로 유도
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4K Card=75M Bytes=3G)
1 0 PX COORDINATOR
2 1 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10001' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_SERIAL) (QC (RANDOM))
3 2 FILTER (PARALLEL_COMBINED_WITH_CHILD)
4 3 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
5 4 PX RECEIVE (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
6 5 PX SEND (HASH) OF 'SYS.:TQ10000' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_PARALLEL) (HASH)
7 6 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
8 7 PX PARTITION RANGE (ITERATOR) (Cost=1K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_CHILD)
9 8 HASH JOIN (Cost=1K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
10 9 TABLE ACCESS (FULL) OF 'CUSTOMERS_PART' (TABLE) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
11 9 TABLE ACCESS (FULL) OF 'SALES_PART' (TABLE) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
--_bloom_pruning_enabled = true
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4K Card=75M Bytes=3G)
1 0 PX COORDINATOR
2 1 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10002' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_SERIAL) (QC (RANDOM))
3 2 FILTER (PARALLEL_COMBINED_WITH_CHILD)
4 3 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
5 4 PX RECEIVE (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
6 5 PX SEND (HASH) OF 'SYS.:TQ10001' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_PARALLEL) (HASH)
7 6 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
8 7 HASH JOIN (Cost=1K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
9 8 PART JOIN FILTER (CREATE) OF 'SYS.:BF0000' (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
10 9 PX RECEIVE (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
11 10 PX SEND (BROADCAST) OF 'SYS.:TQ10000' (Cost=228 Card=47K Bytes=1M) (PARALLEL_TO_PARALLEL) (BROADCAST)
12 11 PX BLOCK (ITERATOR) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_CHILD)
13 12 TABLE ACCESS (FULL) OF 'CUSTOMERS_PART' (TABLE) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
14 8 PX BLOCK (ITERATOR) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_CHILD)
15 14 TABLE ACCESS (FULL) OF 'SALES_PART' (TABLE) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
--_bloom_pruning_enabled = false
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4K Card=75M Bytes=3G)
1 0 PX COORDINATOR
2 1 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10002' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_SERIAL) (QC (RANDOM))
3 2 FILTER (PARALLEL_COMBINED_WITH_CHILD)
4 3 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
5 4 PX RECEIVE (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
6 5 PX SEND (HASH) OF 'SYS.:TQ10001' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_PARALLEL) (HASH)
7 6 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
8 7 HASH JOIN (Cost=1K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
9 8 PX RECEIVE (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
10 9 PX SEND (BROADCAST) OF 'SYS.:TQ10000' (Cost=228 Card=47K Bytes=1M) (PARALLEL_TO_PARALLEL) (BROADCAST)
11 10 PX BLOCK (ITERATOR) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_CHILD)
12 11 TABLE ACCESS (FULL) OF 'CUSTOMERS_PART' (TABLE) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
13 8 PX BLOCK (ITERATOR) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_CHILD)
14 13 TABLE ACCESS (FULL) OF 'SALES_PART' (TABLE) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
Distribution Method
| Distribution Method | 설명 |
| PQ_DISTRIBUTE( Inner, none, none ) | Full-Partition Wise 조인으로 유도할 때 사용한다. 다연히, 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝( equi-partitioning ) 돼 있을 때만 작동 |
| PQ_DISTRIBUTE( Inner, partition, none ) | Partial-Partition Wise 조인으로 유도할 때 사용하며, outer 테이블을 inner 테이블 파티션기준에 따라 파티셔닝하라는 의미임 inner 테이블이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때만 작동한다. |
| PQ_DISTRIBUTE( Inner, none, partition ) | Partial-Partition Wise 조인으로 유도할 때 사용하며, inner 테이블을 outer 테이블 파티션기준에 따라 파티셔닝 하라는 의미임 |
| PQ_DISTRIBUTE( Inner, hash, hash ) | OUTER, INNER 크기가 비슷할 때, 조인 키 컬럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝하라는 의미임 |
| PQ_DISTRIBUTE( innert, broadcast, none ) | OUTER 테이블이 작을때, outer 테이블을 Boardcast 하라는 의미임 |
| PQ_DISTRIBUTE( inner, none, broadcast ) | INNER 테이블이 작을때, inner 테이블을 Broadcast 하라는 의미임 |
SELECT /*+ parallel PQ_DISTRIBUTE(s, none, none ) */
c.cust_last_name
, COUNT(*)
FROM customers_HEAP c
, sales_part s
WHERE c.time_id = s.time_id
AND s.time_id BETWEEN TO_DATE('01-01-1990', 'DD-MM-YYYY') AND TO_DATE('01-12-2000', 'DD-MM-YYYY')
GROUP BY c.cust_last_name
HAVING COUNT(*) > 100;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4K Card=75M Bytes=3G)
1 0 PX COORDINATOR
2 1 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10001' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_SERIAL) (QC (RANDOM))
3 2 FILTER (PARALLEL_COMBINED_WITH_CHILD)
4 3 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
5 4 PX RECEIVE (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
6 5 PX SEND (HASH) OF 'SYS.:TQ10000' (Cost=4K Card=75M Bytes=3G) (PARALLEL_TO_PARALLEL) (HASH)
7 6 HASH (GROUP BY) (Cost=4K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
8 7 PX PARTITION RANGE (ITERATOR) (Cost=1K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_CHILD)
9 8 HASH JOIN (Cost=1K Card=75M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
10 9 TABLE ACCESS (FULL) OF 'CUSTOMERS_PART' (TABLE) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
11 9 TABLE ACCESS (FULL) OF 'SALES_PART' (TABLE) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
SELECT /*+ parallel PQ_DISTRIBUTE(s, PARTITION, none ) */
c.cust_last_name
, COUNT(*)
FROM customers_HEAP c
, sales_part s
WHERE c.time_id = s.time_id
AND s.time_id BETWEEN TO_DATE('01-01-1990', 'DD-MM-YYYY') AND TO_DATE('01-12-2000', 'DD-MM-YYYY')
GROUP BY c.cust_last_name
HAVING COUNT(*) > 100;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4K Card=74M Bytes=3G)
1 0 PX COORDINATOR
2 1 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10002' (Cost=4K Card=74M Bytes=3G) (PARALLEL_TO_SERIAL) (QC (RANDOM))
3 2 FILTER (PARALLEL_COMBINED_WITH_CHILD)
4 3 HASH (GROUP BY) (Cost=4K Card=74M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
5 4 PX RECEIVE (Cost=4K Card=74M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
6 5 PX SEND (HASH) OF 'SYS.:TQ10001' (Cost=4K Card=74M Bytes=3G) (PARALLEL_TO_PARALLEL) (HASH)
7 6 HASH (GROUP BY) (Cost=4K Card=74M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
8 7 HASH JOIN (Cost=1K Card=74M Bytes=3G) (PARALLEL_COMBINED_WITH_PARENT)
9 8 PART JOIN FILTER (CREATE) OF 'SYS.:BF0000' (Cost=228 Card=46K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
10 9 PX RECEIVE (Cost=228 Card=46K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
11 10 PX SEND (PARTITION (KEY)) OF 'SYS.:TQ10000' (Cost=228 Card=46K Bytes=1M) (PARALLEL_TO_PARALLEL) (PARTITION (KEY))
12 11 PX BLOCK (ITERATOR) (Cost=228 Card=46K Bytes=1M) (PARALLEL_COMBINED_WITH_CHILD)
13 12 TABLE ACCESS (FULL) OF 'CUSTOMERS_HEAP' (TABLE) (Cost=228 Card=46K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
14 8 PX PARTITION RANGE (AND) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_CHILD)
15 14 TABLE ACCESS (FULL) OF 'SALES_PART' (TABLE) (Cost=823 Card=664K Bytes=6M) (PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
SELECT /*+ parallel PQ_DISTRIBUTE(s, NONE, PARTITION ) */
c.cust_last_name
, COUNT(*)
FROM customers_PART c
, sales_HEAP s
WHERE c.time_id = s.time_id
AND s.time_id BETWEEN TO_DATE('01-01-1990', 'DD-MM-YYYY') AND TO_DATE('01-12-2000', 'DD-MM-YYYY')
GROUP BY c.cust_last_name
HAVING COUNT(*) > 100;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3K Card=57M Bytes=2G)
1 0 PX COORDINATOR
2 1 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10002' (Cost=3K Card=57M Bytes=2G) (PARALLEL_TO_SERIAL) (QC (RANDOM))
3 2 FILTER (PARALLEL_COMBINED_WITH_CHILD)
4 3 HASH (GROUP BY) (Cost=3K Card=57M Bytes=2G) (PARALLEL_COMBINED_WITH_PARENT)
5 4 PX RECEIVE (Cost=3K Card=57M Bytes=2G) (PARALLEL_COMBINED_WITH_PARENT)
6 5 PX SEND (HASH) OF 'SYS.:TQ10001' (Cost=3K Card=57M Bytes=2G) (PARALLEL_TO_PARALLEL) (HASH)
7 6 HASH (GROUP BY) (Cost=3K Card=57M Bytes=2G) (PARALLEL_COMBINED_WITH_PARENT)
8 7 HASH JOIN (Cost=1K Card=57M Bytes=2G) (PARALLEL_COMBINED_WITH_PARENT)
9 8 PX PARTITION RANGE (ITERATOR) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_CHILD)
10 9 TABLE ACCESS (FULL) OF 'CUSTOMERS_PART' (TABLE) (Cost=228 Card=47K Bytes=1M) (PARALLEL_COMBINED_WITH_PARENT)
11 8 PX RECEIVE (Cost=686 Card=712K Bytes=6M) (PARALLEL_COMBINED_WITH_PARENT)
12 11 PX SEND (PARTITION (KEY)) OF 'SYS.:TQ10000' (Cost=686 Card=712K Bytes=6M) (PARALLEL_TO_PARALLEL) (PARTITION (KEY))
13 12 PX BLOCK (ITERATOR) (Cost=686 Card=712K Bytes=6M) (PARALLEL_COMBINED_WITH_CHILD)
14 13 TABLE ACCESS (FULL) OF 'SALES_HEAP' (TABLE) (Cost=686 Card=712K Bytes=6M) (PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------
부록 (병렬 파라미터)
| Parameter Name | 설명 | |
| Fast_start_parallel_rollback | 인스턴스 복구 시 SMON이 Query Salve를 사용하여 병렬로 롤백을 수행하도록 함 | |
| Parallel_adaptive_multi_user | 요청된 parallel degree에 대해서 query startup time 시의 system load에 근거 해서 degree를 축소한다. | |
| Parallel_automatic_tuning | 10g이후 사용되지 않는 파라미터. Parameter value가 Default일 때 오라클이 병렬도를 조정할 수 있도록 설정 | |
| Parallel_degree_limit | Auto Dop를 사용 시 문장에서 허용하는 최대 Dop 값 cpu count * parallel_theads_per_cpu * active_instance | |
| Parallel_degree_policy | Auto DOP 시 문장 대기열 및 메모리 병렬 실행을 사용할 지 여부를 제어 | |
| Parallel_execution_massage_size | 병렬 실행(병렬 질의, PDML(병렬 데이터 조작어), 병렬 복구, 복제)의 메시지 크기를 지정 2048 또는 4096보다 큰 값을 지정하면 보다 큰 공유 풀이 필요 보통 8k이상을 권고(16k) | |
| Parallel_foce_local | 병렬 실행을 현재 RAC인스턴스로 제한함 | |
| Parallel_instance_group | parallel_instance_group이 지정되어 있지 않을 경우 한 node에서 수행한 PQ에 대해 모든 active instance 들의 PQ 참여가 가능하다 | |
| Parallel_max_servers | 쿼리에서 사용될 수 있는 병렬서버의 임계치 (cpu_count * parallel_threadsper_cpu * (pga_aggreate_target > 0 = 2 else 1) * 5 사용 가능한 값: 0 - 256 CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, PARALLEL_ADAPTIVE_MULTI_USER 값에 따라 달라 질 수 있음 | |
| Parallel_min_servers | 병렬 실행을 위해 인스턴스를 시작했을 때 Oracle이 생성하는 질의 서버 프로세스의 최소 개수를 지정합니다. 사용 가능한 값: 0에서 PARALLEL_MAX_SERVERS까지입니다. 기본값 : 0 | |
| Parallel_min_percent | 병렬실행에 필요한 병렬 프로세스의 최소 요구율, 요청된 parallel degree의 parallel_min_percent 만큼 여유가 없으면 직렬로 실행되거나 ORA-12827 error을 return함 병렬 실행에 필요한 스레드의 최소 비율을 퍼센트 단위로 지정 적절 한 수의 질의 슬래이브를 병렬 실행에 사용할 수 없을 경우 오류 메시지를 표시하고 질의가 순차적으로 실행되지 않도록 하려면 이 매개변수를 설정 사용 가능한 값: 0 - 100 기본값 : 0(이 매개변수를 사용하지 않음을 나타냄) | |
| Parallel_min_time_threshold | 최적화된 추정 실행 시간, 이것 보다 큰 값의 경우 문장은 병렬 쿼리 및 Auto Dop도출 후보가 됨 | |
| Parallel_server | 클러스터 데이터베이스 옵션을 활성화하려면 PARALLEL_SERVER를 TRUE로 설정 사용 가능한 값: TRUE | FALSE 기본값 : FALSE |
| Parallel_server_instance | 클러스터 데이터베이스 매개변수로 병렬 실행 슬래이브 생성에 사용되는 병렬 인스턴스 그룹의 식별에 사용됨 병렬 작업은 해당 INSTANCE_GROUPS 매개변수 일치 그룹을 지정한 인스턴스에 대해서만 병렬 실행 슬래이브를 생성 사용 가능한 값: 그룹 이름을 나타내는 문자열 기본값 : 현재 활성화된 모든 인스턴스로 구성된 그룹 | |
| Parallel_server_target | 병렬 서버의 큐잉에 사용할 때까지의 병렬서버 프로세스 수, parallel_degree_poicy가 auto로 설정되어야 만 활성활 됨 | |
| Parallel_threads_per_cpu | 병렬 실행 중 또는 병렬 적응 알고리즘 및 로드 밸런싱 알고리즘을 조정하기 위해 CPU가 처리할 수 있는 스레드 또는 프로세스의 수 사용 가능한 값: 0이 아닌 임의의 값 기본값 : 운영 체제에 따라 다름(일반적으로 2) | |
| Recovery_parallelism | 병렬복구 시 병렬도를 지정 , 병렬복구를 사용하기 위해 설정해야 함. | |
| _PX_use_large_pool | true이면 parallel_automatic_tuning=true가 아니더라도 parallel_execution_message가 large pool을 사용하게 한다. |