꿈꾸는 개발자, DBA 커뮤니티 구루비

Introduction
1. 병렬 쿼리
2. 병렬처리 유형
병렬 신화
1. 병렬 처리 오버헤드
병렬 관리
1. 병렬 작업을 할 수 있는데 간과되고 있는 것들..
병렬 쿼리
1. 병렬 쿼리 설정
2. 언제 병렬 쿼리가 유용한가?
병렬 DML

Introduction

병렬 쿼리

단일 SQL 문을 서로 다른 프로세스/쓰레드에 의해 동시에 수행될 수 있는 일련의 작업으로 분할하여 이들을 동시에 실행시킬 수 있는 능력
아래 그림은 테이블(Table)emp의 스캔을 실행하는 패러렐 실행 서버를 설명하고 있다.

Parallel Coordinator가 SQL문내의 각 연산자 분해
그것들을 지정한 순서에 따라 실행
연산자를 실행하는 Parallel Excution Server에 의해 부분적으로 실행결과를 통합

병렬처리 유형

병렬 DML(수정) : 병렬 쿼리와 유사하지만 INSERT, UPDATE, 그리고 DELETE 문에 적용됨.
병렬 DDL : 15개의 CREATE INDEX 문을 동시에 실행(오라클에서는 이렇게도 할 수 있다) 시키는 대신 N개의 프로세스를 사용하여 병렬로 단일 인덱스를 생성하는 CREATE INDEX와 같은 다양한 문을 병렬로 실행시킬 수 있는 관리자용 능력.
병렬 DIY(Do It Youself) : 실제로 전통적인 오라클 병렬 처리 기법은 아니다. 오라클이 작업을 분할하지 않고 사용자가 분할한다.

병렬 신화

병렬 처리는 큰 문제를 여러개로 나누어 작은 문제로 나누면(분할정복 접근법) 처리시간을 획기적으로 줄일 수 있다.
그러나 병렬 처리는 독이 될 수도 있다. 10초 이내에 실행되는 쿼리는 병렬 처리를 하게 되면 오버헤드가 매우 두드러져 보인다. 왜냐하면 병렬 쿼리의 시작비용은 매우 높기 때문에 대상 쿼리가 실제로는 느리게 시작된다.(6~7초 정도 느리다는 이야기같다.. P277)

병렬 처리 오버헤드

병렬 처리는 엄청난 양의 어렵고 장기간을 요하는 작업을 빠르게 수행할 수 있도록 해준다. 그러나 짧은 문제에 대해 병렬 쿼리 작업을 적용하면 오히려 훨씬 느려진다.
그러나 갖은 노력에도 불구하고 튜닝에 성공하지 못한(분, 시간, 일 단위의) 긴 문제에는 병렬 쿼리가 효과적일 수 있다.

병렬 관리

요점이 무엇인가... 병렬관리를 할 수 있는데... 사용자가 안한다는 이야기 같다.

병렬 작업을 할 수 있는데 간과되고 있는 것들..

ALTER INDEX REBUILD, ALTER TABLE MOVE, CREATE INDEX와 같은 문의 속도를 높이기 위해서 병렬 작업을 사용할 수 있다.
단순한 ANALYZE는 병렬로 처리될 수 없다. 그러나 통계를 수집하는 데 강력한 기능을 가진 DBMS_STATS는 병렬로 처리될 수 있다.
DBMS_STATS 관련자료

대량파일을 데이터베이스에 적재하는 방법으로 사용한 SQLLDR을 EXTERNAL_TABLE을 사용하라
- CREATE EXTERNAL_TABLE(단층 파일을 대상으로 SELECT를 사용할 수 있는 능력, 즉 파일을 마치 데이터베이스 테이블인 것처럼 사용할 수 있는 능력) (P.547~ 참조)
- CREATE TABLE T NOLOGGING PARALLEL AS SELECT * FROM EXTERNAL_TABLE 위와같이 작성된 문을 CATS(Create Table As Select)라 한다.
THOMAS KYTE는 위와같이 수행한 것이 지금까지 수행했던 적재중에 가장 쉽고 빨랐다고 한다. 그런데... 난 계속 EXTERNAL TABLE을 실패하고 있다 ㅜㅜ 아뛰....

