이펙티브 오라클 (2008년)
병렬 작업을 사용할 시기 알기 0 0 3,001

by 구루비스터디 Parallel 병렬작업 [2009.04.30]


I. Parallel이란?

1. Parallel이란?

  • 최근 10년간 데이터베이스 기술의 발전과 더불어 컴퓨터의 활용이 증가하면서 기업이 축적하고 있는 데이터 량도 상상할 수 없을 만큼 증가했다.
  • 하드웨어와 소프트웨어기술의 발전은 사용자로부터 더 많은 정보요구를 수용하게 만들었고 이렇게 축적된 정보량은 기하급수적으로 증가하면서 대용량 데이터를 보다 안전하고 효율적으로 관리하기 위한 파티셔닝 방안이 도입됐다.
  • 파티셔닝은 용량이 큰(지속적으로 증가하는) 테이블을 파티션이라는 보다 작은 단위로 나눔으로써 성능 저하를 방지하고 관리를 보다 쉽게 하고자 하는 개념이다.
  • 그렇지만 대용량의 데이터를 무조건 파티셔닝만 한다고 해서 목적이 이뤄지는 것은 아니다.
  • RDBMS의 데이터 저장구조, 파티셔닝 기법에 따라 파티셔닝의 개념적 차이가 존재하기 때문에 이를 이해하고 적절한 파티션 전략을 구사해야 관리 용이성과 성능을 확보할 수 있다.


2. Parallel Operation의 장점

Normal Execution
  • Normal Execution의 경우 하나의 process가 여러 SQL문들을 sequential하게 처리한다.


Parallel execution
  • Parallel execution의 경우 여러 process들이 동시에 작업을 함으로서 performance를 크게 향상시킬 수 있으며, 특히 data-intensive한 DSS(Decision Support System) 및 VLDB(Very Large Database) 환경에서 효과가 크다.
  • 특히 여러 CPU를 사용하여 SMP(Symmetric Multiprocessing), clustered systems, MPP(Massively Parallel Systems)의 성능을 극대화할 수 있다.


3. Parallel에 관련된 Hidden Parameter

  • (Oracle 10.2.0.3 기준)
NAMEDESCRIPTION
_log_parallelismNumber of log buffer strands
_log_parallelism_dynamicEnable dynamic strands
_log_parallelism_maxMaximum number of log buffer strands
_log_private_parallelismNumber of private log buffer strands for zero-copy redo
_log_private_parallelism_mulActive sessions multiplier to deduce number of private strands
_mv_refresh_force_parallel_queryforce materialized view refreshes to use parallel query
_olap_parallel_update_small_groupOLAP parallel update pagespace by group
_olap_parallel_update_small_thresholdOLAP parallel update threshold for number of small pagespaces
_olap_parallel_update_thresholdOLAP parallel update threshold in pages
_optimizer_percent_paralleloptimizer percent parallel
_parallel_adaptive_max_usersmaximum number of users running with default DOP
_parallel_broadcast_enabledenable broadcasting of small inputs to hash and sort merge joins
_parallel_default_max_instancesdefault maximum number of instances for parallel query
_parallel_execution_message_alignAlignment of PX buffers to OS page boundary
_parallel_fake_class_pctfake db-scheduler percent used for testing
_parallel_fixwrite_bucketNumber of buckets for each round of fix write
_parallel_load_bal_unitnumber of threads to allocate per instance
_parallel_load_balancingparallel execution load balanced slave allocation
_parallel_min_message_poolminimum size of shared pool memory to reserve for pq servers
_parallel_recovery_stopatstop at position to step through SMON
_parallel_replay_msg_limitNumber of messages for each round of parallel replay
_parallel_server_idle_timeidle time before parallel query server dies (in 1/100 sec)
_parallel_server_sleep_timesleep time between dequeue timeouts (in 1/100ths)
_parallel_slave_acquisition_waittime(in seconds) to wait before retrying slave acquisition
_parallel_txn_globalenable parallel_txn hint with updates and deletes
_parallelism_cost_fudge_factorset the parallelism cost fudge factor
_rcfg_parallel_fixwriteif TRUE enables parallel fixwrite at reconfiguration
_rcfg_parallel_replayif TRUE enables parallel replay and cleanup at reconfiguration
_rcfg_parallel_verifyif TRUE enables parallel verify at reconfiguration
fast_start_parallel_rollbackmax number of parallel recovery slaves that may be used
parallel_adaptive_multi_userenable adaptive setting of degree for multiple user streams
parallel_automatic_tuningenable intelligent defaults for parallel execution parameters
parallel_execution_message_sizemessage buffer size for parallel execution
parallel_instance_groupinstance group to use for all parallel operations
parallel_max_serversmaximum parallel query servers per instance
parallel_min_percentminimum percent of threads required for parallel query
parallel_min_serversminimum parallel query servers per instance
parallel_serverif TRUE startup in parallel server mode
parallel_server_instancesnumber of instances to use for sizing OPS SGA structures
parallel_threads_per_cpunumber of parallel execution threads per CPU
recovery_parallelismnumber of server processes to use for parallel recovery


