전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
목록
그래뉼과 Distribute 0 0 84,721

by 구루비스터디 병렬처리 Distribute Granules [2018.09.27]

병렬처리 그래뉼과 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_tuning10g이후 사용되지 않는 파라미터. Parameter value가 Default일 때 오라클이 병렬도를 조정할 수 있도록 설정
Parallel_degree_limitAuto Dop를 사용 시 문장에서 허용하는 최대 Dop 값
cpu count * parallel_theads_per_cpu * active_instance
Parallel_degree_policyAuto DOP 시 문장 대기열 및 메모리 병렬 실행을 사용할 지 여부를 제어
Parallel_execution_massage_size병렬 실행(병렬 질의, PDML(병렬 데이터 조작어), 병렬 복구, 복제)의 메시지 크기를 지정
2048 또는 4096보다 큰 값을 지정하면 보다 큰 공유 풀이 필요
보통 8k이상을 권고(16k)
Parallel_foce_local병렬 실행을 현재 RAC인스턴스로 제한함
Parallel_instance_groupparallel_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_pooltrue이면 parallel_automatic_tuning=true가 아니더라도 parallel_execution_message가 large pool을 사용하게 한다.
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4069

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입