Oracle 12c의 버그 Fix으로 인해 구문 수준의 PARALLEL 힌트를 사용한 병렬 쿼리의 경우 JPPD에 대한 실행 계획이 변경될 수 있습니다.
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 100');
CREATE TABLE t2 (c1, c2) AS SELECT CEIL (ROWNUM / 100), LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
CREATE INDEX t2_x1 ON t2 (c1);
아래는 일반적인 병렬 NL 조인의 실행 계획입니다. 1개의 DFO 트리가 작업을 수행합니다.
-- 2
SELECT /*+ PARALLEL(2) LEADING(A) USE_NL(B) INDEX(B) */
*
FROM t1 a
, t2 b
WHERE b.c1 = a.c1;
--------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
|* 7 | INDEX RANGE SCAN | T2_X1 | Q1,00 | PCWP | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
아래는 12.2 버전에서 JPPD를 사용한 병렬 NL 조인의 실행 계획입니다. 2개의 DFO(Data Flow Operation) 트리(Q1, Q2)가 생성된 것을 확인할 수 있습니다. 아울러 실행 계획 15, 16번에서 t2_x1 인덱스를 단일 PX 서버가 액세스하고, 불필요한 데이터 분배가 발생한 것을 알 수 있습니다. 이 실행 계획의 경우 V$PQ_TQSTAT 뷰에 정확한 통계가 저장되지 않는 것으로 보입니다. 다만 V$PX_PROCESS_TRACE 뷰의 트레이스 정보에서 PX SELECTOR 연산에 대한 오버헤드를 유추할 수 있습니다.
-- 3-1
SELECT /*+ PARALLEL(2) LEADING(A) USE_NL(B) INDEX(B) NO_MERGE(B) PUSH_PRED(B) */
*
FROM t1 a
, (SELECT c1
, COUNT (c2) AS cnt
FROM t2
GROUP BY c1) b
WHERE b.c1 = a.c1;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20000 | Q2,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | Q2,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q2,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q2,00 | PCWP | |
| 6 | VIEW PUSHED PREDICATE | | Q2,00 | PCWP | |
|* 7 | FILTER | | Q2,00 | PCWP | |
| 8 | SORT AGGREGATE | | Q2,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | -- !
| 10 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 11 | SORT AGGREGATE | | Q1,01 | PCWP | |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | Q1,01 | PCWP | |
| 13 | BUFFER SORT | | Q1,01 | PCWC | |
| 14 | PX RECEIVE | | Q1,01 | PCWP | |
| 15 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | Q1,00 | S->P | HASH (BLOCK| -- !
| 16 | PX SELECTOR | | Q1,00 | SCWC | | -- !
|* 17 | INDEX RANGE SCAN | T2_X1 | Q1,00 | SCWP | |
----------------------------------------------------------------------------------------------
-- 3-2: V$PX_PROCESS_TRACE
PID COMP FUNC
------------ ------------------ ----
PX_Control kxfxmai 3
PX_Control kxfxsStatus 1
PX_Control kxfxsp 2
PX_Control kxfxsp1 2
PX_Control kxfxsuf 2
PX_Messaging kxfpChkSlvJoinCond 1
PX_Messaging kxfpProcessJoin 4
PX_Messaging kxfpSendSessStats 2
PX_Messaging kxfpqrsod 2
PX_Messaging kxfpqsod 8
PX_Messaging kxfpqsod_slv_sod 2
PX_Messaging kxfprdp_int 3
PX_Scheduler qerpxSlaveFetch 8
PX_Scheduler qerpxStart 76 -- !
PX_Selector qerpselFetch 76 -- !
PX_Selector qerpselStart 75 -- !
앞선 쿼리에 OPTIMIZER_FEATURES_ENABLE('11.2.0.4') 힌트를 사용하면 1개의 DFO 트리가 작업을 수행하는 실행 계획이 생성됩니다.
-- 4-1
SELECT /*+ PARALLEL(2) LEADING(A) USE_NL(B) INDEX(B) NO_MERGE(B) PUSH_PRED(B)
OPTIMIZER_FEATURES_ENABLE('11.2.0.4') */
*
FROM t1 a
, (SELECT c1
, COUNT (c2) AS cnt
FROM t2
GROUP BY c1) b
WHERE b.c1 = a.c1;
-----------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
| 6 | VIEW PUSHED PREDICATE | | Q1,00 | PCWP | |
|* 7 | FILTER | | Q1,00 | PCWP | |
| 8 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 9 | TABLE ACCESS BY INDEX ROWID| T2 | Q1,00 | PCWP | |
|* 10 | INDEX RANGE SCAN | T2_X1 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------
-- 4-2: V$PX_PROCESS_TRACE
PID COMP FUNC
------------ ------------------ ----
PX_Control kxfxmai 4
PX_Control kxfxsStatus 2
PX_Control kxfxsp 4
PX_Control kxfxsp1 4
PX_Control kxfxsuf 2
PX_Messaging kxfpChkSlvJoinCond 2
PX_Messaging kxfpProcessJoin 8
PX_Messaging kxfpSendSessStats 2
PX_Messaging kxfpqrsod 2
PX_Messaging kxfpqsod 8
PX_Messaging kxfpqsod_slv_sod 2
PX_Messaging kxfprdp_int 4
PX_Scheduler qerpxSlaveFetch 14
PX_Scheduler qerpxStart 2
아래와 같이 t1 테이블에 대해 오브젝트 수준의 PARALLEL 힌트를 사용하면 t2 테이블이 직렬로 액세스되므로 앞선 쿼리와 동일한 실행 계획이 생성됩니다. 이 방법의 단점은 쿼리에 많은 테이블을 사용한 경우 각 테이블에 대해 오브젝트 수준의 PARALLEL 힌트를 사용해야 한다는 것입니다.
-- 5
SELECT /*+ PARALLEL(A 2) LEADING(A) USE_NL(B) INDEX(B) NO_MERGE(B) PUSH_PRED(B) */
*
FROM t1 a
, (SELECT c1
, COUNT (c2) AS cnt
FROM t2
GROUP BY c1) b
WHERE b.c1 = a.c1;
-----------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
| 6 | VIEW PUSHED PREDICATE | | Q1,00 | PCWP | |
|* 7 | FILTER | | Q1,00 | PCWP | |
| 8 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 9 | TABLE ACCESS BY INDEX ROWID| T2 | Q1,00 | PCWP | |
|* 10 | INDEX RANGE SCAN | T2_X1 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------
Oracle 12c의 이 동작은 13345888 버그 Fix와 관련이 있습니다. 이 버그 Fix을 통해 ROWID로 테이블을 병렬 조회할 수 있습니다.
-- 6
SELECT bugno, value, sql_feature, description, optimizer_feature_enable, is_default
FROM v$system_fix_control
WHERE bugno = 13345888;
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
-------- ----- ----------------- ------------------------------------- ------------------------ ----------
13345888 1 QKSFM_PQ_13345888 parallel table lookup access by rowid 12.1.0.1 1
1개의 행이 선택되었습니다.
아래 6-1번 쿼리는 Q1,00 PX 서버 세트의 단일 PX 서버가 t2_x1 인덱스를 조회한 결과를 Q1,01 PX 서버 세트로 분배하고, Q1,01 PX 서버 세트의 2개의 PX 서버가 t2 테이블을 조회합니다. 6-2번 쿼리는 병렬이 아닌 직렬로 수행됩니다.
-- 7-1
SELECT /*+ PARALLEL(2) INDEX(T2) */
*
FROM t2
WHERE c1 > 0;
---------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | Q1,01 | PCWP | |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | Q1,00 | S->P | HASH (BLOCK|
| 7 | PX SELECTOR | | Q1,00 | SCWC | |
|* 8 | INDEX RANGE SCAN | T2_X1 | Q1,00 | SCWP | |
---------------------------------------------------------------------------------------
-- 7-2
SELECT /*+ PARALLEL(2) INDEX(T2) OPT_PARAM('_fix_control' '13345888:0') */
*
FROM t2
WHERE c1 > 0;
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
|* 2 | INDEX RANGE SCAN | T2_X1 |
-----------------------------------------------------
OPT_PARAM('_fix_control' '13345888:0') 힌트를 사용하면 오브젝트 수준의 PARALLEL 힌트를 사용하지 않고 5번 쿼리와 동일한 실행 계획을 생성할 수 있습니다.
-- 8
SELECT /*+ PARALLEL(2) LEADING(A) USE_NL(B) INDEX(B) NO_MERGE(B) PUSH_PRED(B)
OPT_PARAM('_fix_control' '13345888:0') */
*
FROM t1 a
, (SELECT c1
, COUNT (c2) AS cnt
FROM t2
GROUP BY c1) b
WHERE b.c1 = a.c1;
-----------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
| 6 | VIEW PUSHED PREDICATE | | Q1,00 | PCWP | |
|* 7 | FILTER | | Q1,00 | PCWP | |
| 8 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 9 | TABLE ACCESS BY INDEX ROWID| T2 | Q1,00 | PCWP | |
|* 10 | INDEX RANGE SCAN | T2_X1 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------
참고로 인라인 뷰에서 테이블을 액세스하지 않으면 1개의 DFO 트리가 작업을 수행하는 실행 계획이 생성됩니다.
-- 9
SELECT /*+ PARALLEL(2) LEADING(A) USE_NL(B) INDEX(B) NO_MERGE(B) PUSH_PRED(B) */
*
FROM t1 a
, (SELECT c1
, COUNT (*) AS cnt
FROM t2
GROUP BY c1) b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
| 6 | VIEW PUSHED PREDICATE | | Q1,00 | PCWP | |
|* 7 | FILTER | | Q1,00 | PCWP | |
| 8 | SORT AGGREGATE | | Q1,00 | PCWP | |
|* 9 | INDEX RANGE SCAN | T2_X1 | Q1,00 | PCWP | |
---------------------------------------------------------------------------