II. Parallel operation이 사용되는 사례

1. 사례 연구

'A ~ Z'의 26개 값을 가진 Parent 테이블 생성(Small Size)

/***************************************************************************************************/
/* 1. A ~ Z 코드값 존재하는 테이블 생성                                                            */
/***************************************************************************************************/
CREATE TABLE APPS.SMALL_TABLE AS
SELECT CHR(65 + LEVEL - 1) CODE
FROM   DUAL
CONNECT BY LEVEL <= 26;

/***************************************************************************************************/
/* 2. 통계정보 생성                                                                                */
/***************************************************************************************************/
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('APPS',
                                'SMALL_TABLE',
                                CASCADE => TRUE);
END;
/

/***************************************************************************************************/
/* 3. 통계정보 확인                                                                                */
/***************************************************************************************************/
SMALL_TABLE
-----------
Rows=26                                 Blocks=13
Empty Blocks=0                          Avg Space=0
Chain Count=0                           Avg Row Length=2
Avg Space Freelist Blocks=0             Freelist Blocks=0
Sample Size=26                          Last Analyze=2008/08/28
Partitioned=NO                          

  Column Name                      Nullable Column Type     Distinct    Buckets
  -------------------------------- -------- ------------- ---------- ----------
  CODE                                      VARCHAR2(100)         26          1

  INDEX
  -----------------------------------------------------------------------------
  No Index


'A ~ Z'의 값을 각각 100만건씩 가진 Child 테이블 생성(Big Size. 2600만건)

/***************************************************************************************************/
/* 1. 260만건 마다 코드값을 넣고 나머지는 순차값                                                   */
/***************************************************************************************************/
-- 1. Normal Execution
CREATE TABLE APPS.BIG_TABLE AS
SELECT CHR(65 + DECODE(MOD(LEVEL, 1000000), 
                       0, 
                       TRUNC(LEVEL / 1000000), 
                       TRUNC(LEVEL / 1000000) + 1) - 1)
        CODE,
       DECODE(MOD(LEVEL, 1000000), 0, 1000000, MOD(LEVEL, 1000000)) SEQ_VAL
FROM   DUAL
CONNECT BY LEVEL <= 26000000;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.20          0          0          0           0
Execute      1    327.15     393.06          0       5735     134669    26000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    327.16     393.27          0       5735     134669    26000000

-- 2. Parallel Execution
CREATE TABLE APPS.BIG_TABLE PARALLEL(DEGREE 8) AS
SELECT /*+ PARALLEL(TEMP 8) */
       CHR(65 + DECODE(MOD(LEVEL, 1000000), 
                       0, 
                       TRUNC(LEVEL / 1000000), 
                       TRUNC(LEVEL / 1000000) + 1) - 1)
        CODE,
       DECODE(MOD(LEVEL, 1000000), 0, 1000000, MOD(LEVEL, 1000000)) SEQ_VAL
FROM   DUAL
CONNECT BY LEVEL <= 26000000;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.19          0          0          0           0
Execute      1    259.70     287.24         44     264623     138679    26000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    259.71     287.43         44     264623     138679    26000000