병렬 쿼리

병렬 쿼리는 특정 유형의 큰 문제, 성능문제를 해결하면서 시도하는 마지막 수단이지 결코 첫번째 경로가 아니다.

병렬 쿼리 설정

필자가 즐겨 사용하는 방법은 Oracl8i 릴리스 2에 처음 도입된 병렬 자동 튜닝 옵션인 PARALLEL_AUTOMATIC_TUNING-TRUE;이다.
이 옵션이 설정되면 다른 병렬 설정은 자동으로 설정된다. 병렬 처리 정도(특히 문제에 할당될 프로세스/쓰레드의 수)는 시스템의 부하에 따라 자동으로 결정되고 변경된다. 필자의 경험으로 강추한다....
기타 미세 조정을 위해 기타 병렬 설정중 일부를 시도해 볼 수 있는데... P283을 참조하자.

언제 병렬 쿼리가 유용한가?

병렬쿼리를 강력한 툴 정도로 생각해야지 일상적으로 사용할 수 있는것으로 생각해선 안된다.
사용자의 동시 세션의 수가 많은 트랜잭션 시스템은 병렬쿼리가 맞지 않는다.
그러나 데이터 웨어하우스에는 병렬 쿼리가 훌륭한 솔루션이 될 수 있다.
필자가 병렬 쿼리를 사용할 경우의 충고는 .... 병렬처리 정도를 지정하지 말고... 병렬 쿼리를 사용하지 않아도 되는 방도를 찾아라

병렬 DML

여기서 부터의 내용은 블로그 Science of DataBase의 내용을 적습니다. 정말 좋은 내용인거 같습니다. 이거 허락 안받았는뎅 .. 만약 이 글을 보시고 삭제 요청하시면 삭제하겠습니다.

DML 과 PARALLEL의 관계

현재 많은 종류의 Oracle 튜닝 책에 Update, Delete 시의 parallel operation 관련하여 Partition 이 되어 있지 않으면 single mode 로 처리된다고 되어 있다. 하지만 이것이 맞는말인가?

테스트 시나리오

고객테이블(100 만건) 의 고객영문명에 serial update 와 parallel update 를 한번씩 한다.
고객테이블은 파티션이 되지않은 테이블이다.
간단하게 테스트해 보았다


alter session disable parallel dml; -- parallel 을 disable 한다.
update  /*+ parallel(tb_cus 8) */ t_big_table
  set owner = 'hhh';

-- 실행결과 DBMS_XPLAN
SQL_ID  2nqb0hxvwurs1, child number 0
-------------------------------------
update  /*+ parallel(tb_cus 8) */ t_big_table   set owner = 'hhh'

Plan hash value: 2407949350

--------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------
|   1 |  UPDATE               | T_BIG_TABLE |      3 |        |      0 |00:00:02.85 |   74046 |     79 |
|   2 |   PX COORDINATOR      |             |      3 |        |  99035 |00:00:00.46 |     100 |     79 |
|   3 |    PX SEND QC (RANDOM)| :TQ10000    |      0 |  46981 |      0 |00:00:00.01 |       0 |      0 |
|   4 |     PX BLOCK ITERATOR |             |      0 |  46981 |      0 |00:00:00.01 |       0 |      0 |
|*  5 |      TABLE ACCESS FULL| T_BIG_TABLE |      0 |  46981 |      0 |00:00:00.01 |       0 |      0 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

엇... 정말 병렬처리를 하는걸로 실행계획이 풀린다... UPDATE 뿐만 아니라 DELETE문에서도 동일하게 병렬처리가 된다.

결론

파티션 되지않은 테이블을 update, delete 할때 parallel 옵션의 적용은 문제가 전혀 없는것으로 드러남.
V$PX_PROCESS 나 GV$PX_SESSION 등의 뷰에서도 정상적으로 Parallel Process 가 관찰되었다.
Parallel 관련 wait event 도 발생됨 .
책만 의지하지말고 실제 테스트를 해바라~~

Parallel Query 의 조인시 Row Distribution

