병렬 작업을 사용할 시기 알기 (by kkabong) [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 기준)
| NAME | DESCRIPTION | |
|---|---|---|
| _log_parallelism | Number of log buffer strands | |
| _log_parallelism_dynamic | Enable dynamic strands | |
| _log_parallelism_max | Maximum number of log buffer strands | |
| _log_private_parallelism | Number of private log buffer strands for zero-copy redo | |
| _log_private_parallelism_mul | Active sessions multiplier to deduce number of private strands | |
| _mv_refresh_force_parallel_query | force materialized view refreshes to use parallel query | |
| _olap_parallel_update_small_group | OLAP parallel update pagespace by group | |
| _olap_parallel_update_small_threshold | OLAP parallel update threshold for number of small pagespaces | |
| _olap_parallel_update_threshold | OLAP parallel update threshold in pages | |
| _optimizer_percent_parallel | optimizer percent parallel | |
| _parallel_adaptive_max_users | maximum number of users running with default DOP | |
| _parallel_broadcast_enabled | enable broadcasting of small inputs to hash and sort merge joins | |
| _parallel_default_max_instances | default maximum number of instances for parallel query | |
| _parallel_execution_message_align | Alignment of PX buffers to OS page boundary | |
| _parallel_fake_class_pct | fake db-scheduler percent used for testing | |
| _parallel_fixwrite_bucket | Number of buckets for each round of fix write | |
| _parallel_load_bal_unit | number of threads to allocate per instance | |
| _parallel_load_balancing | parallel execution load balanced slave allocation | |
| _parallel_min_message_pool | minimum size of shared pool memory to reserve for pq servers | |
| _parallel_recovery_stopat | stop at | |
| _parallel_replay_msg_limit | Number of messages for each round of parallel replay | |
| _parallel_server_idle_time | idle time before parallel query server dies (in 1/100 sec) | |
| _parallel_server_sleep_time | sleep time between dequeue timeouts (in 1/100ths) | |
| _parallel_slave_acquisition_wait | time(in seconds) to wait before retrying slave acquisition | |
| _parallel_txn_global | enable parallel_txn hint with updates and deletes | |
| _parallelism_cost_fudge_factor | set the parallelism cost fudge factor | |
| _rcfg_parallel_fixwrite | if TRUE enables parallel fixwrite at reconfiguration | |
| _rcfg_parallel_replay | if TRUE enables parallel replay and cleanup at reconfiguration | |
| _rcfg_parallel_verify | if TRUE enables parallel verify at reconfiguration | |
| fast_start_parallel_rollback | max number of parallel recovery slaves that may be used | |
| parallel_adaptive_multi_user | enable adaptive setting of degree for multiple user streams | |
| parallel_automatic_tuning | enable intelligent defaults for parallel execution parameters | |
| parallel_execution_message_size | message buffer size for parallel execution | |
| parallel_instance_group | instance group to use for all parallel operations | |
| parallel_max_servers | maximum parallel query servers per instance | |
| parallel_min_percent | minimum percent of threads required for parallel query | |
| parallel_min_servers | minimum parallel query servers per instance | |
| parallel_server | if TRUE startup in parallel server mode | |
| parallel_server_instances | number of instances to use for sizing OPS SGA structures | |
| parallel_threads_per_cpu | number of parallel execution threads per CPU | |
| recovery_parallelism | number 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 처리 방식
- (Reference Site : 'Science of DataBase Blog'(http://scidb.tistory.com/))
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, NONE | Outer Table 의 사이즈와 Inner Table 의 사이즈를 비교하여 Outer 테이블의 사이즈가 훨씬적을때 권장된다. 예를들면 코드 테이블과 대용량 테이블을 조인할때 적격이다. 왜냐하면 Inner Table 의 Granule 갯수 만큼 Outer 테이블의 Row 가 반복해서 제공되어야 하기 때문에 Broadcast 하는쪽의 테이블이 크면 I/O 양이 급격히 늘어난다. |
| NONE, BROADCAST | 'BROADCAST, NONE'의 방법과 같으나 순서가 정반대 이다. 다시말해 Inner 테이블이 Broadcast 된다. Outer Table 의 사이즈와 Inner Table 의 사이즈를 비교하여 Inner 테이블의 사이즈가 훨씬적을때 권장된다. |
| PARTITION, NONE | Outer 테이블을 조인된 컬럼기준으로 Partition을 하여 Row 를 분배하며 Partition Wise 조인을 한다. |
| NONE, PARTITION | Inner 테이블을 조인된 컬럼기준으로 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