/***************************************************************************************************/
/* 2. 인덱스 생성                                                                                  */
/***************************************************************************************************/
-- 1. Normal Execution
CREATE INDEX APPS.BIG_TABLE_N1 ON APPS.BIG_TABLE (CODE) INITRANS 11
TABLESPACE APPS_TS_TX_DATA COMPUTE STATISTICS;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0          2          0           0
Execute      1    123.91     447.62     110728      53495     179564           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    123.92     447.66     110728      53497     179564           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                       2974        0.18         37.53
  direct path write temp                         32        0.12          0.90
  direct path read temp                        3584        0.28         42.26
  direct path write                               2        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00


-- 2. Parallel Execution
CREATE INDEX APPS.BIG_TABLE_N1 ON APPS.BIG_TABLE (CODE) INITRANS 11
TABLESPACE APPS_TS_TX_DATA COMPUTE STATISTICS PARALLEL 8;

ALTER INDEX APPS.BIG_TABLE_N1 NOPARALLEL;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          2          0           0
Execute      1      1.62      83.39      68673     350170     163662           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.62      83.41      68673     350172     163662           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  os thread startup                              16        0.06          0.82
  PX Deq: Join ACK                                8        0.00          0.00
  PX Deq Credit: send blkd                        6        0.01          0.02
  PX Deq: Parse Reply                            12        0.01          0.05
  PX Deq: Execute Reply                         161        1.96         80.29
  PX qref latch                               43948        0.00          0.11
  PX Deq: Table Q qref                            2        0.01          0.01
  db file sequential read                        28        0.01          0.19
  rdbms ipc reply                                 7        0.00          0.00
  log file sync                                   1        0.00          0.00
  PX Deq: Signal ACK                              7        0.00          0.00
  enq: PS - contention                            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

/***************************************************************************************************/
/* 3. 통계정보 생성                                                                                */
/***************************************************************************************************/
-- 1. Normal Execution
EXEC FND_STATS.GATHER_TABLE_STATS(OWNNAME=>'APPS',TABNAME=>'BIG_TABLE',PERCENT=>100,GRANULARITY=>'ALL') ;

