병렬 처리에 관한 기타 상식 (by niceboyous) [2013.01.29]
병렬 처리에 관한 기타 상식
Direct Path Read
일반적인 블록 읽기는 DB 버퍼 캐시를 경유한다. 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고, 찾지 못할 때 디스크에서 읽는다.
디스크에서 읽을 때도 바로 읽지 않고 버퍼 캐시에 적재한 후에 읽는다.
그런데 버퍼캐시 히트율이 낮은 대용량 데이터를 건건히 버퍼 캐시를 거쳐서 읽는다면 오히려 성능이 나빠지게 마련이다.
오라클은 그래서 병렬 방식으로 Full Scan 할 때는 버퍼 캐시를 거치지 않고 곧바로 PGA 영역으로 읽어들이는 Direct Path Read 방식을 사용한다(1권 6장 5절 참조). 병렬도를 2로 주면 쿼리 수행 속도가 2배보다 훨씬 더 향상되는 이유가 바로 여기에 있다.
Direct Path Read 과정에서 읽기 Call이 완료될 때까지 대기가 발생하는데, 모니터링해 보면 direct path read 이벤트로 측정된다.
자주 사용되고 버퍼 캐시에 충분히 적재될 만큼의 중소형 테이블을 병렬 쿼리로 읽을 때는 오히려 성능이 나빠지는 경우가 있는데, 버퍼 경합이 없는 한 디스크 I/O가 메모리 I/O보다 빠를수 없기 때문이다.
게다가 Direct Path Read를 하려면 메모리와 디스크간 동기화를 위한 체크포인트를 먼전 수행해야 한다.
따라서 병렬 쿼리의 Direct Path Read 효과를 극대화하려면 그만큼 테이블이 아주 커야 한다.
h3.병렬 DML
- 병열처리가 가능해지려면 쿼리,DML,DDL을 수행하기전 각각 아래와 같은 명령을 먼저 수행해 주어야한다.
alter session enable parallel query;
alter session enable parallel dml;
alter session enable parallel ddl;
기본적으로 query,ddl의 경우는 활성화 되어 있지만, dml의 경우는 사용자가 명시적으로 활성화시켜 주어야한다.
- alter session enable parallel dml이 수행되기 전 과 후 비교
- 수행 전
SCOTT@ora10g>create table t
2 partition by range(no)(
3 partition p1 values less than(25000)
4 , partition p2 values less than(50000)
5 , partition p3 values less than(75000)
6 , partition p4 values less than(maxvalue)
7 )
8 as
9 select rownum no, lpad(rownum, 10, '0') no2 from dual
10 connect by level <= 100000;
Table created.
SCOTT@ora10g>explain plan for
2 update /*+ parallel(t 4) */ t set no2 = lpad(no, 5, '0');
Explained.
SCOTT@ora10g>@? /edbms/admin/utlxplp
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 84854 | 2071K| 20 (0)| | | | | |
| 1 | UPDATE | T | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 84854 | 2071K| 20 (0)| | | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 84854 | 2071K| 20 (0)| 1 | 4 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T | 84854 | 2071K| 20 (0)| 1 | 4 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
SCOTT@ora10g>commit;
Commit complete.
- 수행 후
SCOTT@ora10g>alter session enable parallel dml;
Session altered.
SCOTT@ora10g>explain plan for
2 update /*+ parallel(t 4) */ t set no2 = lpad(no, 5, 10);
Explained.
SCOTT@ora10g>@?/rdbms/admin/utlxplp
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 84854 | 2071K| 20 (0)| | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 84854 | 2071K| 20 (0)| | | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | T | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 84854 | 2071K| 20 (0)| 1 | 4 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T | 84854 | 2071K| 20 (0)| 1 | 4 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
병렬 인덱스 스캔
Index Fast Full Scan이 아닌 한 인덱스는 기본적으로 병렬로 스캔할 수 없다.
파티션된 인덱스일 때는 병렬 스캔이 가능하며, 파티션 기반 Granule이므로 당연히 병렬도는 파티션 개수 이하로만 지정할 수 있다
!그림 7-12.jpg!
병렬 NL 조인
병렬 조인은 항상 Table Fall Scan을 이용한 해시 조인 또는 소트 머지 조인으로 처리된다고 생각하기 쉽다.
하지만 아래와 같이 인덱스 스캔을 기반으로 한 병렬 NL 조인도 가능하다.
TEST@ora10g>create table emp
2 partition by range(sal) (
partition p1 values less than(1000)
3 4 , partition p2 values less than(2000)
5 , partition p3 values less than(3000)
6 , partition p4 values less than(MAXVALUE) )
7 as
8 select * from scott.emp ;
Table created.
TEST@ora10g>create index emp_sal_idx on emp(sal) local;
Index created.
TEST@ora10g>create table dept as select * from scott.dept;
Table created.
TEST@ora10g>alter table dept add constraint dept_pk primary key(deptno);
Table altered.
TEST@ora10g>set autotrace traceonly exp;
TEST@ora10g>select /*+ ordered use_nl(d) full(e) parallel(e 2) */ *
2 from emp e, dept d
3 where d.deptno = e.deptno
4 and e.sal >= 1000 ;
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1404 | 3 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 12 | 1404 | 3 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 12 | 1404 | 3 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 12 | 1044 | 2 (0)| 00:00:01 | 2 | 4 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | EMP | 12 | 1044 | 2 (0)| 00:00:01 | 2 | 4 | Q1,00 | PCWP | |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 7 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("E"."SAL" IS NOT NULL)
7 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
그림으로 표현하자면 다음과 같다
h3.병렬 인덱스 스캔으로 드라이빙하는 경우
emp 테이블에 생성해 둔 emp_sal_idx 인덱스를 먼저 드라이빙해 병렬 NL 조인을 수행하는 경우를 살펴보자.
TEST@ora10g>select /*+ ordered use_nl(d) index(e emp_sal_idx)
2 parallel_index(e emp_sal_idx 3) */ *
3 from emp e, dept d
4 where d.deptno = e.deptno
and e.sal >= 1000 ;
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1404 | 7 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 12 | 1404 | 7 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 12 | 1404 | 7 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 4 | PX PARTITION RANGE ITERATOR | | 12 | 1044 | 6 (0)| 00:00:01 | 2 | 4 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| EMP | 12 | 1044 | 6 (0)| 00:00:01 | 2 | 4 | Q1,00 | PCWP | |
|* 6 | INDEX RANGE SCAN | EMP_SAL_IDX | 1 | | 3 (0)| 00:00:01 | 2 | 4 | Q1,00 | PCWP | |
| 7 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 30 | 1 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 8 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."SAL">=1000)
8 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
위의 설명을 그림으로 표현하면 다음과 같다
병렬 NL 조인의 효용성
NL 조인을 병렬로 수행하는 것도 가능하지만 실무적으로 활용할 기회가 많지는 않다.
유용하게 쓸 수 있는 상황을 가정해 보자.
- Outer 테이블과 Inner 테이블이 둘 다 초대용량 테이블이다.(-> 어느 한쪽이 작은 테이블이면 병렬 해시 조인으로 해결 가능하다.)
- Outer 테이블에 사용된 특정 조건의 선택도가 매우 낮은데 그 컬럼에 대한 인덱스가 없다.
- Inner 쪽 조인 컬럼에는 인덱스가 있다.
- 수행 빈도가 낮다.(-> 수행 빈도가 높다면 Outer 쪽에도 인덱스를 만드는 편이 낮다.)
두 개의 초대용량 테이블을 소트 머지나 해시 방식으로 조인하려면 아주 많은 리소스가 필요해 부담스러운 데다, 조인 결과 집합까지 소량일 때는 큰 비효율이 아닐 수 없다.(Full Partition Wise 조인이 가능한 상황이라면 조금 낫다.)
사실, Outer 테이블을 스캔하면서 조건 필터링을 한 결과 건수가 매우 소량(낮은 선택도)이라면 원칙적으로 인덱스를 생성하는 것이 최적의 해법이다.
하지만 수행빈도가 낮은 쿼리를 위해 대용량 테이블에 인덱스를 하나 더 두는 것은 여간 고민스러운 문제가 아니다.
병렬 쿼리와 ROWNUM
SQL에 rownum을 포함하면 쿼리문을 병렬로 실행하는 데에 제약을 받게 되므로 주의해야 한다.
아래 실행계획을 보면, rownum 결과치로 정렬하는 것이 아님에도 sort order by를 QC가 담당{color:green}(-> sort order by가 px coordinator 위쪽에 나타남){color}하게 된 것을 볼 수 있다.
TEST@ora10g>create table t
2 as
3 select rownum no, lpad(rownum, 10, '0') no2 from dual
4 connect by level <= 100000;
Table created.
TEST@ora10g>set autotrace traceonly exp;
TEST@ora10g>select /*+ parallel(t) */ no, no2, rownum
2 from t
3 order by no2;
Execution Plan
----------------------------------------------------------
Plan hash value: 124124330
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99924 | 2439K| | 448 (3)| 00:00:06 | | | |
| 1 | SORT ORDER BY | | 99924 | 2439K| 7096K| 448 (3)| 00:00:06 | | | |
| 2 | COUNT | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | |
| 4 | PX SEND QC (RANDOM)| :TQ10000 | 99924 | 2439K| | 39 (3)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 99924 | 2439K| | 39 (3)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T | 99924 | 2439K| | 39 (3)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
마찬가지로 병렬 DML 문장에도 rownum를 사용하는 순간 병렬 처리에 제약을 받게 되므로 주의가 필요하다.
TEST@ora10g>set autotrace off;
TEST@ora10g>commit;
Commit complete.
TEST@ora10g>alter session enable parallel dml;
Session altered.
TEST@ora10g>explain plan for
2 update /*+ parallel(t 4) */ t set no2 = lpad(rownum, 5, '0');
Explained.
TEST@ora10g>@?/rdbms/admin/utlxplp
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 99924 | 1170K| 20 (5)| 00:00:01 | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20001 | 99924 | 1170K| 20 (5)| 00:00:01 | Q2,01 | P->S | QC (RAND)
| 3 | UPDATE | T | | | | | Q2,01 | PCWP |
| 4 | BUFFER SORT | | | | | | Q2,01 | PCWC |
| 5 | PX RECEIVE | | 99924 | 1170K| 20 (5)| 00:00:01 | Q2,01 | PCWP |
| 6 | PX SEND HASH (BLOCK ADDRESS)| :TQ20000 | 99924 | 1170K| 20 (5)| 00:00:01 | | S->P | HASH (BLOCK
| 7 | COUNT | | | | | | | |
| 8 | PX COORDINATOR | | | | | | | |
| 9 | PX SEND QC (RANDOM) | :TQ10000 | 99924 | 1170K| 20 (5)| 00:00:01 | Q1,00 | P->S | QC (RAND)
| 10 | PX BLOCK ITERATOR | | 99924 | 1170K| 20 (5)| 00:00:01 | Q1,00 | PCWC |
| 11 | TABLE ACCESS FULL | T | 99924 | 1170K| 20 (5)| 00:00:01 | Q1,00 | PCWP |
---------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
h3.병렬처리가 바람직한 경우
동시 사용자 수가 적은 애플리케이션 환경에서 직렬로 처리할 때 보다 성능 개선효과가 확실할 때.
OLTP성 시스템 환경이더라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스 사용률을 감소시킬 수 있을때.
h3.병렬쿼리와 관계된 주의사항
workarea_size_policy를 manual로 설정한다면, 사용자가 지정한 sort_area_size가 모든 병렬서버에 적용되므로 적절한 sort_area_size를 설정하지 않은 경우 OS레벨에서 과도한 페이징이 발생하고 심할 경우 시스템이 마비될 수 있다.
병렬도를 지정하지 않으면 cpu_count X parallel_threads_per_cpu만큼의 병렬 프로세스가 할당되어 의도하지 않은 수의 프로세스가 작동하게 된다.
실행계획에 P->P 가 나타날 때문 지정한 병렬도의 두 배수만큼 병렬 프로세스가 필요하다는 것이다. ( producer, consumer)
쿼리 블록마다 병렬도를 다르게 지정하면 여러가지 규칙에 따라 최종병렬도가 결정되어 사용된다. 결국 쿼리 작성 시 병렬도를 모두 같게 지정하는 것이 바람직하다.
Parallel 힌트를 사용할 때는 반드시 Full힌트도 함께 사용하는 것이 바람직하다. 간혹 옵티마이져에 의해 index 스캔이 선택된 경우 Parallel 이 무시되는 경우가 발생하기 때문이다.
병렬 DML 수행 시 Exclusive 모드로 Table lock이 걸리므로 업무 트랜젝션을 고려해야 한다.
테이블이나 인덱스를 빠르게 생성하려고 parallel옵션을 주었다면 작업을 완료하자마자 noparallel로 돌려놓는 것을 잊지 말아야 한다.
부분범위 처리 방식으로 조회하면서 병렬 쿼리를 사용한 경우 필요한 만큼의 fetch이후 곧바로 커서를 닫아줘야 한다. ( Orange, Toad같은 툴의 경우 리소스를 해제하지 못하는 경우가 있으므로 select * from dual로 새로운 쿼리를 수행하여 이전 커서를 닫아 주는 방식을 취할 수 있다. )