2. 병렬 Order By와 Group By
2011.06.30 00:52
02 병령 Order By 와 Group By
P->P 데이터 재분배는 주로 병렬 order by, 병렬 group by, 병렬 조인을 포함한 SQL에서 나타난다.
거의 모든 병렬 SQL에서 Inter-Operation Parallelism 이 일어난다고 볼 수 있다.
(1) 병렬 Order By
예제)
l 테스트 데이터 생성
create table 고객
as
select
rownum 고객ID
, dbms_random.string('U', 10) 고객명
, mod(rownum, 10) + 1 고객등급
, to_char(to_date('20090101','yyyymmdd') + (rownum-1), 'yyyymmdd') 가입일
from dual
connect by level <= 1000;
exec dbms_stats.gather_table_stats(user, '고객');
l 고객테이블을 병렬로 읽어 고객명 순으로 정렬하는 쿼리와 실행계획
set autotrace traceonly exp;
select /*+ full(고객) parallel(고객 2) */
고객ID, 고객명, 고객등급
from 고객
order by 고객명 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 908971466
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 18000 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 1000 | 18000 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (O
| 3 | SORT ORDER BY | | 1000 | 18000 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1000 | 18000 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1000 | 18000 | 2 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 1000 | 18000 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| 고객 | 1000 | 18000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
l v$pq_tqstat 의 활용법 소개
order by를 병렬로 수행하려면 테이블 큐를 통한 데이터 재분배가 필요한데,
쿼리 수행이 완료된 직후에 같은 세션에서 v$pq_tqstat를 쿼리해 보면 테이블 큐를 통한
데이터 전송통계를 확인해 볼 수 있다.
from v$pq_tqstat
order by
dfo_number
, tq_id
, decode(substr(server_type, 1, 4),'Rang', 1, 'Prod', 2, 'Cons', 3)
, process;
---------- -------------------- -------------------- ---------- ---------- ----------
0 Ranger QC 182 7584 0
Producer P002 455 9543 4
P003 545 11532 5
Consumer P000 548 11513 4
P001 452 9504 4
1 Producer P000 548 11470 0
P001 452 9461 0
Consumer QC 1000 20931 2
ð 병렬 쿼리 수행 속도가 예상만큼 빠리지 않다면 테이블 큐를 통한 데이터 전송량에 편차가
크지 않은지 확인해 볼 필요가 있는데, 그럴 때 v$pq_tqstat 뷰가 유용하게 쓰인다.
(2) 병렬 Group By
예제)
set autotrace traceonly exp;
select /*+ full(고객) parallel(고객 2) */
고객명, count(*)
from 고객
group by 고객명 ;
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 11000 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1000 | 11000 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (
| 3 | HASH GROUP BY | | 1000 | 11000 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| 고객 | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
l Order by 고객명 추가
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 11000 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 1000 | 11000 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (O
| 3 | SORT GROUP BY | | 1000 | 11000 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| 고객 | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
병렬 order by 실행계획과 비교해보면 ‘sort order by’가 ‘sort group by’로 바뀌는 것 말고는 모두 같다.
즉, order by와 group by를 병렬로 처리하는 내부 수행원리는 기본적으로 같다는 뜻이다.
병렬 sort group by 와 병렬 hash group by 의 차이점은 데이터 분배 방식에 있다.
즉, group by 키 정렬 순서에 따라 분배하느냐 해시 함수 결과 값에 따라 분배하느냐의 차이이다.
Group by 결과를 QC에게 전송할 때도, sort group by는 값 순서대로 진행하지만 hash group by는 먼저 처리가 끝난 순서대로 진행한다.
l 명함을 그룹핑해서 고객사별로 연락 가능한 담당자가 몇 명인지를 집계
(1) 8명의 영업 사원이 각자 관리하던 명함을 집계하고 나면 그 결과를 영업부장이 최종적으로 집계하는 경우
(2) 두 집합으로 나워 한 쪽은 명함을 읽어서 분배하고 다른 한쪽은 그것을 받아 집계하는 경우
Group By가 두 번 나타날 때의 처리 과정
병렬 group by 실행계획에 group by가 두번 나타나는 경우.
select /*+ full(고객) parallel(고객 2) */
고객등급, count(*)
from 고객
group by 고객등급 ;
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10 | 30 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (RA
| 3 | HASH GROUP BY | | 10 | 30 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 10 | 30 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 10 | 30 | 3 (34)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 10 | 30 | 3 (34)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1000 | 3000 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| 고객 | 1000 | 3000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
Group by를 두 번 수행하는 것일까?
비밀은 group 기준 컬럼의 선택도에 있다.
고객명의 선택도는 0.001 (=0.1%)로서 매우 낮고, 고객등급의 선택도는 0.1(=10%)로서 비교적 높다.
즉, 고객등급으로 group by 한 결과 집합은 원래의 데이터 집합과 비교하면 1/10 크기이다.
따라서 첫 번째 서버 집합이 읽은 데이터를 먼저 group by 하고 나서 두 번째 서버 집합에 전송한다면 프로세스간 통신량이 1/10로 줄어 그만큼 병렬 처리 과정에서 생기는 병목을 줄일 수 있다.
select /*+ full(고객) parallel(고객 2) */
고객등급, count(*)
from 고객
group by 고객등급 ;
SQL> select tq_id, server_type, process, num_rows, bytes, waits
2 from v$pq_tqstat
3 order by
4 dfo_number
5 , tq_id
6 , decode(substr(server_type, 1, 4),'Rang', 1, 'Prod', 2, 'Cons', 3)
7 , process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- -------------------- -------------------- ---------- ---------- ----------
0 Producer P002 10 200 0
0 Producer P003 10 200 0
0 Consumer P000 10 200 3
0 Consumer P001 10 200 3
1 Producer P000 5 60 3
1 Producer P001 5 60 3
1 Consumer QC 10 120 2
7 개의 행이 선택되었습니다.
앞에서 group by가 한 번 나타날 때와 비교했을 때 프로세스 간에 주고받은 데이터 건수가 현격히 준 것을 확인할 수 있다.
l 선택도가 낮은 컬럼으로 group by 할 때도 강제로 이 방식을 사용하도록 하려면
_groupby_nopushdown_cut_ratio 파라미터를 0으로 세팅하면 된다.
이 파라미터의 기본 값인 3은 group by 기준 컬럼의 선택도에 따라 옵티마이저가 방식을 결정하도록 하는 것이다.
l 11g에서는 gby_pushdown, no_gby_pushdown 힌트가 추가돼 파라미터 변경 없이도 group by 방식을 조정 할 수 있다.
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 | 4장._쿼리_변환 | 휘휘 | 2011.09.07 | 2394 |
54 | 진행기록 | 운영자 | 2011.08.23 | 2383 |
» | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10117 |
52 | 3. 병렬 조인 | 오예스 | 2011.06.29 | 4969 |
51 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15609 |
50 |
5. 병렬 처리에 관한 기타 상식
[1] ![]() | balto | 2011.06.26 | 28162 |
49 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28273 |
48 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11857 |
47 | 7. Sort Area 크기 조정 | 오예스 | 2011.06.16 | 5984 |
46 | 5. 인덱스를 이용한 소트 연산 대체 | AskZZang | 2011.06.16 | 4955 |
45 | 4. 소트가 발생하지 않도록 SQL 작성 | AskZZang | 2011.06.16 | 3109 |
44 | 3. 데이터 모델 측면에서의 검토 | AskZZang | 2011.06.16 | 2724 |
43 | 5장. 소트 튜닝 | balto | 2011.06.16 | 4283 |
42 | 6. Sort Area를 적게 사용하도록 SQL 작성 | 오예스 | 2011.06.14 | 4207 |
41 |
1. 소트 수행 원리
![]() | balto | 2011.06.12 | 8022 |
40 |
2. 소트를 발생시키는 오퍼레이션
![]() | balto | 2011.06.12 | 4847 |
39 | 11. 집합 연산을 조인으로 변환 | 오라클잭 | 2011.06.08 | 4956 |
38 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9953 |
37 | 12. 기타 쿼리 변환 | 휘휘 | 2011.06.06 | 3122 |
36 | 7. OR-Expansion | AskZZang | 2011.06.01 | 8333 |