5. 병렬 처리에 관한 기타 상식 (by sunshiny) [2010.12.05]
05. 병렬 처리에 관한 기타 상식
(1) 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 효과를 극대화하려면 그만큼 테이블이 아주 커야 한다.
(2) 병렬 DML
병렬 처리가 가능해지려면 쿼리, DML, DDL을 수행하기 전에 각각 아래와 같은 명령을 먼저 수행해 주어야 한다.
alter session enable parallel query;
alter session enable parallel dml;
alter session enable parallel ddl;
이와 관련해 각 세션의 상태를 v$session을 통해 확인할 수 있다.
select pq_status, pdml_status, pddl_status from v$session;
다행히 parallel query와 parallel ddl은 기본적으로 활성화돼 있으므로 사용자가 의도적으로 비활성화하지 않는 한 신경 쓸 필요가 없다.
하지만, parallel dml은 사용자가 명시적으로 활성화 해 주어야 하는데, 이 사실을 몰라 병렬 DML을 효과적으로 활용하지 못하는 개발팀을 여러 번 보았다.
아래 두 실행계획에서 똑같은 update문인데도 'alter session enable parallel dml' 문을 수행하기 전후 update 오퍼레이션 위치가 다름에 주목하기 바란다.
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 | |
--------------------------------------------------------------------------------------------------------------------
위의 병렬 서버는 T 테이블을 읽고서 갱신할 레코드 주소만 QC에게 전달하고, 실제 update는 QC가 수행한다.
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 | |
--------------------------------------------------------------------------------------------------------------------
여기서는 T 테이블을 읽은 병렬 서버가 update까지 직접 수행하며, 갱신한 건수만 QC에게 전달한다.
오라클 9iR1까지는 한 세그먼트를 두 개 이상 프로세스가 동시에 갱신할 수 없었다.
따라서 파티션되지 않은 테이블이라면 병렬로 갱신{color:green}(update, merge, delete){color}할 수가 없었고, 파티션 테이블일 때는 병렬도를 파티션 개수 이하로만 지정할 수 있었다. 즉, 오라클 9iR1까지 병렬 DML은 파티션 기반 Granule이었다.
오라클 9iR2부터 병렬 DML이 블록 기반 Granule로 바뀌었다.(메뉴얼에는 비파티션에 대한 병렬 DML이 여전히 불가능하다고 돼 있지만 9iR2 new Features를 보면 주요 개선사항 중 하나로 기술돼 있다.)
주의할 점은, 병렬 DML을 수행할 때 Exclusive 모드 테이블 Lock이 걸린다는 사실이다.
성능은 비교할 수 없을 정도로 빨라지겠지만 해당 테이블에 다른 트랜잭션이 DML을 수행하지 못하게 되므로 트랜잭션이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물이다.
(3) 병렬 인덱스 스캔
Index Fast Full Scan이 아닌 한 인덱스는 기본적으로 병렬로 스캔할 수 없다.
그림 7-12처럼 파티션된 인덱스일 때는 병렬 스캔이 가능하며, 파티션 기반 Granule이므로 당연히 병렬도는 파티션 개수 이하로만 지정할 수 있다.
(4) 병렬 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
위 실행계획을 그림으로 표시하면 그림 7-13과 같다.
Parallel Full Scan에는 블록 기반 Granule이 사용되므로 병렬도는 파티션 개수와 무관하다.
위에서는 병렬도를 2로 지정했지만 테이블 크기에 따라 자유롭게 더 큰 값을 지정할 수 있다.
물론 데이터량에 비해 병렬도를 너무 크게 지정하면 아무 일도 하지 않고 노는 프로세스가 생긴다.
병렬 인덱스 스캔으로 드라이빙하는 경우
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
위 실행계획을 그림으로 표시하면 그림 7-14와 같다.
그림 7-14와 같은 방식으로 병렬 NL 조인을 수행하려면, 드라이빙 인덱스{color:green}(emp_sal_idx){color}가 반드시 파티션 인덱스여야 한다.
드라이빙 테이블(emp)과 두 번째 인덱스(dept_pk) 및 테이블(dept)의 파티션 여부와는 상관없다.
인덱스를 드라이빙한 병렬 NL 조인에는 파티션 기반 Granule이 사용되므로 병렬도가 파티션 개수를 초과할 수 없다.
여기서는 세 개{color:green}(Pstart=2, Pstop=4){color} 파티션만 액세스하므로 병렬도를 3보다 크게 줄 수 없다.
크게 주더라도 실제 세 개 프로세스만 사용된다.
만약 병렬도를 2로 지정한다면 각각 하나씩 처리하다가 먼저 일을 마친 프로세스가 나머지 하나를 더 처리한다.
병렬 NL 조인의 효용성
지금 본 것처럼 NL 조인을 병렬로 수행하는 것도 가능하지만 실무적으로 활용할 기회가 많지는 않다.
유용하게 쓸 수 있는 상황을 가정해 보자.
- Outer 테이블과 Inner 테이블이 둘 다 초대용량 테이블이다.(-> 어느 한쪽이 작은 테이블이면 병렬 해시 조인으로 해결 가능하다.)
- Outer 테이블에 사용된 특정 조건의 선택도가 매우 낮은데 그 컬럼에 대한 인덱스가 없다.
- Inner 쪽 조인 컬럼에는 인덱스가 있다.
- 수행 빈도가 낮다.(-> 수행 빈도가 높다면 Outer 쪽에도 인덱스를 만드는 편이 낮다.)
두 개의 초대용량 테이블을 소트 머지나 해시 방식으로 조인하려면 아주 많은 리소스가 필요해 부담스러운 데다, 조인 결과 집합까지 소량일 때는 큰 비효율이 아닐 수 없다.(Full Partition Wise 조인이 가능한 상황이라면 조금 낫다.)
사실, Outer 테이블을 스캔하면서 조건 필터링을 한 결과 건수가 매우 소량(낮은 선택도)이라면 원칙적으로 인덱스를 생성하는 것이 최적의 해법이다.
하지만 수행빈도가 낮은 쿼리를 위해 대용량 테이블에 인덱스를 하나 더 두는 것은 여간 고민스러운 문제가 아니다.
(5) 병렬 쿼리와 스칼라 서브쿼리
병렬 쿼리에 트레이스를 걸면 QC의 트레이스 파일은 user_dump_dest 디렉토리 밑에 생기지만 병렬 서버의 트레이스 파일은 background_dump_dest 디렉토리 밑에 생긴다.
SYS@ora10g>show parameter dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /usr/local/oracle10g/admin/oracle10/bdump
core_dump_dest string /usr/local/oracle10g/admin/oracle10/cdump
user_dump_dest string /usr/local/oracle10g/admin/oracle10/udump
따라서 user_dump_dest 디렉토리에 생긴 트레이스 파일을 분석해 보면 전체 병렬 쿼리ㅣ 수행 과정 중 QC가 처리한 오퍼레이션이 어떤 것인지 식별해 낼 수 있다.
먼저 아래 병렬 쿼리에서 스칼라 서브쿼리를 QC가 수행하는지, 아니면 병렬 서버가 수행하는지 확인해 보자.
select /*+ full(e) parallel(e 2) */ empno, ename, job, sal
, (select dname from dept where deptno = e.deptno)
from emp e;
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN DEPT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 52993)
0 PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
6 PX BLOCK ITERATOR PARTITION: 1 4 (cr=3 pr=0 pw=0 time=3754 us)
6 TABLE ACCESS FULL EMP PARTITION: 1 4 (cr=3 pr=0 pw=0 time=2728 us)
Row Source Operation에서 보듯 병렬 서버로부터 받은 결과집합을 QC가 클라이언트에게 전송하면서 스칼라 서브쿼리를 수행하고 있다.
QC의 SQL 트레이스에 스칼라 서브쿼리 수행 통계가 나타난 것을 통해 이런 사실을 알 수 있다.
이번에는 order by절을 추가해 보자.
select /*+ full(e) parallel(e 2) */ empno, ename, job, sal
, (select dname from dept where deptno = e.deptno)
from emp e
order by ename
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.07 0 8 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.08 0 8 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 63 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=0 pw=0 time=1087 us)
3 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=687 us)(object id 52993)
0 PX COORDINATOR (cr=0 pr=0 pw=0 time=0 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)
6 PX BLOCK ITERATOR PARTITION: 1 4 (cr=3 pr=0 pw=0 time=4108 us)
6 TABLE ACCESS FULL EMP PARTITION: 1 4 (cr=3 pr=0 pw=0 time=3740 us)
QC의 SQL 트레이스에 스칼라 서브쿼리 수행 통계가 나타나지 않았으므로 병렬 서버 프로세스들이 스칼라 서브쿼리를 수행했음을 미루어 짐작할 수 있다.
즉, 병렬 서버들이 order by를 위한 정렬 처리를 함과 동시에 스칼라 서브쿼리를 수행하면서 Sort Area(또는 Temp 테이블스레이스)에 중간 결과집합을 담는다.
병렬 퀄리는 대부분 Full Table Scan으로 처리되는데, 도중에 이처럼 인덱스를 경유한 Random 액세스 위주의 스칼라 서브쿼리까지 수행해야 한다면 수행 속도를 크게 저하시킨다.
따라서 병렬 쿼리에서는 스칼라 서브쿼리를 가급적 일반 조인문장으로 변환하고서 Full Scan + Parallel 방식으로 처리되도록 하는 것이 매우 중요한 튜닝 기법 중 하나이다.
만약 병렬 쿼리 결과 집합 전체를 Fetch하지 않고 중간에 멈추는 상황, 즉 부분 범위처리가 가능한 상황이라면 불필요한 스칼라 서브쿼리 수행을 최소화하는 것만으로도 쿼리 응답 속도를 크게 향상시킬 수 있다.
아래와 같이 변환하고서 쿼리를 다시 수행해 보자.
select empno, ename, job, sal
, (select dname from dept where deptno = e.deptno)
from (
select /*+ full(emp) parallel(emp 2) no_merge */ empno, ename, job, sal, deptno
from emp
order by ename
) e
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN DEPT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 52993)
0 PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (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)
6 PX BLOCK ITERATOR PARTITION: 1 4 (cr=3 pr=0 pw=0 time=2356 us)
6 TABLE ACCESS FULL EMP PARTITION: 1 4 (cr=3 pr=0 pw=0 time=2096 us)
스칼라 서브쿼리를 제외한 나머지 문장을 인라인 뷰로 묶고 스칼라 서브쿼리는 맨 위쪽 메인 쿼리 select-list에 기술하였더니 이번에는 스칼라 서브쿼리 QC가 수행하게 되었다.(no_merge 힌트를 빠뜨리면 뷰 Merging이 발생해 스칼라 서브쿼리를 원래대로 병렬 서버가 수행하게 되므로 주의하기 바란다.)
여기서, 정렬된 쿼리 결과를 일부만 Fetch하고 멈춘다면 스칼라 서브쿼리 수행횟수도 최소화할 수 있다.
이처럼 스칼라 서브쿼리를 기술하는 위치에 따라 QC가 수행하기도 하고 병렬 서버가 수행하기도 하며, 이는 병렬 쿼리 수행 속도에 지대한 영향을 미친다.
병렬 처리 효과를 높이려면 부분범위처리, 전체범위처리 여부에 따라 스칼라 서브쿼리 위치를 옮기거나 아예 일반 조인문으로 바꾸는등의 튜닝을 실시함으로써 큰 효과를 얻을 수 있다.
(6) 병렬 쿼리와 사용자 정의 함수
사용자 정의 함수를 생성할 때 아래와 같이 parallel_enable 키워드를 선언(3번라인)해 주어야 병렬 실행이 가능하다고 흔히 생각하는데, 그렇지 않다.
TEST@ora10g>create or replace function getDname(p_deptno number)
2 return varchar2
3 parallel_enable
4 is
5 l_dname dept.dname%type;
6 begin
7 select dname into l_dname from dept where deptno = p_deptno;
8 return l_dname;
9 end;
10 /
Function created.
TEST@ora10g>select /*+ parallel(emp 2) */ empno, ename, job, sal, getDname(deptno)
2 from emp;
세션 변수를 참조하지 않는다면 이 키워드를 지정하든 안 하든 병렬 수행이 하며, 위 getDanme 함수는 세션 변수를 사용하지 않았으므모 parallel_enable 선언은 사실상 불필요하다.
세션 변수를 참조하는 함수일 때는 parallel_enable을 선언 하느냐에 따라 함수의 병렬 수행 여부가 결정된다.
parallel_enable 키워드 역할
parallel_enable 키워드가 어떤 역할을 하는지 살펴보자.
SQL 수행 결과는 병렬로 수행했는지 여부와 상관없이 항상 일관된 상태여야 한다.
그런데 함수가 패키지 변수와 같은 세션 변수를 참조한다면 병렬 수행 여부에 따라 결과가 달라질 수 있다.
원인은, 병렬 쿼리 시 각 병렬 서버가 개별적인 세션 ID를 부여받고 실행된다는 데에 있다.
패키지 변수는 세션 레벨에서만 유효하다는 특징을 갖느다. 즉, 세션이 수립될 때 초기화 되어 같은 세션 내에서는 지속적으로 참조할 수 있지만 다른 세션과는 값을 공유하지 못한다.
따라서 패키지 변수를 가진 함수를 한 세션이 직렬로 호출할 때와 여러 세션이 병렬로 호출할 때의 결과는 다를 수 있다.
이해를 돕기위한 테스트를 수행해 보자.
우선 패키지 변수를 읽고 쓰는 패키지를 아래와 같이 생성한다.
TEST@ora10g>create or replace package call_counter
2 as
3 procedure initCount;
4 procedure increaseCount;
function getCount return integer;
5 6 end call_counter;
7 /
Package created.
TEST@ora10g>
TEST@ora10g>
TEST@ora10g>create or replace package body call_counter
2 as
3 call_count number;
4 procedure initCount is
5 begin
6 call_count := 0;
7 end initCount;
8
9 procedure increaseCount is
10 begin
11 call_count := call_count + 1;
12 end increaseCount;
13
14 function getCount return integer is
begin
15 16 return call_count;
17 end getCount;
18
19 begin
20 call_count := 0;
21 end call_counter;
22 /
Package body created.
increaseCount 프로시저는 호출할 때마다 패키지 변수 call_count를 1씩 증가시키고, getCount 함수는 현재의 call_count 값을 반환한다.
세션 변수를 참조하는 함수에 parallel_enable을 지정하지 않은 경우부터 살펴보자.
TEST@ora10g>create or replace function FuncCallCount return integer
2 as
3 l_count integer;
4 begin
5 call_counter.increaseCount;
6 return call_counter.getCount;
7 end;
8 /
Function created.
TEST@ora10g>explain plan for
2 create table t
3 parallel 4
4 as
5 select /*+ parallel(t 4) */ t.*, FuncCallCount callcount
6 from big_table t ;
Explained.
TEST@ora10g>select * from table(dbms_xplan.display(null, null,'basic parallel'));
-------------------------------------------------------------------------------
| 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 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | BIG_TABLE | Q1,00 | PCWP | |
-------------------------------------------------------------------------------
ID 1번 단계에 'FORCED SERIAL'이라고 표시된 부분을 확인하기 바란다.
이것은 FuncCallCount 함수가 패키지 변수를 참조하기 때문이다. 이 함수를 병렬로 실행하면 원치 않는 결과가 나타날 수 있기 때문에 함수 호출 부분만큼은 QC에 의해 직렬(serial)로 처리됨을 뜻한다.
call_count를 초기화 하고 실제로 CTAS 문장을 수행해 보자.
TEST@ora10g>exec call_counter.initCount;
PL/SQL procedure successfully completed.
TEST@ora10g>create table t
2 parallel 4
3 as
4 select /*+ parallel(t 4) */ t.*, FuncCallCount callcount
5 from big_table t ;
Table created.
TEST@ora10g>select FuncCallCount from dual;
FUNCCALLCOUNT
-------------
1000001
CTAS 후에 QC 세션에서 FuncCallCount 함수를 한 번 더 호출한 결과가 1,000,001 이므로 CTAS 문장 수행중에는 QC에 의해 1,000,000 번 함수 호출이 일어났음을 알 수 있다.
패키지 변수 call_count를 1씩 증가시키면서 T 테이블에 입력한 callcount 컬럼 값에 중복이 있는지 확인해 보자.
TEST@ora10g>select count(*) from (
2 select callcount, count(*) from t
3 group by callcount
4 having count(*) > 1
5 ) ;
COUNT(*)
----------
0
중복이 전혀 없다. QC가 함수 호출을 도맡아 순차적으로 call_count 값을 읽고 1씩 증가시켰기 때문이다.
이번에는 세션변수를 참조하는 FuncCallCount 함수에 parallel_enable을 선언하는 경우를 살펴보자.
TEST@ora10g>create or replace function FuncCallCount return integer
parallel_enable
as
2 3 4 l_count integer;
5 begin
6 call_counter.increaseCount;
7 return call_counter.getCount;
8 end;
9 /
Function created.
TEST@ora10g>explain plan for
2 create table t
3 parallel 4
4 as
5 select /*+ parallel(t 4) */ t.*, FuncCallCount callcount
6 from big_table t ;
Explained.
TEST@ora10g>select * from table(dbms_xplan.display(null, null,'basic parallel'));
-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | BIG_TABLE | Q1,00 | PCWP | |
-------------------------------------------------------------------------
ID 1번 단계에서 'FORCED SERIAL'표시가 사라졌다.
함수를 병렬 서버가 동시에 실행할 수 있게 된 것을 의미한다.
실제 수행해 보자.
TEST@ora10g>drop table t purge;
TEST@ora10g>exec call_counter.initCount;
TEST@ora10g>create table t
2 parallel 4
3 as
4 select /*+ parallel(t 4) */ t.*, FuncCallCount callcount
5 from big_table t ;
Table created.
TEST@ora10g>select FuncCallCount from dual;
FUNCCALLCOUNT
-------------
1
CTAS 후에 QC 세션에서 FuncCallCount 함수를 호출한 결과가 1이므로 CTAS 문장 수행 중에는 QC가 함수를 호출한 적이 전혀 없음을 알 수 있다.
T 테이블에 입력한 callcount 컬럼 값에 중복이 있는지 확인해 보자.
TEST@ora10g>select count(*) from (
2 select callcount, count(*) from t
3 group by callcount
4 having count(*) > 1
5 );
COUNT(*)
----------
250181
250,181건의 중복이 발생하였다.
각 병렬 서버가 각가 세션 레벨 변수를 읽고 썼기 때문에 T 테이블에 입력된 callcount 값에 중복이 발생한 것이다.
(7) 병렬 쿼리와 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
(8) 병렬 처리 시 주의사항
Parallelism has potential to paralyze your system.
"병렬 처리는 잠재적으로 당신의 시스템을 마비 시킬수 있다."
병렬처리가 바람직한 경우
동시 사용자 수가 적은 애플리케이션 환경에서 직렬로 처리할 때 보다 성능 개선효과가 확실할 때.
OLTP성 시스템 환경이더라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스 사용률을 감소시킬 수 있을때.
병렬쿼리와 관계된 주의사항
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로 새로운 쿼리를 수행하여 이전 커서를 닫아 주는 방식을 취할 수 있다. )
문서에 대하여
- 최초작성자 : ~sunshiny
- 최초작성일 : 2010년 12월 05일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*