5. 병렬 처리에 관한 기타 상식 (by nav012) [2013.01.18]
h1.병렬 처리 기타 상식
h4.Direct Path Read
- 버퍼 없이 Disk - PGA 로 읽어 일반적 Disk Read 에 비해 비교적 빠르나
- 사용 빈도가 높아 버퍼에 Cachied 된 데이터 일 경우 오히려 병렬 처리시 느려질 수 있음
h4.병렬 DML
- Parallel Query(SELECT), Parallel DDL 은 기본 enable 되어 있음
- DML 작업에서는 Paralle 힌트를 주어도 QC 가 작업 담당
- 아래와 같은 Altering 작업 해 주어야 병렬 DML 가능
SQL> alter session enable parallel dml;
h4.병렬 DML 수행 중 Lock
- 테이블 전체에 Exclusive 모드로 Lock 획득하므로 주의
h4.Parallel Index Scan - Index Past full scan 이 아닐 경우는 병렬 인덱스 스캔 동작하지 않음
- partitioned index 일 경우는 파티션 별로 병렬 스캔 가능
h4.Index NL Join - 인덱스 스캔 기반의 병렬 NL 조인 가능
1. Outer full scan --> Inner index scan
EXPLAIN PLAN FOR
SELECT
/*+ PARALLEL(A 2) ORDERED USE_NL(B) FULL(A) */
*
FROM
T_PART A,
T_NON_PART B
WHERE A.VAL=B.VAL
AND A.ID = 5
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110K| 2484K| 36938 (1)| | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 12 | 1 (0)| | | Q1,00 | P->S | QC (RAND) |
| 3 | TABLE ACCESS BY INDEX ROWID| T_NON_PART | 1 | 12 | 1 (0)| | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | 110K| 2484K| 36938 (1)| | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 110K| 1188K| 47 (3)| 5 | 5 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | T_PART | 110K| 1188K| 47 (3)| 5 | 5 | Q1,00 | PCWP | |
|* 7 | *INDEX RANGE SCAN* | I_NON_PART2 | 1 | | 1 (0)| | | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
2. 병렬 인덱스 스캔 드라이빙
드라이빙하는 인덱스가 파티션 인덱스여야 하며, Inner 테이블/인덱스, Outer Table 파티션 여부는 상관 없음
EXPLAIN PLAN FOR
SELECT
/*+ ORDERED USE_NL(B) PARALLEL_INDEX(A I_PART 3) */
*
FROM
T_PART A,
T_NON_PART B
WHERE A.VAL=B.VAL
AND A.VAL <= 2000000
;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 249K| 5845K| 152K (1)| | |
| 1 | TABLE ACCESS BY INDEX ROWID| T_NON_PART | 1 | 12 | 1 (0)| | |
| 2 | NESTED LOOPS | | 249K| 5845K| 152K (1)| | |
| 3 | PARTITION LIST ALL | | 249K| 2922K| 2834 (4)| 1 | 5 |
|* 4 | TABLE ACCESS FULL | T_PART | 249K| 2922K| 2834 (4)| 1 | 5 |
|* 5 | INDEX RANGE SCAN | I_NON_PART2 | 1 | | 1 (0)| | |
------------------------------------------------------------------------------------------------
인덱스 패러럴 안됨
사용 필요한 경우
- Outer - Inner 가 둘 대 대용량 테이블
- Outer Table 조건에 대한 선택도가 낮은데(distinct 한 키카 많음) 인덱스가 없음
- (--> 수행 빈도가 낮아서 인덱스 안만들었을 경우)
- Inner Table 조인 컬럼에 인덱스 있을 때
- 수행 빈도가 낮을 때
- 책 예시
Table: 현물채결 t Outer / 현물호가 o Inner - 둘 다 일별 파티션
조건: t.채결일자, t.채결 수량으로 걸러진 건수가 매우 적음
결과: 5건 선택을 위해 현물채결 전체 테이블 스캔은 피할 수 없지만 현물호가 테이블은 NL 로 Index unique scan 가능
스칼라 서브쿼리
- 일반적으로 QC 가 클라이언트에 전송하면서 수행
- Order by 절 주면 Parallel Process 가 수행
- 병렬 처리는 기본적으로 FTS 처럼 대량 처리를 하는데 Row 마다 random access 발생시키면 성능 저하 가능
스칼라 서브쿼리 사용
SELECT
/*+ PARALLEL(A 4) */
ID,
VAL,
(SELECT ID FROM T_NON_PART WHERE VAL=A.VAL)
FROM
T_PART A
WHERE A.VAL <= 2000000
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.001 0.004 0 18 0 0
Fetch 2001 0.475 0.514 0 62053 0 20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2003 0.476 0.518 0 62071 0 20000
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20000 *TABLE ACCESS BY INDEX ROWID T_NON_PART (cr=62053 pr=0 pw=0 time=343712 us)*
20000 *INDEX RANGE SCAN I_NON_PART2 (cr=42053 pr=0 pw=0 time=247262 us)OF I_NON_PART2 (NONUNIQUE)*
20000 PX COORDINATOR (cr=18 pr=0 pw=0 time=93225 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T_PART PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
스칼라 서브쿼리 + ORDER BY
SELECT
/*+ PARALLEL(A 4) */
ID,
VAL,
(SELECT ID FROM T_NON_PART WHERE VAL=A.VAL)
FROM
T_PART A
WHERE A.VAL <= 2000000
*ORDER BY ID*
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.004 0.004 0 1 3 0
Execute 1 0.004 0.123 0 18 0 0
Fetch 2001 0.007 0.595 0 0 0 20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2003 0.015 0.721 0 19 3 20000
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 *TABLE ACCESS BY INDEX ROWID T_NON_PART (cr=0 pr=0 pw=0 time=0 us)*
0 *(INDEX RANGE SCAN I_NON_PART2 (cr=0 pr=0 pw=0 time=0 us)OF I_NON_PART2 (NONUNIQUE)*
20000 PX COORDINATOR (cr=18 pr=0 pw=0 time=700630 us)
0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 SORT ORDER 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 RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T_PART PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
- 성능개선 방안
1 QC 수행 유도 불가능할 시 가급적 일반 Join 문으로 변환 필요
*Join 변환 + ORDER BY*
SELECT
/*+ ORDERED FULL(A) FULL(B) PARALLEL(A 4) PARALLEL(B 4) */
A.ID,
A.VAL,
B.ID
FROM
T_PART A,
T_NON_PART B
WHERE A.VAL=B.VAL
AND A.VAL <= 2000000
*ORDER BY A.ID*
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.003 0.010 0 21 0 0
Fetch 2001 0.033 2.887 0 0 0 20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2003 0.036 2.897 0 21 0 20000
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20000 PX COORDINATOR (cr=21 pr=0 pw=0 time=4815773 us)
0 PX SEND QC (ORDER) :TQ10003 (cr=0 pr=0 pw=0 time=0 us)
0 SORT ORDER 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 RANGE :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 JOIN FILTER CREATE :BF0000 (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 PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T_PART PARTITION: 1 5 (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 JOIN FILTER USE :BF0000 (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 T_NON_PART (cr=0 pr=0 pw=0 time=0 us)
2 병렬 쿼리를 부분집합 처리
SELECT
ID,
VAL,
(SELECT ID FROM T_NON_PART WHERE VAL=A.VAL)
FROM
(SELECT /*+ FULL(A) PARALLEL(A 4) NO_MERGE */ *
FROM T_PART
*WHERE VAL <= 2000000*
ORDER BY ID
) A
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2001 1.256 1.577 0 74470 0 20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2003 1.256 1.577 0 74470 0 20000
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: LIM (ID=58)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
20000 TABLE ACCESS BY INDEX ROWID T_NON_PART (cr=62053 pr=0 pw=0 time=331868 us)
20000 INDEX RANGE SCAN I_NON_PART2 (cr=42053 pr=0 pw=0 time=239675 us)OF I_NON_PART2 (NONUNIQUE)
20000 PARTITION LIST ALL PARTITION: 1 5 (cr=12417 pr=0 pw=0 time=317292 us)
20000 VIEW (cr=12417 pr=0 pw=0 time=1119944 us)
20000 SORT ORDER BY (cr=12417 pr=0 pw=0 time=1119933 us)
20000 TABLE ACCESS FULL T_PART PARTITION: 1 5 (cr=12417 pr=0 pw=0 time=346 us)
테스트 시 그냥 처음 쿼리(스칼라 서브 쿼리)가 제일 빠름
h3.함수 사용 시 parallel_enable
- 병렬 처리 시에는 처음 수행한 Session(QC) 외에 별도의 Parallel Session 생성하여 작업 처리
- Session 레벨에서 관리되는 값 사용 시 Serial 과 Parallel 차이에 따른 결과 값 생길 수 있어 강제로 Serial 하게 변환 됨
- parallel_enabld 은 사용자가 강제로 Parallel 로 동작하도록 하는 키워드
- 병렬 처리로 인한 결과값에 대해서는 사용자의 책임
SQL> SELECT /*+ PARALLEL(A 4) */ CALL_COUNTER.FUNCALLCOUNTER , VAL FROM T_NON_PART A
2 WHERE A.VAL < 10
ORDER BY 1
; 3 4
FUNCALLCOUNTER VAL
-------------- --------------------------------------------------
1 1
2 6
3 2
4 7
5 3
6 8
7 4
8 9
9 5
9 rows selected.
SQL> SELECT /*+ PARALLEL(A 4) */ CALL_COUNTER.FUNCALLCOUNTER2 , VAL FROM T_NON_PART A
WHERE A.VAL < 10
ORDER BY 1
; 2 3 4
FUNCALLCOUNTER2 VAL
--------------- --------------------------------------------------
1 3
1 1
1 2
1 4
2 8
2 6
2 9
2 7
3 5
9 rows selected.
SQL> EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(A 4) */ FUNCALLCOUNTER , VAL FROM T_NON_PART A;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
------------------------------------------------------------------------
| 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 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T_NON_PART | Q1,00 | PCWP | |
------------------------------------------------------------------------
SQL> EXPLAIN PLAN FOR
CREATE TABLE T_PERMANENT3
PARALLEL 4
AS
SELECT /*+ PARALLEL(A 4) */ CALL_COUNTER.FUNCALLCOUNTER , VAL FROM T_NON_PART A;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | *PX COORDINATOR FORCED SERIAL*| | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T_PERMANENT3 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T_NON_PART | Q1,00 | PCWP | |
----------------------------------------------------------------------------------
그냥 select 시에는 강제 Serial 변환 안됨, CTAS 작업 할때만 변환
h3.Rownum + Order by
- Table Scan 은 Parallel Server 가, Ordering 은 QC 가 수행함
ROWNUM
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ PARALLEL(A 4) */ ROWNUM AS RNUM1 FROM T_NON_PART A WHERE VAL < 10000000
3 ;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 245K| 2401K| 787 (4)| | | |
| 1 | COUNT | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 245K| 2401K| 787 (4)| Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 245K| 2401K| 787 (4)| Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| T_NON_PART | 245K| 2401K| 787 (4)| Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------
ROWNUM + ORDER BY
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ PARALLEL(A 4) */ ROWNUM AS RNUM1 FROM T_NON_PART A WHERE VAL < 10000000
ORDER BY VAL
3 ;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 245K| 2401K| | 1046 (4)| | | |
| 1 | *SORT ORDER BY* | | 245K| 2401K| 7736K| 1046 (4)| | | |
| 2 | COUNT | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM)| :TQ10000 | 245K| 2401K| | 787 (4)| Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 245K| 2401K| | 787 (4)| Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T_NON_PART | 245K| 2401K| | 787 (4)| Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
ORDER BY
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ PARALLEL(A 4) */ ID FROM T_NON_PART A WHERE VAL < 10000000
ORDER BY VAL
3 ;
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 245K| 2882K| | 1082 (4)| | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 245K| 2882K| | 1082 (4)| Q1,01 | P->S | QC (ORDER) |
| 3 | *SORT ORDER BY* | | 245K| 2882K| 9656K| 1082 (4)| Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 245K| 2882K| | 787 (4)| Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 245K| 2882K| | 787 (4)| Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 245K| 2882K| | 787 (4)| Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| T_NON_PART | 245K| 2882K| | 787 (4)| Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
h3.기타 주의사항
- workarea_size_policy = manual 시 sort_area_size 가 모든 병렬 서버에게 적용
사이즈 계산 잘못하면 OS 페이징 등 유발 가능
- parallel degree 지정 안하면 num_cpu * parallel_threads_per_cpu 만큼 할당됨
adaptive multiuser 사용하는게 아니면 반드시 degree 지정
adaptive multiuser 란?
- 쿼리 안에 Parallel degree 는 가급적 동일하게 맞추자
안그러면 대충 알아서 지정 됨
- Optimizer 에 의해 인덱스 스캔이 선택 될 경우 Parallel 무시됨
parallel 힌트 사용 시 FTS 힌트 함께 사용
- Optimizer 에 의해 Full table scan 선택 될 경우 Parallel_index 무시됨
parallel_index 힌트 사용 시 index / index_ffs 힌트 사용
- 부분범위 처리 방식에는 일부 Fetch 하고 나서도 Parallel session 은 대기상태로 떠있음
Resource 낭비 상황 가능하니 주의