1. Introduction
   1. 병렬 쿼리
   2. 병렬처리 유형
2. 병렬 신화
   1. 병렬 처리 오버헤드
3. 병렬 관리
   1. 병렬 작업을 할 수 있는데 간과되고 있는 것들..
4. 병렬 쿼리
   1. 병렬 쿼리 설정
   2. 언제 병렬 쿼리가 유용한가?
5. 병렬 DML
   1. DML 과 PARALLEL의 관계
   2. Parallel Query 의 조인시 Row Distribution
   3. 조인시 Row Distribution 의 Combination
   4. PQ_DISTRIBUTE 힌트의 사용
   5. parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)
   6. Group By Push Down

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 가지가 있다.

• 한가지씩 알아보자

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가지 이다.

PQ_DISTRIBUTE 힌트의 사용

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

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

위의 힌트에서 보듯이 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_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 을 수동으로 적절히 발생시킨다면 성능향상을 꾀할수 있다.