대용량 Parallel 쿼리 에서 조인을 사용할 경우 성능이 저하되는 경우가 많이 있다.
이경우의 원인은 여러가지가 있다.
원인 중에서 가장 치명적인 것은 잘못된 Row Distribution (Row 의 분배방법) 에 있다.
옵티마이져의 잘못된 Row Distribution 을 피하기 위하여 원리및 사용방법 그리고 최후의 방법으로 힌트를 통한 잘못된 Row Distribution 을 피하기 등에 대하여 알아본다.
필자가 이주제를 선택한 이유는 예전에 필자가 그랬듯이 이해하기가 힘들고 DBA 및 튜너라고 할지라도 모르는 사람들이 많이 있기 때문이다.
그렇기 때문에 최대한 이해하기 쉽게 설명 하였다.

h4. Row Distribution Method
Parallel 옵션을 사용한 Select 에서 조인시에 Row 의 분배방법에는 4 가지가 있다.

한가지씩 알아보자

분배방법	설명
Hash	조인기준컬럼을 Hash Function 을 이용하여 양쪽 테이블을 Mapping 하는 방식임. 조인컬럼 기준으로 각각의 Temp 성 매핑테이블을 만들고 마지막에 Join 하는 방식이다.
Broadcast	조인된 양쪽테이블에서 한쪽 테이블의 모든 Row를 다른쪽 테이블을 Scan 시에 분배하는 방식이다. BroadCast 받는 Table의 Scan 시에 자동으로 조인이 되므로 따로 Join Operation 이 필요가 없다. 하지만 Broadcast 하는측 테이블의 사이즈가 커지면 Parallel Slave 마다 Outer Table 을 반복적으로 BroadCast 해야 하므로 비효율이 커진다.
Partition	파티션을 이용하여 조인이된 양쪽테이블의 Row 를 분배한다. 2개의 조인된 테이블 중에서 조인컬럼을 기준으로 반드시 한쪽 테이블은 파티션이 되어 있어야한다. 파티션이 안된 테이블을 조인컬럼을 기준으로 파티션하여 Row 를 분배하는 방식이다. 이분배방식은 Partition Wise Join 과 관계가 있다.
None	이미 조인컬럼기준으로 파티션 된 테이블은 Row 가 파티션기준으로 자동으로 분배되거나 Broadcast 방식일 경우 분배를 받는쪽 테이블의 Row 는 따로 분배가 필요 없으므로 None 으로 표현된다.

Partition Wise Join

파티션 키를 이용하여 병렬로(Parallel Hint사용) 조인할 때 오라클은 관련된 파티션끼리만 조인을 시도하는 Partition-wise join방식을 사용하게 된다. 이 방식은 조인되는 2개의 테이블이 모두 조인 컬럼으로 동일하게 파티션되어 있고 두 테이블 모두 Parallel 대상일 경우, 동일 범위의 파티션끼리만 조인을 시도하는 Full partition-wise join 방식과 조인 컬럼으로 파티션되어 있는 한 개의 테이블만 파티션별로 조인을 시도하는 Partial partition-wise join 방식으로 나뉜다.

조인시 Row Distribution 의 Combination

한테이블의 Row 분배방식을 알았으니 이젠 양측 테이블의 Row 분배를 조인을 위하여 결합해야 하는데
4가지 분배방식 중에서 Oracle 에서 허용되는 Combination 은 아래처럼 6가지 이다.

Outer Table	Inner Table	설명
HASH	HASH	양쪽 테이블의 사이즈가 비슷하고 Hash Join 이나 Sort Merge 조인을 사용할때 권장된다.
BROADCAST	NONE	Outer Table 의 사이즈와 Inner Table 의 사이즈를 비교하여 Outer 테이블의 사이즈가 훨씬적을때 권장된다. 예를들면 코드 테이블과 대용량 테이블을 조인할때 적격이다. 왜냐하면 Inner Table 의 Granule 갯수 만큼 Outer 테이블의 Row 가 반복해서 제공되어야 하기 때문에 Broadcast 하는쪽의 테이블이 크면 I/O 양이 급격히 늘어난다.
NONE	BROADCAST	BROADCAST,NONE(바로위) 방법과 같으나 순서가 정반대 이다. 다시말해 Inner 테이블이 Broadcast 된다. Outer Table 의 사이즈와 Inner Table 의 사이즈를 비교하여 Inner 테이블의 사이즈가 훨씬적을때 권장된다. \--> Outer 가 Driving 되는 Hash Join 을 사용시 최악의 Combination 임.
PARTITION	NONE	Outer 테이블을 조인된 컬럼기준으로 Partition을 하여 Row 를 분배하며 Partition Wise 조인을 한다.
NONE	PARTITION	Inner 테이블을 조인된 컬럼기준으로 Partition을 하여 Row 를 분배하며 Partition Wise 조인을 한다.
NONE	NONE	조인이되는 양측의 테이블이 이미 조인컬럼 기준으로 파티션이 되어 있을때 따로 분배가 필요없으므로 이런 Combination 이 발생한다.(양측 테이블이 파티션 기준으로 분배된다.)

