12.1 버전에 추가된 PX SELECTOR 오퍼레이션을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
아래 쿼리는 t2 테이블을 serial로 읽었다. 12.1 이하 버전에서는 실행 계획에서 PX SEND BROADCAST 오퍼레이션을 확인할 수 있다. V$PQ_TQSTAT 뷰를 조회해보면 QC가 t2 테이블을 읽은 것을 알 수 있다.
SQL Monitor 결과에서도 QC가 t2 테이블에 대해 수행한 I/O 통계(Read Requests 87회, Read Bytes 78MB)을 확인할 수 있다.
12.1 이후 버전은 실행 계획에서 PX SELECTOR 오퍼레이션을 확인할 수 있다. V$PQ_TQSTAT 뷰를 조회해보면 P004 병렬 프로세스가 t2 테이블을 읽은 것을 알 수 있다. 12.1 버전부터 병렬 쿼리에서 serial로 조회되는 테이블은 QC가 아닌 병렬 프로세스에서 처리되며, PX SELECTOR 오퍼레이션으로 표시된다.
SQL Monitor 결과에서도 P004 병렬 프로세스가 t2 테이블에 대해 수행한 I/O 통계(Read Requests 95회, Read Bytes 78MB)을 확인할 수 있다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, LPAD ('X', 4000, 'X') AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT * FROM t1;
-- 2-1
SELECT /*+ MONITOR ORDERED FULL(A) FULL(B) PARALLEL(A 4) USE_HASH(B) */ * FROM t1 a, t2 b WHERE b.c1 = a.c1;
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | PX COORDINATOR | | 1 | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 0 | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 4 | Q1,01 | PCWP | |
|* 5 | HASH JOIN | | 4 | Q1,01 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 4 | Q1,01 | PCWC | |
|* 7 | TABLE ACCESS FULL | T1 | 52 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | 4 | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | 4 | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | 0 | | S->P | BROADCAST | -- !
| 11 | TABLE ACCESS FULL| T2 | 1 | | | |
------------------------------------------------------------------------------------
-- 2-2
SELECT tq_id, server_type, process, num_rows FROM v$pq_tqstat;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS
----- ----------- ------- --------
0 Producer QC 40000
0 Consumer P000 10000
0 Consumer P001 10000
0 Consumer P002 10000
0 Consumer P003 10000
1 Producer P000 1
1 Producer P001 1
1 Producer P002 1
1 Producer P003 1
1 Consumer QC 4
10 행이 선택되었습니다.
-- 2-3 Parallel Execution Details (DOP=4 , Servers Allocated=4) ============================================================ | Name | Type | Server# | Buffer | Read | Read | | | | | Gets | Reqs | Bytes | ============================================================ | PX Coordinator | QC | | 10009 | 87 | 78MB | -- ! | p000 | Set 1 | 1 | 2708 | 41 | 21MB | | p001 | Set 1 | 2 | 2548 | 32 | 20MB | | p002 | Set 1 | 3 | 2352 | 30 | 18MB | | p003 | Set 1 | 4 | 2548 | 35 | 20MB | ============================================================ SQL Plan Monitoring Details (Plan Hash Value=626684163) ================================================================================= | Id | Operation | Name | Rows | Read | Read | Mem | | | | | (Actual) | Reqs | Bytes | (Max) | ================================================================================= | 0 | SELECT STATEMENT | | 1 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | PX COORDINATOR | | 4 | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 4 | | | | | 4 | SORT AGGREGATE | | 4 | | | | | 5 | HASH JOIN | | 10000 | | | 7M | | 6 | PX BLOCK ITERATOR | | 10000 | | | | | 7 | TABLE ACCESS FULL | T1 | 10000 | 138 | 78MB | | | 8 | BUFFER SORT | | 40000 | | | 1M | | 9 | PX RECEIVE | | 40000 | | | | | 10 | PX SEND BROADCAST | :TQ10000 | 40000 | | | | | 11 | TABLE ACCESS FULL | T2 | 10000 | 87 | 78MB | | -- ! =================================================================================
-- 3-1
SELECT /*+ MONITOR ORDERED FULL(A) FULL(B) PARALLEL(A 4) USE_HASH(B) */ * FROM t1 a, t2 b WHERE b.c1 = a.c1;
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | PX COORDINATOR | | 1 | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 0 | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 4 | Q1,01 | PCWP | |
|* 5 | HASH JOIN | | 4 | Q1,01 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 4 | Q1,01 | PCWC | |
|* 7 | TABLE ACCESS FULL | T1 | 79 | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 4 | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 0 | Q1,00 | S->P | BROADCAST |
| 10 | PX SELECTOR | | 4 | Q1,00 | SCWC | | -- !
| 11 | TABLE ACCESS FULL| T2 | 1 | Q1,00 | SCWP | |
------------------------------------------------------------------------------------
-- 3-2
SELECT tq_id, server_type, process, num_rows FROM v$pq_tqstat;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS
----- ----------- ------- --------
0 Producer P004 40000 -- !
0 Producer P005 0
0 Producer P006 0
0 Producer P007 0
0 Consumer P000 10000
0 Consumer P001 10000
0 Consumer P002 10000
0 Consumer P003 10000
1 Producer P000 1
1 Producer P001 1
1 Producer P002 1
1 Producer P003 1
1 Consumer QC 4
13 행이 선택되었습니다.
-- 3-3 Parallel Execution Details (DOP=4 , Servers Allocated=8) ============================================================ | Name | Type | Server# | Buffer | Read | Read | | | | | Gets | Reqs | Bytes | ============================================================ | PX Coordinator | QC | | 20 | | . | | p000 | Set 1 | 1 | 1991 | 25 | 14MB | | p001 | Set 1 | 2 | 2103 | 29 | 15MB | | p002 | Set 1 | 3 | 4072 | 60 | 29MB | | p003 | Set 1 | 4 | 2803 | 36 | 20MB | | p004 | Set 2 | 1 | 10044 | 95 | 78MB | -- ! | p005 | Set 2 | 2 | | | . | | p006 | Set 2 | 3 | | | . | | p007 | Set 2 | 4 | | | . | ============================================================ SQL Plan Monitoring Details (Plan Hash Value=273711751) ================================================================================= | Id | Operation | Name | Rows | Read | Read | Mem | | | | | (Actual) | Reqs | Bytes | (Max) | ================================================================================= | 0 | SELECT STATEMENT | | 1 | | | . | | 1 | SORT AGGREGATE | | 1 | | | . | | 2 | PX COORDINATOR | | 4 | | | . | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 4 | | | . | | 4 | SORT AGGREGATE | | 4 | | | . | | 5 | HASH JOIN | | 10000 | | | 7MB | | 6 | PX BLOCK ITERATOR | | 10000 | | | . | | 7 | TABLE ACCESS FULL | T1 | 10000 | 150 | 78MB | . | | 8 | PX RECEIVE | | 40000 | | | . | | 9 | PX SEND BROADCAST | :TQ10000 | 40000 | | | . | | 10 | PX SELECTOR | | 10000 | | | . | | 11 | TABLE ACCESS FULL | T2 | 10000 | 95 | 78MB | . | -- ! =================================================================================
'Oracle > Tuning' 카테고리의 다른 글
| INLIST ITERATOR 성능 개선 (0) | 2018.12.26 |
|---|---|
| PX SEND 1 SLAVE 오퍼레이션 (0) | 2018.12.21 |
| PRECOMPUTE_SUBQUERY 힌트 (0) | 2018.12.14 |
| DB 링크 튜닝 기법 (0) | 2018.12.13 |
| JPPD와 서브 쿼리 팩토링 (0) | 2018.12.11 |
