오라클 성능 고도화 원리와 해법 II (2010년)
1. 병렬 Order By
- 테스트 스크립트 생성
-- 테이블 생성
CREATE TABLE CUSTOMER_XSOFT AS
SELECT ROWNUM CUSTOMER_ID,
DBMS_RANDOM.STRING('U', 10) CUSTOMER_NAME,
MOD(ROWNUM, 10) + 1 CUSTOMER_GRADE,
TO_CHAR(TO_DATE('20090101', 'YYYYMMDD') + (ROWNUM - 1), 'YYYYMMDD') JOIN_DATE
FROM DUAL
CONNECT BY LEVEL <= 1000
;
-- 통계정보 생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'CUSTOMER_XSOFT',
CASCADE => TRUE);
END;
/
-- 실행계획
EXPLAIN PLAN FOR
SELECT /*+ FULL(CX) PARALLEL(CX 2) */
CX.CUSTOMER_ID,
CX.CUSTOMER_NAME,
CX.JOIN_DATE
FROM CUSTOMER_XSOFT CX
ORDER BY CUSTOMER_NAME
;
@xplan
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 24000 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 1000 | 24000 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 1000 | 24000 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1000 | 24000 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1000 | 24000 | 2 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 1000 | 24000 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| CUSTOMER_XSOFT | 1000 | 24000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------
14 rows selected.
- 여기서 소개할 내용은 병렬 쿼리를 수행 한 후 1/N이 잘 되었는지 확인할 수 있는 V$PQ_TQSTAT 뷰(Parallel Query Table Queue Statistics)의 활용법을 소개한다.
- ORDER BY를 병렬로 수행하려면 테이블 큐를 통한 데이터 재분배가 필요한데, 쿼리 수행이 완료된 직후에 같은 세션에서 V$PQ_TQSTAT를 수행하면 데이터 전송 통계를 볼 수 있다.
SET SERVERTRACE OFF;
SELECT /*+ FULL(CX) PARALLEL(CX 2) */
CX.CUSTOMER_ID,
CX.CUSTOMER_NAME,
CX.JOIN_DATE
FROM CUSTOMER_XSOFT CX
ORDER BY CX.CUSTOMER_NAME
;
SELECT TQ_ID,
SERVER_TYPE,
PROCESS,
NUM_ROWS,
BYTES,
WAITS
FROM V$PQ_TQSTAT
ORDER BY DFO_NUMBER,
TQ_ID,
DECODE(SUBSTR(SERVER_TYPE, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3),
PROCESS
;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
----- ----------- ------- -------- ----- -----
0 Ranger QC 182 9808 1
0 Producer P002 548 14968 5
0 Producer P003 452 12427 3
0 Consumer P000 491 13421 88
0 Consumer P001 509 13908 89
1 Producer P000 491 13394 0
1 Producer P001 509 13881 0
1 Consumer QC 1000 27275 1
-- 그림 7_4 참조
- 병렬 쿼리 수행 속도가 예상만큼 빠르지 않다면 테이블 큐를 통한 데이터 전송량에 편차가 크지 않은지 확인해 볼 필요가 있는데, 이 때 이 뷰를 사용함.
2. 병렬 Group By
2_1. 병렬 Group By
EXPLAIN PLAN FOR
SELECT /*+ FULL(CX) PARALLEL(CX 2) */
CX.CUSTOMER_NAME,
COUNT(*)
FROM CUSTOMER_XSOFT CX
GROUP BY CUSTOMER_NAME
;
@xplan
-----------------------------------------------------------------------------------------------------------------------
| 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 (RAND) |
| 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| CUSTOMER_XSOFT | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------
14 rows selected.
EXPLAIN PLAN FOR
SELECT /*+ FULL(CX) PARALLEL(CX 2) */
CX.CUSTOMER_NAME,
COUNT(*)
FROM CUSTOMER_XSOFT CX
GROUP BY CX.CUSTOMER_NAME
ORDER BY CX.CUSTOMER_NAME
;
@xplan
-----------------------------------------------------------------------------------------------------------------------
| 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 (ORDER) |
| 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| CUSTOMER_XSOFT | 1000 | 11000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------
14 rows selected.
- 병렬 ORDER BY와 병렬 GROUP BY의 내부 실행원리는 같지만, 차이점은 데이터 분배 방식에 있다.
즉, GROUP BY 키 정렬 순서에 따라 분배하느냐 해시 함수 결과 값에 따라 분배하느냐의 차이다. - SORT GROUP BY는 GROUP BY 결과를 QC에게 전송할 때 값 순서대로(QC ORDER) 진행하지만, HASH GROUP BY는 먼저 처리가 끝난 순서대로(QC RANDOM) 진행함.
SET AUTOTRACE OFF;
SELECT /*+ FULL(CX) PARALLEL(CX 2) */
CX.CUSTOMER_NAME,
COUNT(*)
FROM CUSTOMER_XSOFT CX
GROUP BY CX.CUSTOMER_NAME
ORDER BY CX.CUSTOMER_NAME
;
SELECT TQ_ID,
SERVER_TYPE,
PROCESS,
NUM_ROWS,
BYTES,
WAITS
FROM V$PQ_TQSTAT
ORDER BY DFO_NUMBER,
TQ_ID,
DECODE(SUBSTR(SERVER_TYPE, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3),
PROCESS
;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
----- ----------- ------- ---------- ---------- ----------
0 Ranger QC 182 4494 1
0 Producer P002 455 5616 4
0 Producer P003 545 6696 5
0 Consumer P000 491 6015 5760
0 Consumer P001 509 6231 5761
1 Producer P000 491 7976 0
1 Producer P001 509 8264 0
1 Consumer QC 1000 16240 3
- P002와 P003은 각각 455와 545개 로우를 읽어 해시 함수를 적용하고, 거기서 반환된 값에 따라 두 번째 서버 집합으로 데이터를 분배함.
그 과정에서 P000은 491개 로우를 받아 GROUP BY한 결과 491개 로우를 QC에 전송하고, P001으로 509개 로우를 받아 GROUP BY한 결과 509개 로우를 QC에게 전송
- HASH 값에 따라 데이터를 분배하였으므로 P000과 P001은 서로 배타적인 집합을 가지고 GROUP BY를 수행함.
2_2. Group By가 두 번 나타날 때의 처리 과정
EXPLAIN PLAN FOR
SELECT /*+ FULL(CX) PARALLEL(CX 2) */
CX.CUSTOMER_GRADE,
COUNT(*)
FROM CUSTOMER_XSOFT CX
GROUP BY CX.CUSTOMER_GRADE
;
@xplan
------------------------------------------------------------------------------------------------------------------------
| 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 (RAND) |
| 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| CUSTOMER_XSOFT | 1000 | 3000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
- 이렇게 'HASH GROUP BY'가 2번 나타난 이유는 각 컬럼의 선택도 값의 차이에 있다.
SELECT COLUMN_NAME,
NUM_DISTINCT,
NUM_NULLS,
1/NUM_DISTINCT SELECTIVITY,
ROUND(1/NUM_DISTINCT * T.NUM_ROWS, 2) CARDINALITY
FROM USER_TABLES T,
USER_TAB_COLUMNS C
WHERE T.TABLE_NAME = 'CUSTOMER_XSOFT'
AND C.TABLE_NAME = T.TABLE_NAME
ORDER BY COLUMN_ID
;
COLUMN_NAME NUM_DISTINCT NUM_NULLS SELECTIVITY CARDINALITY
-------------- ------------ ---------- ----------- -----------
CUSTOMER_ID 1000 0 .001 1
CUSTOMER_NAME 1000 0 .001 1
CUSTOMER_GRADE 10 0 .1 100
JOIN_DATE 1000 0 .001 1
- CUSTOMER_NAME 컬럼의 선택도는 0.001로 매우 낮고, CUSTOMER_GRADE는 0.1로 비교적 높다.
즉, CUSTOMER_GRADE를 GROUP BY하면 전체 데이터의 1/10으로 줄어들기 때문에(10 로우) 먼저 GROUP BY를 하고 두 번째 서버 집합에 전송하여 프로세스 간 통신량을 줄이기 위함이다.
하지만 CUSTOMER_NAME을 GROUP BY 해도 1000로우 그대로 유지하므로 GROUP BY 의미가 없으므로 한번밖에 표시가 안된다.
- 아래는 실제 SQL을 수행 후 V$PQ_TQSTAT로 확인 한 결과이다.
SET AUTOTRACE OFF;
SELECT /*+ FULL(CX) PARALLEL(CX 2) */
CX.CUSTOMER_GRADE,
COUNT(*)
FROM CUSTOMER_XSOFT CX
GROUP BY CX.CUSTOMER_GRADE
;
SELECT TQ_ID,
SERVER_TYPE,
PROCESS,
NUM_ROWS,
BYTES,
WAITS
FROM V$PQ_TQSTAT
ORDER BY DFO_NUMBER,
TQ_ID,
DECODE(SUBSTR(SERVER_TYPE, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3),
PROCESS
;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
----- ----------- ------- ---------- ---------- ----------
0 Producer P002 10 208 0
0 Producer P003 10 208 0
0 Consumer P000 10 208 10
0 Consumer P001 10 208 10
1 Producer P000 5 64 1
1 Producer P001 5 64 2
1 Consumer QC 10 128 1
- 앞에서 GROUP BY가 한번 나타날때와 비교했을 때 프로세스 간에 주고받은 데이터 건수가 현저히 준 것을 확인할 수 있다.
- 참고로 선택도가 낮은 컬럼으로 GROUP BY할 때도 강제로 이 방식을 사용하도록 하려면 '_groupby_nopushdown_cut_ratio' 값을 0으로 세팅(기본은 3)하면 된다.
11g에서는 'gby_pushdown, no_gby_pushdown' 힌트가 추가되 파라미터 변경 없이도 사용자가 GROUP BY 방식을 조정할 수 있다.
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2010년 06월 19일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3251
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.