SELECT /*+ parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
       COUNT(*),
       COUNT("GRP_P2"),
       COUNT(DISTINCT "GRP_P2"),
       SUM(SYS_OP_OPNSIZE("GRP_P2")),
       SUBSTRB(DUMP(MIN("GRP_P2"), 16, 0, 32), 1, 120),
       SUBSTRB(DUMP(MAX("GRP_P2"), 16, 0, 32), 1, 120),
       COUNT("CNT_N1"),
       COUNT(DISTINCT "CNT_N1"),
       SUM(SYS_OP_OPNSIZE("CNT_N1")),
       SUBSTRB(DUMP(MIN("CNT_N1"), 16, 0, 32), 1, 120),
       SUBSTRB(DUMP(MAX("CNT_N1"), 16, 0, 32), 1, 120)
FROM   "APPS"."BIG_TABLE" T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.11          0          0          0           0
Execute      1      0.03       1.16          0         13          0           0
Fetch        2      0.84      75.87          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.88      77.16          0         13          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT GROUP BY (cr=13 pr=0 pw=0 time=77044277 us)
     12   PX COORDINATOR  (cr=13 pr=0 pw=0 time=76839548 us)
      0    PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND HASH :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0        SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
      0         PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL BIG_TABLE (cr=0 pr=0 pw=0 time=0 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.16          0.16
  PX Deq: Join ACK                                6        0.00          0.03
  PX Deq Credit: send blkd                        2        0.00          0.00
  PX Deq: Parse Reply                             3        0.00          0.00
  PX Deq: Execute Reply                          89        1.95         74.94
  PX qref latch                               30402        0.00          0.06
  PX Deq: Signal ACK                              4        0.09          0.09
  enq: PS - contention                            2        0.00          0.00

UPDATE FND_STATS_HIST
SET    LAST_GATHER_DATE     = SYSDATE,
       LAST_GATHER_END_TIME = SYSDATE
WHERE  SCHEMA_NAME = UPPER(:B6)
AND    OBJECT_NAME = UPPER(:B5)
AND    (PARTITION = UPPER(:B4) OR :B4 IS NULL)
AND    (COLUMN_TABLE_NAME = UPPER(:B3) OR :B3 IS NULL)
AND    OBJECT_TYPE = UPPER(:B2)
AND    REQUEST_ID = :B1
AND    HISTORY_MODE = 'L'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.01          0          5          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          0          5          2           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  FND_STATS_HIST (cr=5 pr=0 pw=0 time=440 us)
      1   INDEX RANGE SCAN FND_STATS_HIST_U1 (cr=4 pr=0 pw=0 time=140 us)(object id 210282)



-- 2. Parallel Execution
EXEC FND_STATS.GATHER_TABLE_STATS(OWNNAME=>'APPS',TABNAME=>'BIG_TABLE',PERCENT=>100,DEGREE=>16,GRANULARITY=>'ALL') ;

SELECT /*+ parallel(t,16) parallel_index(t,16) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
       COUNT(*),
       COUNT("GRP_P2"),
       COUNT(DISTINCT "GRP_P2"),
       SUM(SYS_OP_OPNSIZE("GRP_P2")),
       SUBSTRB(DUMP(MIN("GRP_P2"), 16, 0, 32), 1, 120),
       SUBSTRB(DUMP(MAX("GRP_P2"), 16, 0, 32), 1, 120),
       COUNT("CNT_N1"),
       COUNT(DISTINCT "CNT_N1"),
       SUM(SYS_OP_OPNSIZE("CNT_N1")),
       SUBSTRB(DUMP(MIN("CNT_N1"), 16, 0, 32), 1, 120),
       SUBSTRB(DUMP(MAX("CNT_N1"), 16, 0, 32), 1, 120)
FROM   "APPS"."BIG_TABLE" T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.07          0          0          0           0
Execute      1      0.05       1.87          0         13          0           0
Fetch        2      4.99      76.97          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.05      78.92          0         13          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT GROUP BY (cr=13 pr=0 pw=0 time=78850041 us)
     45   PX COORDINATOR  (cr=13 pr=0 pw=0 time=78678113 us)
      0    PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)


      0       PX SEND HASH :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0        SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
      0         PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL BIG_TABLE (cr=0 pr=0 pw=0 time=0 us)

UPDATE FND_STATS_HIST
SET    LAST_GATHER_DATE     = SYSDATE,
       LAST_GATHER_END_TIME = SYSDATE
WHERE  SCHEMA_NAME = UPPER(:B6)
AND    OBJECT_NAME = UPPER(:B5)
AND    (PARTITION = UPPER(:B4) OR :B4 IS NULL)
AND    (COLUMN_TABLE_NAME = UPPER(:B3) OR :B3 IS NULL)
AND    OBJECT_TYPE = UPPER(:B2)
AND    REQUEST_ID = :B1
AND    HISTORY_MODE = 'L'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          4          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          4          2           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  FND_STATS_HIST (cr=4 pr=0 pw=0 time=391 us)
      1   INDEX RANGE SCAN FND_STATS_HIST_U1 (cr=4 pr=0 pw=0 time=118 us)(object id 210282)

/***************************************************************************************************/
/* 4. 통계정보 확인                                                                                */
/***************************************************************************************************/
BIG_TABLE
---------
Rows=26,000,000                         Blocks=47,584
Empty Blocks=0                          Avg Space=0
Chain Count=0                           Avg Row Length=7
Avg Space Freelist Blocks=0             Freelist Blocks=0
Sample Size=26,000,000                  Last Analyze=2008/08/28
Partitioned=NO                          

  Column Name                      Nullable Column Type     Distinct    Buckets
  -------------------------------- -------- ------------- ---------- ----------
  CODE                                      NUMBER                26          1
  SEQ_VAL                                   NUMBER         1,000,000          1

  INDEX
  -----------------------------------------------------------------------------
  BIG_TABLE_N1 : CODE
      Type=NORMAL, Uniq=No, Distinct=26, Rows=26,000,000, Last Analyze=2008/08/28


2. 정리

  • PDCL 사용시 반드시 사용 후 NOPARALLEL 구문을 사용하여 변경 해 주어야 한다.


III. Parallel 처리 방식


