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 |
/***************************************************************************************************/
/* 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
/***************************************************************************************************/
/* 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
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 으로 표현된다. |
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 이 발생한다.(양측 테이블이 파티션 기준으로 분배된다.) |
-- 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
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