PQ_DISTRIBUTE 힌트의 사용

파티션 분배방식을 제외하면 양측 테이블의 Size 가 비슷한 경우는 분배방식은 Hash, Hash 로 풀려야 하고 코드성 테이블과 같이 소형 테이블과 대형테이블의 조인인경우는 Broadcast, None 으로 풀려야 한다.
그럼에도 불구하고 Optimizer 가 잘못된 분배방식의 Combination 을 선택하였다면 10중 8, 9 는 통계정보를 제대로 생성해주면 된다.
왜냐하면 파티션 분배방식을 제외하고 Broadcast 나 Hash 등의 분배방식을 선택할? Row 수 및 평균 Row 의 길이 등이 결정적인 영향을 끼치기 때문이다.
하지만 Temp 성 테이블이나 Global temp Table 등을 사용하면 통계정보가 아예 없다.
또한 통계정보가 있어도 Optimizer 잘못된 선택을 할수도 있다.
이때 사용할수 있는 힌트중의 하나가 PQ_DISTRIBUE 이다.

아래의 힌트 옵션을 보고 실제 SQL 을 살펴보자.

/\+ PQ_DISTRIBUTE(inner 테이블명 outer_distribution, inner_distribution) \/

위의 힌트에서 보듯이 Inner 테이블명이나 Alias 를 먼저적고 Row 분배방식의 Combination 을 작성하면 된다.

parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)

Parallel Join Filter

Bloom Filter 라고도 하는데 그이유는 알고리즘을 최초로 개발한 사람이 오라클사의 Burton H. Bloom 이라는 사람이고 이는 1970 년의일이다.
실제로 실행계획상에 Bloom 의 이름을 따서 필터명이 BF0000, BF0001, BF0003 .... 이렇게 생성된다.
어쨋든 이런사유로 인하여 2개의 용어가 혼용되어 사용된다.

제약사항

Parallel Join Filter 는 10gR2 이상에서 실행가능함.

테스트용 스크립트


create table emp_1
as
with a as
(select /*+ materialize */ level + 10000000 as empno,
chr(mod(level,90)) as big_ename, chr(mod(level,90)) as big_addr
from dual
connect by level <= 100) -- 실제 블로그에는 100000이나 너무 오래걸려 줄였음 ㅋ
select empno,
lpad(big_ename, 3000,big_ename) as big_ename ,
lpad(big_addr, 3000,big_addr) as big_addr
from a ;

create table emp_2
as
select * from emp_1 ;

EXEC dbms_stats.gather_table_stats(user,'EMP_1');
EXEC dbms_stats.gather_table_stats(user,'EMP_2');

아래 PLAN 을 설명하기전에 일단 TQ(Table queues) 개념을 알아야 한다.
복잡한 plan 같지만 원리를 알고 나면 간단하게 해석 할수 있다.
TQ 는 processes간의 데이터를 주고받는 기능을 한다.
하나의 TQ 는 여러개의 parallel Slave 를 가진다.
아래 PLAN 을 보면 TQ 가 3개(:TQ10000, :TQ10001, TQ10002 ) 생성되어 있다.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    45 |   527K|     8  (13)|        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 |    45 |   527K|     8  (13)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          |    45 |   527K|     8  (13)|  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |          |    45 |   263K|     4   (0)|  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |    45 |   263K|     4   (0)|  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |    45 |   263K|     4   (0)|  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL| EMP_1    |    45 |   263K|     4   (0)|  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE          |          |   100 |   586K|     4   (0)|  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001 |   100 |   586K|     4   (0)|  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |          |   100 |   586K|     4   (0)|  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL| EMP_2    |   100 |   586K|     4   (0)|  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."EMPNO"="T2"."EMPNO")
   7 - filter("T1"."BIG_ENAME">'1')