1. Parallel 처리 방식

  • 대용량 Parallel 쿼리 에서 조인을 사용할 경우 성능이 저하되는 경우가 많이 있다.
  • 이경우의 원인은 여러가지가 있다. 원인 중에서 가장 치명적인 것은 잘못된 Row Distribution (Row 의 분배방법) 에 있다.
  • 옵티마이져의 잘못된 Row Distribution 을 피하기 위하여 원리및 사용방법 그리고 최후의 방법으로 힌트를 통한 잘못된 Row Distribution 을 피하기 등에 대하여 알아본다.
  • Row Distribution Method
HASH조인기준컬럼을 Hash Function 을 이용하여 양쪽 테이블을 Mapping 하는 방식임.
조인컬럼 기준으로 각각의 Temp 성 매핑테이블을 만들고 마지막에 Join 하는 방식이다.
BROADCAST조인된 양쪽테이블에서 한쪽 테이블의 모든 Row를 다른쪽 테이블을 Scan 시에 분배하는 방식이다.
BroadCast 받는 Table의 Scan 시에 자동으로 조인이 되므로 따로 Join Operation이 필요가 없다.
하지만 Broadcast 하는측 테이블의 사이즈가 커지면 Parallel Slave 마다 Outer Table을 반복적으로
BroadCast 해야 하므로 비효율이 커진다.
PARTITION파티션을 이용하여 조인이된 양쪽테이블의 Row 를 분배한다.
2개의 조인된 테이블 중에서 조인컬럼을 기준으로 반드시 한쪽 테이블은 파티션이 되어 있어야한다.
파티션이 안된 테이블을 조인컬럼을 기준으로 파티션하여 Row를 분배하는 방식이다.
이분배방식은 Partition Wise Join 과 관계가 있다.
NONE이미 조인컬럼기준으로 파티션 된 테이블은 Row 가 파티션기준으로 자동으로 분배되거나
Broadcast 방식일 경우 분배를 받는쪽 테이블의 Row 는 따로 분배가 필요 없으므로 None 으로 표현된다.


  • 조인시 Row Distribution 의 Combination
HASH, HASH양쪽 테이블의 사이즈가 비슷하고 Hash Join 이나 Sort Merge 조인을 사용할때 권장된다.
BROADCAST, NONEOuter Table 의 사이즈와 Inner Table 의 사이즈를 비교하여 Outer 테이블의 사이즈가
훨씬적을때 권장된다. 예를들면 코드 테이블과 대용량 테이블을 조인할때 적격이다.
왜냐하면 Inner Table 의 Granule 갯수 만큼 Outer 테이블의 Row 가 반복해서 제공되어야
하기 때문에 Broadcast 하는쪽의 테이블이 크면 I/O 양이 급격히 늘어난다.
NONE, BROADCAST'BROADCAST, NONE'의 방법과 같으나 순서가 정반대 이다. 다시말해 Inner 테이블이 Broadcast 된다.
Outer Table 의 사이즈와 Inner Table 의 사이즈를 비교하여 Inner 테이블의 사이즈가 훨씬적을때 권장된다.
PARTITION, NONEOuter 테이블을 조인된 컬럼기준으로 Partition을 하여 Row 를 분배하며 Partition Wise 조인을 한다.
NONE, PARTITIONInner 테이블을 조인된 컬럼기준으로 Partition을 하여 Row 를 분배하며 Partition Wise 조인을 한다.
NONE, NONE조인이되는 양측의 테이블이 이미 조인컬럼 기준으로 파티션이 되어 있을때 따로 분배가 필요없으므로
이런 Combination 이 발생한다.(양측 테이블이 파티션 기준으로 분배된다.)


2. Parallel 예제

  • HASH, HASH

-- 1. Normal Execution
SELECT /*+ USE_HASH(SM BT) FULL(SM) FULL(BT) */
       BT.*
FROM   SMALL_TABLE SM,
       BIG_TABLE   BT
WHERE  SM.CODE = BT.CODE
AND    SM.CODE = :B1 -- 'Z'
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.011          0          0          0          0
Execute      1    0.000        0.002          0          0          0          0
Fetch   100001    4.960       42.586      42260     143161          0    1000000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total   100003    4.960       42.598      42260     143161          0    1000000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
1000000   HASH JOIN  (cr=143161 pr=42260 pw=0 time=5346589 us)
      1    TABLE ACCESS FULL SMALL_TABLE (cr=3 pr=0 pw=0 time=98 us)
