데이터이야기
DB 노하우, 데이터직무, 다양한 인터뷰를 만나보세요.
병렬로 처리하여 시간 단축하기 - 병렬쿼리
병렬 처리는 대량의 데이터 처리를 신속하게 처리할 수 있도록
다음과 같은 오퍼레이션에 한해 병렬 처리가 가능하다.
1. 전체 테이블 스캔(INDEX FAST FULL SCAN도 2. Hash 조인(다만 Driving하는 쪽이
Full Scan일 경우 Nested loop 조인도 병렬 처리 가능함) 3. 병렬 DML(PDML): Insert, Delete, Update, 4. 병렬 DDL: 인덱스 리빌드, 인덱스 생성, CTAS(Create Table As Select) 5. 병렬 복구: recovery_parallelism 파라미터
위의 2번 Nested loop 조인에 대해서도 특정 조건만 준수한다면 병렬 처리가 가능하다는 사실을 모르는 경우가 의외로
SCOTT@ORCL > set SCOTT@ORCL >
------------------------------------------------------------------------------------------------------------------------ | Id | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT | 1 | PX COORDINATOR | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | | | 3 | NESTED LOOPS | | | | 4 | NESTED LOOPS | | | 5 | PX BLOCK ITERATOR | | | | 6 | TABLE ACCESS |* 7 | INDEX UNIQUE SCAN | DEPT_PK | 1 | 8 | TABLE ACCESS BY INDEX ROWID| DEPT | ------------------------------------------------------------------------------------------------------------------------
Index에 대한 멀티블록 I/O 즉 INDEX_FFS에 대해서도 다음과 같이 병렬 처리가 가능하다.
SCOTT@ORCL >
----------------------------------------------------------------------------------------------------------- | Id | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT | 1 | SORT AGGREGATE | | | 2 | PX COORDINATOR | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | 4 | SORT AGGREGATE | | 1 | | | | 5 | PX BLOCK ITERATOR | | 6 | INDEX FAST FULL SCAN| PK_EMP | ----------------------------------------------------------------------------------------------------------- 실행계획은 ID 6부터
병렬처리가 작동하기 위해 필요한 조건들
1.
오라클 엔터프라이즈 에디션 2.
사용하는 병렬도 정도에 따라 슬레이브 프로세스를 고려하여 3.
CPU와 I/O에 충분한 여유가 있어야 함. 특히 최근 CPU 기술의 눈부신 발전으로 인해 CPU 보다는 I/O가 병목점이 되는 경우가 훨씬 많다.
특히 3번의 경우 CPU 여유가 충분함에도 불구하고 병렬도(DOP)를 증가시켜도 더 이상 처리 시간이 단축되지 않는 경우가 많은데, 대부분 I/O가 포화되어 그렇다. 따라서 운영체제 상에서 top, topas, glance 등의 모니터링 도구를 사용하여 H/W 리소스
여기서 한가지
병렬도 다운그레이드 parallel_max_servers=32에서 다른 병렬 쿼리가 24개의 병렬 프로세스를 사용 중일 특히 최근의 현재 병렬 병렬도 다운그레이드
SYS@ORCL > select * from v$sysstat where name like
STATISTIC# NAME ---------- 563 564 565 566 567 568
6 rows selected.
V$SYSSTAT 뷰의 과거의 이력을 조회하려면 AWR의 dba_hist_sysstat를 살펴보면 된다.
병렬 처리의 통계값 살펴보기
병렬 처리에
SYS@ORCL > select * from v$pq_sysstat;
STATISTIC VALUE ------------------------------------------------------------ Servers Busy Servers Idle Servers Highwater Server Sessions Servers Started Servers Shutdown 8 Servers Cleaned Up Queries Queued Queries Initiated Queries Initiated (IPQ) DML Initiated DML Initiated (IPQ) DDL Initiated 0 DDL Initiated (IPQ) DFO Trees Sessions Active Local Msgs Sent Distr Msgs Sent Local Msgs Recv'd Distr Msgs Recv'd 0
20 rows selected.
위의 STATISTIC 항목 중 일부는 특정 시점의 스냅샷 값이고, 나머지는 “Server Sessions”는 인스턴스 시작 후 병렬 실행 오퍼레이션의 누적 개수이다. “Queries 안타깝게도병렬로 처리하여 시간 단축하기 - 병렬쿼리
오라클 7.1.6 버전에서 처음 도입된 오라클 엔터프라이즈 에디션의 기능이다(스탠다드 에디션은 이를 지원하지 않음). 병렬 처리는 대량의 데이터
처리를 신속하게 처리하기 위해 큰 작업을 여러 개의 작은 작업으로 쪼개서 동시에 처리하여 총 수행 시간을 단축시킬 수 있다. 우리의 일상 생활에서 업무량이 많을 경우에는 한 사람이 단독으로 처리하는 것보다 팀 단위로 작업을 쪼개서 협업으로
처리하는 것이 보다 효율적인 것과 마찬가지다. 그대신 병렬 처리는 하드웨어 리소스를 더 많이 사용하게
되므로 간단한 OLTP성 작업에 대해서 사용할 경우 오히려 처리가 지연된다. 이는 팀 단위 작업에서는 커뮤니케이션 오버헤드가 발생하기 때문에 그러하다. 따라서
배치나 대용량 집계 작업 등에 한해서 이 기능을 사용하는 것이 바람직하다.
가능함).
Merge 문에 대해서 적용 가능하다.
설정 값에 따라 인스턴스 복구에 걸리는 시간을 단축할 수 있음.
많은데 정확히 짚고 넘어가자. 다음과 같이 Driving Table이 Full Scan으로 처리될 경우 Nested loop 조인에 대해서도
병렬 처리가 가능하다.
autotrace on exp
select /*+ full(e) ordered use_nl(e d)
parallel(e 4) */ * from emp e, dept d where e.deptno = d.deptno;
Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
STATEMENT | |
12 | 1404 | 5
(0)| 00:00:01 | |
| |
| | | |
| |
| | |
Q1,00 | P->S | QC (RAND) |
| | |
Q1,00 | PCWP | |
12 | 1404 | 5
(0)| 00:00:01 | Q1,00 | PCWP
| |
| | |
Q1,00 | PCWC | |
FULL | EMP |
12 | 1044 | 2
(0)| 00:00:01 | Q1,00 | PCWP
| |
| | 0
(0)| 00:00:01 | Q1,00 | PCWP
| |
1 | 30 | 1
(0)| 00:00:01 | Q1,00 | PCWP
| |
select /*+ index_ffs(e) parallel_index(e
4) */ count(*) from emp e;
Operation | Name | Rows
| Cost (%CPU)| Time | TQ
|IN-OUT| PQ Distrib |
STATEMENT | |
1 | 2 (0)| 00:00:01 | |
| |
1 | | | |
| |
| |
Q1,00 | P->S | QC (RAND) |
Q1,00 | PCWP | |
| 12 | 2
(0)| 00:00:01 | Q1,00 | PCWC
| |
12 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
아래에서 위로 올라가면서 읽으면 된다. ID 5(PX BLOCK ITERATOR)를 살펴보면 여러 개의
병렬 슬레이브로 INDEX_FFS 작업을 분할하여 처리한 것을 알 수 있다. ID 4의 SORT AGGREGATE는 각 병렬 슬레이브에서 COUNT하였다. ID 3의 PX
SEND QC는 쿼리 코디네이터(팀장에 해당함)로
각 병렬 슬레이브의 카운트 결과를 전송한 후, ID 1에서 코디네이터가 카운트 결과를 최종 취합한다.
parallel_max_servers를 충분한 크기로 설정
사용률을 모니터링하는 것이 좋다.
병렬도(DOP, Degree of parallelism) 을 얼마로 설정해야 할 것인지에 대해 개발자들이
문의하는 경우가 많다(개발자들은 획일적인 8, 16 등의
수치를 지정해주길 원하는 경향이 크다). 결론부터 말하자면 가용한 시스템 리소스와 시스템 부하 정도에
따라 다르다. 병렬도는 작업의 중요도에 따라 더 높은 병렬도를 부여할 수 있다. 예를 들어, 아주 중요한 배치 업무이며, 시스템 리소스가 충분하며, 다른 병렬 배치 프로그램과 동시에 수행되지
않아서 자원을 두고 경합할 필요가 없다면 높은 병렬도를 부여하는 편이 좋다. 물론 CPU가 4개인 시스템에서 병렬도를
128을 부여한다거나 하여 너무 과도한 부하를 줄 경우 오히려 느려지는 등 역효과가 날 수 있다. 따라서
하드웨N-US"> DBA가 개괄적인 테스트를 거쳐서 적정 병렬도 범위를 가이드해준
상태에서, 업무 중요도에 따라 개발자가 늘이거나 줄이는 것이 좋다.
때, 병렬도 16으로 요청이 들어오면 어떻게 될까 이렇게 가용한 병렬 프로세스가 모자랄 경우 병렬도 다운그레이드(16？8)가 일어난다. 가용한 병렬 프로세스가 “0”인 최악의
경우에는 병렬로 요청한 쿼리일지라도 순차적(serial)으로 수행될 수도 있다. 이렇게 병렬도 다운그레이드가 발생하면 의도치 않게 병렬 쿼리 수행에 평소보다 더 오랜 시간이 소요될 수 있다.
오라클은 parallel_adaptive_multi_user라는 파라미터의 기본값이 TRUE로 설정된 관계로 시스템 부하 정도에 따라 오라클이 자동으로 요청된 병렬도를 다운그레이드하는 경우가 종종
벌어진다. 이 파라미터의 정확한 병렬도 다운그레이드 알고리즘은 알려져 있지 않다. 따라서 너무 자주 병렬도 다운그레이드가 일어난다면 이 파라미터를 FALSE로
설정하는 것을 고려해볼 수 있다.
쿼리의 병렬도 설정값은 V$PX_SESSION 뷰의
REQ_DEGREE와 DEGREE 칼럼을 참고 바란다.
REQ_DEGREE는 요청한 병렬도이고, DEGREE는 실제 사용된 병렬도 값이다.
발생 여부는 다음과 같이 V$SYSSTAT 뷰를 통해서 확인할 수 있다.
'Parallel operations%';
CLASS VALUE
STAT_ID
---------------------------------------------------------------- ----------
---------- ----------
Parallel operations not downgraded 32 178053 2410168744
Parallel operations downgraded to serial 32 12 4286011915
Parallel operations downgraded 75 to 99 pct 32 2
322858058
Parallel operations downgraded 50 to 75 pct
32 0 3281923117
Parallel operations downgraded 25 to 50 pct 32 0 3330456527
Parallel operations downgraded 1 to 25 pct 32 0 3721245209
대한 통계 값은 다음과 같이 v$pq_sysstat 뷰를 살펴보면 된다. 이 뷰를 통해서 병렬 쿼리의 활동성을 파악할 수 있다.
----------
0
0
4
12
8
0
0
3
0
0
0
0
3
0
74
0
74
인스턴스 기동 후의 누적 값이다. “Servers Busy”통계 항목은 스냅샷 값으로 현재 데이터베이스에서
실행 중인 병렬 프로세스의 개수이다. “Servers Highwater”는 인스턴스 기동 후 병렬 실행
프로세스가 가장 많이 사용되었을 때의 개수이다. 즉, 최대
사용량 개수를 의미한다. 이 값이
parallel_max_servers에 근접한다면 parallel_max_servers를
늘리는 것을 검토해볼 수 있다.
Initiated”과 “DML Initiated”그리고 “DDL
Initiated”은 각각 병렬쿼리 기동 횟수, PDML 기동 횟수, PDDL 기동 횟수를 의미한다.
v$pq_sysstat 뷰에 대한 AWR 이력 테이블은 존재하지
않는다.