각 Id 단위의 설명

Q1,00 의 slave process 들은 emp_1 테이블을 full scan 하면서 t1.BIG_ENAME > '1' 조건을 FILTER 하였고 process 간의 통신을 위하여 걸러진 데이터를 Q1,02 에 보낸다.(Id 기준으로 5~7 이 여기에 해당된다)
Q1,02 의 slave process 들은 1번에서 받은 데이터들을 이용해 hash table 을 만든다.(Id 기준으로 3~4 가 여기에 해당된다)
Q1,01 의 slave process 들은 emp_1 테이블을 full scan 하고 읽은 데이터를 Q1,02 에 보낸다.(Id 기준으로 9~11 가 여기에 해당된다)
Q1,02 의 slave process 들은 3번에서 던진 데이터를 받아서 미리 만들어진 hash 테이블을 검색하면서 조인작업을 진행하고 결과를 Query Cordinator 에 보낸다.(Id 기준으로 2~3 이 여기에 해당된다)
Query Cordinator 는 각 TQ 로 부터 데이터를 받아서 취합한후에 결과를 Return 한다.(Id 기준으로 0~1 이 여기에 해당된다)

위 그림을 보면 무언가 비효율적인 것을 발견하게 된다.

Q1,01 의 모든 SLAVE 들은 Q1,02 의 모든 SLAVE 들에게 똑같은 데이터를 던져서 체크한후에 만족하면 조인에 성공하고 그렇지 않으면 조인에 실패하는 프로세스를 가지게 된다.
위 쿼리를 예를들면 사번 10000100을 Q1,02 의 SLAVE 가 8개라면 8번 던져서 1/8 확률로 조인에 성공하면 다행이지만 아예조인에 실패할 확률도 있는것이다.
이런 비효율을 없애는 것이 Parallel Join Filter 이다.
Parallel Join Filter 의 개념은 Q1,01(후행테이블의 TQ) 이 Q1,02 에게 데이터를 전달하기전에 불필요한 데이터를 걸러 낸다는 것이다.

이제 parallel join filter 를 적용시켜보자.


explain plan for
SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) PX_JOIN_FILTER(t2) */
*
FROM emp_1 t1,
emp_2 t2
WHERE t1.empno = T2.empno
and t1.BIG_ENAME > '1'  -- 이 조건이 없으면 Parallel Join Filter 는 결코 작동하지 않는다.(없으면 트릭 : t1.empno > 0 )
;

필자(블로그 집필자요~)의 연구결과 t1.ename > '1' 등 t1 의 filter predicate 가 없으면 Parallel Join Filter 는 결코 작동하지 않는다.
그럴때는 t1.empno > 0 등의 결과값의 영향을 끼치지 않는 filter 조건을 주는 트릭을 생각할수 있다.
또하나의 Tip 은 PX_JOIN_FILTER 사용시 후행테이블을 사용하여야 한다는것이다.
왜냐하면 아래의 PLAN 을 보면 Filter 의 생성은 t1 에서 하지만(id 가 4번) 사용은 t2 쪽(id 11번)에서
하기때문에 PX_JOIN_FILTER(t1) 을 주면 절대 filter operation 이 생기지 않는다.


----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    45 |   527K|     8  (13)|        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 |    45 |   527K|     8  (13)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED    |          |    45 |   527K|     8  (13)|  Q1,02 | PCWP |            |
|   4 |     PX JOIN FILTER CREATE| :BF0000  |    45 |   263K|     4   (0)|  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE          |          |    45 |   263K|     4   (0)|  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |    45 |   263K|     4   (0)|  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |    45 |   263K|     4   (0)|  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| EMP_1    |    45 |   263K|     4   (0)|  Q1,00 | PCWP |            |
|   9 |     PX RECEIVE           |          |   100 |   586K|     4   (0)|  Q1,02 | PCWP |            |
|  10 |      PX SEND HASH        | :TQ10001 |   100 |   586K|     4   (0)|  Q1,01 | P->P | HASH       |
|  11 |       PX JOIN FILTER USE | :BF0000  |   100 |   586K|     4   (0)|  Q1,01 | PCWP |            |
|  12 |        PX BLOCK ITERATOR |          |   100 |   586K|     4   (0)|  Q1,01 | PCWC |            |
|  13 |         TABLE ACCESS FULL| EMP_2    |   100 |   586K|     4   (0)|  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."EMPNO"="T2"."EMPNO")
   8 - filter("T1"."BIG_ENAME">'1')