1000000    TABLE ACCESS FULL BIG_TABLE (cr=143158 pr=42260 pw=0 time=3345890 us)

Wait Event Name                                      Count Wait(sec)  Max Wait
-------------------------------------------------- ------- ---------- --------
SQL*Net message to client                            100001      0.098    0.000
SQL*Net message from client                          100001    183.285    0.002
db file scattered read                                 2699      5.272    0.001
--------------------------------------------------- ------- --------- --------
Total                                                202701    188.65

-- 2. Parallel Execution
SELECT /*+ ORDERED PARALLEL(SM 4) PARALLEL(BT 4) PQ_DISTRIBUTE(BT HASH HASH) USE_HASH (BT)*/
       BT.*
FROM   SMALL_TABLE SM,
       BIG_TABLE   BT
WHERE  SM.CODE = BT.CODE
AND    SM.CODE = :B1 -- 'Z'
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.009          0          0          0          0
Execute      1    0.020        0.082          0         16          3          0
Fetch   100001    0.650        8.657          0          0          0    1000000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total   100003    0.670        8.748          0         16          3    1000000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
1000000   PX COORDINATOR  (cr=14 pr=0 pw=0 time=4510760 us)
      0    PX SEND QC (RANDOM) :TQ10002 (cr=0 pr=0 pw=0 time=0 us)
      0     HASH JOIN BUFFERED (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND HASH :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0        PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL SMALL_TABLE (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND HASH :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0        PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL BIG_TABLE (cr=0 pr=0 pw=0 time=0 us)

Wait Event Name                                      Count Wait(sec)  Max Wait
-------------------------------------------------- ------- ---------- --------
PX Deq: Parse Reply                                       6      0.039    0.031
PX Deq: Join ACK                                          4      0.003    0.000
PX Deq: Signal ACK                                        5      0.098    0.000
PX Deq: Execute Reply                                    57      2.996    0.000
SQL*Net message to client                            100001      0.104    0.000
enq: PS - contention                                      2      0.000    0.000
PX qref latch                                         16455      0.036    0.000
SQL*Net message from client                          100001    181.771    0.002
PX Deq Credit: send blkd                                  3      0.002    0.000
--------------------------------------------------- ------- --------- --------
Total                                                216534    185.05


  • BROADCAST, NONE

SELECT /*+ ORDERED PARALLEL(SM 4) PARALLEL(BT 4) PQ_DISTRIBUTE(BT BROADCAST NONE) USE_HASH(SM BT)*/
       BT.*
FROM   SMALL_TABLE SM,
       BIG_TABLE   BT
WHERE  SM.CODE = BT.CODE
AND    SM.CODE = :B1 -- 'Z'
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.005          0          0          0          0
Execute      1    0.030        0.051          0         16          3          0
Fetch   100001    0.240        9.451          0          0          0    1000000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total   100003    0.270        9.507          0         16          3    1000000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
1000000   PX COORDINATOR  (cr=14 pr=0 pw=0 time=3345092 us)
      0    PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0     HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0        PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL SMALL_TABLE (cr=0 pr=0 pw=0 time=0 us)
      0      PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0       TABLE ACCESS FULL BIG_TABLE (cr=0 pr=0 pw=0 time=0 us)

Wait Event Name                                      Count Wait(sec)  Max Wait
-------------------------------------------------- ------- ---------- --------
PX Deq: Parse Reply                                       6      0.012    0.003
PX Deq: Join ACK                                          6      0.003    0.001
PX Deq: Signal ACK                                        4      0.098    0.000
PX Deq: Execute Reply                                    29      1.326    0.000
SQL*Net message to client                            100001      0.119    0.000
enq: PS - contention                                      2      0.000    0.000
PX qref latch                                             9      0.000    0.000
SQL*Net message from client                          100001    192.782    0.002
PX Deq: Table Q Normal                                    1      0.000    0.000
PX Deq Credit: send blkd                                  4      0.002    0.000
--------------------------------------------------- ------- --------- --------
Total                                                200063    194.34

"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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