위 plan 은 원래의 PLAN(filter 적용전 plan) 에서 parallel join filter 부분만이 추가 되었다.(파란색 부분)
1. id 4 에서 parallel Join filter 를 생성(create) 하였고 filter 명은 :BF0000 이다.
2. id 11 에서 생성된 :BF0000 filter 를 사용하였다.

parallel Join filter 주의사항

parallel Join filter 를 무조건 사용하지말고 아래와 같은 경우에 사용하라. 왜냐하면 걸러지는 데이터가 별로 없을경우 빨라지지도 않을 뿐더러 filter 부하가 더클수 있기 때문이다.
많은양의 데이터가 조인에 실패하는경우
1번을 만족하면서 RAC 에서 multi-node 로 Parallel Query 를 실행한경우.
이경우는대부분 DOP(Degree Of Parallelism)가 클때 발생하며 추가적인 Network I/O 가 발생하므로 parallel join filter 를 적용할경우 획기적인 성능향상을 기대할수 있다.

parallel Join filter에 의해서 filter 된 데이터를 보려면 v$sql_join_filter 뷰를 사용하면된다.


select filtered, probed, proved - filtered as sent
from v$sql_join_filter
where qc_session_id = sys_context('userenv', 'sid');

Group By Push Down

"Parallel Query 를 수행하면 Group By 가 두번 수행된다. 따라서 Group By 가 있는 SQL 은 Parallel 을 사용하지 마라."
실제 DBA 들의 입에서 오고 가는 말들이다. 잘못된 미신이 퍼져 있다니 참으로 안타까운 현실이 아닐수 없다.
Parallel Query 를 수행하면 Group By 가 무조건 두번 수행되는 것은 아니며 두번 수행 된다면 오히려 성능향상을 기대할 수 있다.

TQ

1.TQ 는 Processes간의 데이터를 주고받는 기능을 한다.
2.하나의 TQ 는 여러개의 parallel Slave 를 가진다.
3.TQ 는 Parallel Query 수행시 생성된다.

Group By Push Down 이 뭐야?*

1.Group By Push Down 이란 TQ 에 데이터를 전달하기 전에 Group By 를 수행하여 데이터의 건수를 대폭 줄인후에 TQ 에 데이터를 전달함으로서 일량을 줄이고 성능을 향상시키는데 목적이 있다.
2.Group By Push Down 은 Parallel Query 에 Group By 가 포함되어 있는 경우 발생한다.

어떻게 하는건데?

참고로 이 힌트는 11g에서 사용 가능하다.


SELECT   /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) GBY_PUSHDOWN */
         prod_id, cust_id, COUNT (*) cnt
    FROM sh.sales A
GROUP BY prod_id, cust_id;

GBY_PUSHDOWN 란 힌트를 사용하였으며 아래의 Plan 에서 보는바와 같이 성공적으로 Group By Push Down 이 수행되었다

---------------------------------------------------------------------------
| Id  | Operation                | Name     | E-Rows | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |          |         |
|   1 |  PX COORDINATOR          |          |        |          |         |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    359K|          |         |
|   3 |    HASH GROUP BY         |          |    359K| 2197K (0)|         |
|   4 |     PX RECEIVE           |          |    359K|          |         |
|   5 |      PX SEND HASH        | :TQ10000 |    359K|          |         |
|   6 |       HASH GROUP BY      |          |    359K| 3284K (1)|    1024 |   -- Group By 가 수행. temp tablespace사용
|   7 |        PX BLOCK ITERATOR |          |    918K|          |         |
|*  8 |         TABLE ACCESS FULL| SALES    |    918K|          |         |
---------------------------------------------------------------------------

\

원래 수행되어야 하는 Group By 는 Id 기즌으로 3번(파랑색 부분) 이지만 Id 6번에서 먼저 Group By 가 수행되었다. 그이유는 Id 5번의 :TQ10000 에게 데이터를 전달하기전에 데이터를 줄여서 성능을 향상시키기 위함이다. 실제로 옵티마이져는 Id 5번에서 Group By 된 359K Row에 대한 데이터만 처리 할것으로 예상하고 있다.
튜닝은 Trade Off 이다.
주의 사항이 있다. 실제로 TQ의 일량은 줄어들지만 불필요한 Hash Group By 혹은 Sort Group By 가 수행되어 성능이 더 나빠질수 도 있다는것이다. Id 6 에서 Hash Area Size 가 부족하여 Disk 작업(Used-Tmp 부분 참조)이 발생하였다. 따라서 Group By 를 하면 건수가 몇배~ 몇십배이상 줄어드는 경우에 적용하여야 하며 Disk 에서 Sort 및 Hash 작업이 발생하는 경우는 PGA 튜닝을 동반하여야 한다.

NO_GBY_PUSHDOWN 힌트를 사용하면 Group By Push Down이 발생하지 않는다.


SELECT   /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) NO_GBY_PUSHDOWN */
         prod_id, cust_id, COUNT (*) cnt
    FROM sh.sales A
GROUP BY prod_id, cust_id;

----------------------------------------------------------------
| Id  | Operation               | Name     | E-Rows | Used-Mem |
----------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |          |
|   1 |  PX COORDINATOR         |          |        |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |    359K|          |
|   3 |    HASH GROUP BY        |          |    359K| 4492K (0)|
|   4 |     PX RECEIVE          |          |    918K|          |
|   5 |      PX SEND HASH       | :TQ10000 |    918K|          |
|   6 |       PX BLOCK ITERATOR |          |    918K|          |
|*  7 |        TABLE ACCESS FULL| SALES    |    918K|          |
----------------------------------------------------------------

정상적으로 Group By 가 한번만 수행되었지만 옵티마이져는 TQ 의 일량이 Group By Push Down 이 적용된 SQL 에 비하여 918K 로 약 3배정도 중가한것으로 판단하였고 실제로도 그렇게 수행된다.
Group By Push Down은 11g 신기능이 아니다.
GBY_PUSHDOWN / NO_GBY_PUSHDOWN 등의 힌트는 11g 에서 새로 나온 것 이다. 하지만 이전버젼에서도 _groupby_nopushdown_cut_ratio 파라미터를 0 으로 세팅하면 Group By Push Down 을 강제로 수행할수 있다. 이파라미터의 Default 값은 3 이며 이경우는 Group By Push Down 의 수행여부를 옵티마이져가 판단한다.
아래는 옵티마이져의 Costing 과정을 10053 Trace 에서 발췌한 것이다.


*********************************
Number of join permutations tried: 1
*********************************
GROUP BY adjustment factor: 0.707107
GROUP BY cardinality:  359386.000000, TABLE cardinality:  918843.000000
Costing group-by pushdown:
    SORT ressource         Sort statistics
      Sort width:         598 Area size:      552960 Max Area size:   104857600
      Degree:               1
      Blocks to Sort: 563 Row size:     20 Total Rows:         229711
      Initial runs:   2 Merge passes:  1 IO Cost / pass:        306
      Total IO sort cost: 869      Total CPU sort cost: 230852464
      Total Temp space used: 4629000
  Distribution cost: resc_cpu: 91885309  resp_cpu: 22971327
  Costing final group-by:
  Subtracting no-pushdown group-by:
    SORT ressource         Sort statistics
... 이후 생략

결론

적재적소에 사용하자.
실제로 옵티마이져는 Group By Push Down 의 수행여부를 Cost 로서 판단하고 있으므로 기본적으로는 오라클에 맏기면 된다.
하지만 아주 복잡한 SQL 이나 옵티마이져가 판단을 잘못할 경우에 Group By Push Down 을 수동으로 적절히 발생시킨다면 성능향상을 꾀할수 있다.