Parallel Query 수행시 튜닝 방법에 대해 아래와 같이 2개의 글을 연재한 바 있다.
1.Parallel Query 의 조인시 Row Distribution  --> Join 시 Row 의 분배방법 튜닝
2.Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) --> Join Filter 튜닝

Parallel Query 의 튜닝방법은 많지 않아...
Parallel Query 의 튜닝 방법은 많지 않은데 그 이유는 Parallel Query 기능 자체를 튜닝 하는것이 아니라 Hash 조인의 튜닝 혹은 Block I/O 의 튜닝등 Parallel 자체와는 상관없는 것 을 튜닝하는 경우가 많기 때문이다.
오늘은 3번째로 Parallel Query 자체를 튜닝 하는 또다른 방법에 대하여 논의 할것이다.

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

TQ 를 알고 가자.
먼저 Group By Push Down 기능을 설명하기 전에 TQ(Table queues) 개념을 알아야 한다.
1.TQ 는 Processes간의 데이터를 주고받는 기능을 한다.
2.하나의 TQ 는 여러개의 parallel Slave 를 가진다.
3.TQ 는 Parallel Query 수행시 생성된다.
상세한 개념은 위에서 언급한 글중 2번을 참조하기 바란다.

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

원리는 같다.
이기능은 마치 DW 용 SQL 작성시 Fact 테이블(대용량) 을 먼저 Group By 한후에 Dimension 테이블(소용량 코드 테이블)과 조인하여 조인 건수를 대폭 줄임으로서  성능 향상을 꾀하는것과 같은 개념이다. 이기능은 오라클이 자동으로 해주기도 하는데 이것을 "Group By Placement" 라고 하며 추후 따로 글을 올릴 생각이다.

어떻게 하는건데?
Group By Push Down을 수행하기위해 아래의 SQL 을 보자.
환경: Oracle 11g 11.1.0.7

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 |
|   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 튜닝을 동반하여야 한다.

Group By Push Down 이 적용되지 않은 Plan 을 보여다오.
GBY_PUSHDOWN 힌트 대신에 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 을 수동으로 적절히 발생시킨다면 성능향상을 꾀할수 있다.

편집후기:
필자가 이글을 올린다고 하니 어느 지인은 컨설턴트의 밥그릇 타령을 한다.
다시말해 이런것들을 모든 개발자가 안다면 컨설턴트가 설자리가 없다는 것인데...
글쎄...
여러분은 어떻게 생각하는가?    

신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.08.12 11:43 신고  댓글주소  수정/삭제  댓글쓰기

    이런 글을 올린다고 해서 밥그릇이 없어진다면 Jonathan Lewis나 Steve Adams는 이미 굶어 죽었을 겁니다. ^^;

    이런 기세로 계속 정리하시면 Parallel Execution에 대해 좋은 내용의 글모음(아마 이번 책으로도 나오겠죠?)이 되겠네요. 개인적으로 한번 다루어주셨으면 하는 것이 RAC 환경에서의 Parallel Query에 대한 내용입니다. RAC와 PQ가 합쳐지면 많은 사람들에게 더 미지의 영역이지요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.08.12 12:53 신고  댓글주소  수정/삭제

      저도 같은 생각입니다.
      이런글을 올릴수록 오히려 설자리가 늘지 않을까요?
      RAC + PQ 가 합쳐진 경우는 고려 해야할 것 들이 많네요. 미쳐 생각치 못했던 부분인데 좋은정보 감사드립니다.
      내년에 나올책은 지면이 400 페이지로 한정적이라 ....
      한번 고려해 보아야 할거 같습니다.

  2. feelie 2009.08.12 21:18 신고  댓글주소  수정/삭제  댓글쓰기

    얼마전부터 이곳에서 좋은 자료를 보고 있는데 이제야 댓글을 달아봅니다.
    좋은 내용 감사합니다.
    오라클을 조금안다고 생각했는데. 이곳의 내용을 보니 오라클을 전혀 모르고 있었다는 생각이 듭니다.
    좋은 책들도 추천해주셨는데 대부분이 원서라 조금 아쉽습니다.
    혹시 저정도의 수준에서 (대용량데이터베이스 정독수준) 고수로 가기위해서 해주실 얘기나 책이있으면(원서는 제가 좀) 부탁드리겠습니다.
    다음주 내용도 기대가 됩니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.08.12 22:41 신고  댓글주소  수정/삭제

      원서를 싫어하시니 답변하기가 어렵네요.^^
      하지만 제가 아는데로 나열해 보겠습니다.
      2가지 관점 입니다. 모델링과 튜닝.
      개발관점에서 제일 좋은것은 대용량 시리즈 이기때문에 제외 하였습니다.
      제일 좋은것은 오라클 메뉴얼 입니다.--> 영문임
      메뉴얼 중에서도 Concept Guide, Admin Guide, Performace Tuning Guide 를 추천합니다.
      그것이 영문이라서 싫으시면 OCP 과정의 책을 차선으로 추천합니다. 총 4권으로 알고 있고 한글도 있는것으로 알고 있습니다.
      다음으로 OTN 과정중 RAC for Admin 교육도 한글 책으로 나온것을 보았습니다.
      그리고 OWI 관련 책은 단연 조동욱님의 책 2권을 추천합니다. 튜닝을 하려면 OWI 는 필수라고 할수 있습니다.
      그리고 트랜잭션을 깊이 있게 공부하시려면 김시연님의 책을 추천합니다.
      오라클을 전체 DBMS 관점에서 산을 보고 튜닝하려면 이번에 나온 조시형님의 오라클 성능 고도화 해법시리즈에서 3장 을 추천합니다. 물론 다른장의 내용들은 기본적으로 아셔야 합니다.
      그리고 중요한 모델링 책인데요.
      어쩔수 없이 원서를 추천할수 밖에 없습니다.
      Richard Barker 가 1990년에 저술한 Case*Method: Entity Relationship Modelling 이란 책 입니다.
      저는 이책보다 좋은 책을 아직 보지 못했습니다.
      원서라고해도 반드시 정복해야 할 책입니다.
      지금 이책을 살수 있을지는 저도 확신할수 없습니다.
      그리고 원서를 쉽게 읽을수 있는 방법이 있습니다.
      여러군데의 카페에서 유명한 원서들의 스터디를 진행하고 있으니 기회가 되면 반드시 참석하시기 바랍니다.
      감사합니다.

    • feelie 2009.08.13 11:17 신고  댓글주소  수정/삭제

      너무 성의 없이 날로 먹으려고 생각하실수도 있지만
      여러 카페라면 이것도 좀 추천을 해주실수 있는지요?

      답변 감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.08.13 12:43 신고  댓글주소  수정/삭제

      메일을 남겨주시면 메일로 답변드리겠습니다.

  3. feelie 2009.08.13 16:43 신고  댓글주소  수정/삭제  댓글쓰기

    feelie@empal.com 입니다.
    도움 주시니 열심히 열공하겠습니다.
    감사합니다..

  4. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.07.01 16:23 신고  댓글주소  수정/삭제  댓글쓰기

    저같은 경우엔(개발자입니다.)

    extremedb님의 글을 볼 때마다 아...이래서 컨설턴트가 대단한 거구나..하고 감탄을 합니다.

    대대수의 사람들에게 오히려 전문성을 더 보여줄 수 있는 방법이 아닐까 합니다.

    보통, 어렵고 힘들다는건 알면 알수록 더 많이 느끼니까요 ㅎ
    (최근에 한동안 놓았던 영어공부를 다시 시작했는데요,
    예전엔 몰랐는데 해보니까 더 어렵고 막막하다는 걸 느끼고 있습니다...)

필자가 7월달에  Parallel Query 의 조인시 Row Distribution 이라는 글을 통하여 Parallel + Join 시에
튜닝방법을 설명 한바 있다.
오늘은 최적의 Parallel Join 을 하기 위하여 또다른 튜닝방법을 제시한다.
필자가 이글을 쓰는 원래의 용도는 사내 DB 컨설턴트 들을 교육시키는데 사용하는 것이다.
그렇기 때문에 어려워도 실망하거나 우울증에 걸리지 말자.
최근 최진실씨 사태등등 해서 심히 걱정된다.^^
Parallel Join Filter 를 설명하려고 하는데 용어설명부터 해야겠다.
왜냐하면 어떤곳에서는 Parallel Join Filter 라고 이야기 하고 또다른 곳에서는 Bloom Filter 라고 하는데
그이유는 알고리즘을 최초로 개발한 사람이 오라클사의 Burton H. Bloom 이라는 사람이고 이는 1970 년의
일이다.
실제로 실행계획상에 Bloom 의 이름을 따서 필터명이 BF0000, BF0001, BF0003 .... 이렇게 생성된다.
어쨋든 이런사유로 인하여 2개의 용어가 혼용되는데 여기서는 Parallel Join Filter (힌트로는 px_join_filter) 만 사용할것이다.
아래는 테이블 생성 스크립트 이다.
테스트를 위하여 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 <= 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');


테이블이 생성 되었으므로 테스트 스크립트를 실행시켜보자.

explain plan for
 SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) NO_PX_JOIN_FILTER(t2) */
        *
  FROM emp_1 t1,
            emp_2 t2
 WHERE t1.empno = T2.empno
       and t1.BIG_ENAME > '1'  ;

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


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

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

위설명을 도식화 하면 아래그림과 같다.
다만 위의 SQL 대로라면 각 TQ 내의 SALVE 는 8개 여야 하지만 화면관계상 2개로 줄여서 나타 내었다.

사용자 삽입 이미지

위그림을 보면 무언가 비효율적인 것을 발견하게 된다.
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'  ;

필자의 연구결과 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     | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 12132   (1)|        |      |            |
|   1 |  PX COORDINATOR          |          |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 | 12132   (1)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED    |          | 12132   (1)|  Q1,02 | PCWP |            |
|   4 |     PX JOIN FILTER CREATE| :BF0000  |  3054   (0)|  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |  3054   (0)|  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| EMP_1    |  3054   (0)|  Q1,00 | PCWP |            |
|   9 |     PX RECEIVE           |          |  3054   (0)|  Q1,02 | PCWP |            |
|  10 |      PX SEND HASH        | :TQ10001 |  3054   (0)|  Q1,01 | P->P | HASH       |
|  11 |       PX JOIN FILTER USE | :BF0000  |  3054   (0)|  Q1,01 | PCWP |            |
|  12 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,01 | PCWC |            |
|  13 |         TABLE ACCESS FULL| EMP_2    |  3054   (0)|  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------

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

주의사항은  parallel Join filter 를 무조건 사용하지말라는 것이다.
걸러지는 데이터가 별로 없을경우 빨라지지도 않을 뿐더러  filter 부하가 더클수 있기 때문이다.
다음의 2가지 경우에  parallel Join filter 를 사용하여야 한다.
1. 많은양의 데이터가 조인에 실패하는경우
2. 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');

결론 :
Parallel Join distribution 과 Parallel join filter 을 적절히 이용하면 최적화된 Parallel Join Query를 만들수 있다.
다시한번 말하지만 꼭필요한 경우에만 이런종류의 힌트를 사용하여야 한다.
 
편집후기 :만약 parallel Join filter 로직에 관심이 있어서 직접 구현하려면 아래의 1번 문서를 참조하기 바란다.

Reference :
1.Bloom Filters ( Cristian Anatognini )
2.Oracle Corp Manual (Data Warehousing Guide 11g)

신고
Posted by extremedb

댓글을 달아 주세요

  1. 금땡이 2008.10.13 19:28 신고  댓글주소  수정/삭제  댓글쓰기

    전체적 내용은 이해하겠는데, 세부적으로 보면 제 지식의 한계가..ㅠㅠ
    잘 읽고, 잘 담아 갑니다.

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.10.13 21:23 신고  댓글주소  수정/삭제  댓글쓰기

    10 그램 에서 나온 새기능이므로 아직 사용하는 사람이 없지만 정확한 용도와 용법을 안다면 적용할곳이
    적지않을 걸로 생각 합니다.
    참 제가 농담삼아 oracle 10g 대신에 오라클 10 그램으로 발음 합니다.^^
    오해 없으시길...

  3. Favicon of http://oraschool.tistory.com BlogIcon 시연아카데미 2008.10.15 10:55 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 잘 보고 갑니다. 감사합니다~!

  4. sid 2011.01.27 21:13 신고  댓글주소  수정/삭제  댓글쓰기

    예전에 실행계획 보면서 이건 도대체 뭔 소리야!!
    했던 실행계획의 비밀이 풀렸습니다.
    감사합니다 ^^

대용량 Parallel 쿼리 에서 조인을 사용할 경우 성능이 저하되는 경우가 많이 있다.
이경우의 원인은 여러가지가 있다.
원인 중에서 가장 치명적인 것은 잘못된 Row Distribution (Row 의 분배방법) 에 있다.
옵티마이져의 잘못된 Row Distribution 을 피하기 위하여 원리및 사용방법 그리고 최후의 방법으로 힌트를 통한 잘못된 Row Distribution 을 피하기 등에 대하여 알아본다.

필자가 이주제를 선택한 이유는 예전에 필자가 그랬듯이 이해하기가 힘들고 DBA 및 튜너라고 할지라도 모르는 사람들이 많이 있기 때문이다.
그렇기 때문에 최대한 이해하기 쉽게 설명 하였다
.  

1. Row Distribution Method

Parallel 옵션을 사용한 Select 에서 조인시에 Row 의 분배방법에는 4 가지가 있다.
한가지씩 알아보자

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




2.조인시 Row Distribution 의 Combination

한테이블의 Row 분배방식을 알았으니 이젠 양측 테이블의 Row 분배를 조인을 위하여 결합해야 하는데
4지 분배방식 중에서 Oracle 에서 허용되는 Combination 은 아래처럼 6가지 이다.
보는 방법은 Comma( , ) 왼쪽이 Outer Table 오른쪽이 Inner Table 이다.
다시말하면 조인이 왼쪽테이블에서 오른쪽 테이블로 진행된다.

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


                           
3. 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 을 작성하면 된다.

예제1)

SELECT /*+ORDERED PARALLEL(r 4) PARALLEL(s 4) PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
FROM r,s
WHERE r.c=s.c;

예제1) 은 Outer Table(R) 과 Inner Table(S) 의 SIZE 가 비슷하므로 각각 Hash 분배방식으로 힌트를 사용하였다.


예제2)

SELECT /*+ORDERED PARALLEL(a 4) PARALLEL(b 4) PQ_DISTRIBUTE(b BROADCAST, NONE) USE_HASH (b) */ column_list
FROM a,b
WHERE a.c = b.c;

예제2)는 Outer Table(a) 가 Inner Table(b) 보다 훨씬 적으므로 BROADCAST, NONE 방식을 취하도록 힌트를 사용하였다.
다시말하면 b 테이블 scan 시에 발생하는 Parallel 의 각각의 Slave 마다  Brodcast 된 a 테이블과의 조인을 동시에 하겠다는 뜻이다.


예제3)
CREATE TABLE dept2 AS SELECT * FROM departments;

ALTER TABLE dept2 PARALLEL 2;

CREATE TABLE emp_comp PARTITION BY RANGE(hire_date)
SUBPARTITION BY HASH(department_id) SUBPARTITIONS 3
(
PARTITION emp_p1 VALUES LESS THAN (TO_DATE(’1-JAN-1992’,’DD-MON-YYYY’)),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE(’1-JAN-1994’,’DD-MON-YYYY’)),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE(’1-JAN-1996’,’DD-MON-YYYY’)),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE(’1-JAN-1998’,’DD-MON-YYYY’)),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE(’1-JAN-2001’,’DD-MON-YYYY’))
)
AS SELECT * FROM employees;

ALTER TABLE emp_comp PARALLEL 2;

EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(d NONE PARTITION) ORDERED */ e.last_name,
            --> dept2 를 department_id 로 dynamic 파티션을 하여 row 를 분배하겠다는 의미임.
d.department_name
FROM emp_comp e, dept2 d
WHERE e.department_id = d.department_id;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

사용자 삽입 이미지


예제3)은 dept2 테이블을 조인기준컬럼인 department_id 로 Dynamic Partition 하여 ROW 를 분배한다.
dept2 테이블은 파티션이 되어 있지않으므로 Partial Partioin Wise Join 이 발생 하였다.
Plan 상의 분배방식도 힌트에서 의도한대로 Part(key) 로 나타났다


예제4)
CREATE TABLE dept_hash
PARTITION BY HASH(department_id)
PARTITIONS 3
PARALLEL 2
AS SELECT * FROM departments;

EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(e NONE NONE) ORDERED */ e.last_name,
            --> 이미 양측 테이블이 파티션이 되어있으므로 분배방식이 따로 필요가 없으나
                  (Hash, hash) 나 (broadcast, none) 등으로 풀리는 것을 방지하는 차원에서 힌트를 사용함.

d.department_name
FROM emp_comp e, dept_hash d
WHERE e.department_id = d.department_id;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

사용자 삽입 이미지


예제4)는 양측 테이블이 모두 조인기준컬럼으로 파티션 되어 있기 때문에 Full Partition Wise Join 되었으며 분배방식에도 아무것도 나타나지 않았다( 아무것도 나타나지 않으면 None 임)
당연한 이야기 이지만 emp_comp 테이블의 department_id 컬럼은 Sub Partiotion 이다.
Sub Partiion 도 위의 Plan 처럼 Full Partition Wise Join 이 가능하다.

4.결론

Row 분배방식, 분배방식의 Combination , Optimizer 가 잘못된 분배방식을 선택할 경우를 위한 PQ_DISTRIBUTE 힌트의 사용등을 알아보았다.
잘못된 Row Distribution 을 피하기 위한 목적 뿐만 아니라  Parallel Operation 을 이해하기 위해서는 반드시 알야야 하니 다시한번 꼼꼼히 살펴보기 바란다.
저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2008.07.23 13:29 신고  댓글주소  수정/삭제  댓글쓰기

    PQ Distribution을 설명하실 때 v$pq_tqstat 뷰의 사용법을 같이 설명하시면 더 좋을 거 같습니다.

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.07.23 15:56 신고  댓글주소  수정/삭제  댓글쓰기

    안그래도 조만간에 주제를 Parallel 관련 모니터링에 대하여 글을 작성할 계획 입니다.
    조금만 기다려 주시기 바랍니다.

DML 과 PARALLEL의 관계

현재 많은 종류의 Oracle 튜닝 책에 Update, Delete 시의 parallel operation 관련하여
Partition 이 되어 있지 않으면 single mode 로 처리된다고 되어 있다.
하지만 이것이 맞는말인가?
하나씩 테스트를 해보자
테스트 환경은 Oracle 10g R2(10.2.0.3) 버젼이다.

테스트 시나리오
--고객테이블(100 만건) 의 고객영문명에 serial update 와 parallel update 를 한번씩 한다.
--고객테이블은 파티션이 되지않은 테이블이다.

1.update test

/**************serial update 시작******************/
alter session disable parallel dml; -- parallel 을 disable 한다.

update tb_cus set cus_enm = '1'; -- 100만건 update(17초)

commit;


아래는 trace 결과 이다.
trace 결과 가 깨지는 점을 이해하기 바란다.

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 16.410 16.999 845 27205 1032475 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 16.410 17.001 845 27205 1032475 1000000

Elapsed Time for Client(sec.): 17.000
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: SI31041 (ID=387)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 UPDATE TB_CUS (cr=27205 pr=845 pw=0 time=16998894 us)
1000000 TABLE ACCESS FULL TB_CUS (cr=27133 pr=845 pw=0 time=1000149 us)

/**************parallel update 시작******************/

alter session enable parallel dml; -- parallel 을 enable 한다.

update /*+ parallel(tb_cus 8) */ tb_cus set cus_enm = '1'; -- 100만건 update(8.7초)

commit;



Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.170 8.700 0 6 1 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 0.170 8.701 0 6 1 1000000

Elapsed Time for Client(sec.): 8.701
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: SI31041 (ID=387)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
8 PX COORDINATOR (cr=6 pr=0 pw=0 time=8791448 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 UPDATE TB_CUS (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL TB_CUS (cr=0 pr=0 pw=0 time=0 us)


2.delete test

update 테스트 결과 와 같이 parallel 옵션 사용시 전혀문제 없었음.
delete 테스트 결과는 생략함.


-- 테스트시 재미있는점은 PARALLEL 적용시에 TRACE 결과의 ROWS 에 DOP 수가 나온다는 점이다.
--일종의 버그인것 같다.

3.결론
파티션 되지않은 테이블을 update, delete 할때 parallel 옵션의 적용은 문제가
전혀 없는것으로 드러남.
V$PX_PROCESS 나 GV$PX_SESSION 등의 뷰에서도 정상적으로 Parallel Process 가 관찰되었다.
Parallel 관련 wait event 도 발생됨 .
따라서 최소한 10g 의 parallel 관련서적들은 모두 위의 테스트 결과대로
파티션되지 않은 테이블에 parallel update, delete는 적용되는걸로 수정하여야 한다.
하지만 테스트를 안해보고 서적을 집필한 저자나 출판사의 잘못만은 아니다.
왜냐하면 오라클 10g R2 Data Warehousing Guide 의 25-58에는 분명히 아래와 같이 적용불가능 하다고 나와 있다.

Parallel updates and deletes work only on partitioned tables.
If you are performing parallel UPDATE, MERGE, or DELETE operations, the DOP isequal to or less than the number of partitions in the table.

오라클 매뉴얼도 참조서적에 불과하다.
언제나 의심해보고 테스트를 해보아야 하는것을 잊지말자.

편집후기 :
Parallel DML은 내부적으로 쿼리변환(각각의 slave 쿼리가 Granule 단위로 쪼개짐)에 관계된다. 그런데 조나단루이스저서(cost base~) 의 9장을 참조해보면 쿼리변환과 관계해서 기능의 생명주기를 beta --> 처음으로 공식화 하는상태 -->최종상태 로 나타내고있다.
그런데 파티션 되지 않은 테이블의 parallel update, delete는 아직도 beta 상태인것 같다.
다시말하면 기능은 구현되어 있지만 여러가지문제들로 인하여 공식화 하지 않은상태라는 것이 필자의 생각이다.
참고로 11g 의 매뉴얼에도 10g 와 마찬가지로 공식적으로는 적용불가능이라고 되어 있다.
엑셈의 조동욱씨에 따르면 한가지 주의 할점은 Intra-partition parallelism이 항상 동작하는 것은 아니라는 것이다. 일부 제약이 있고, 또 제약이 없더라도 간혹 동작하지 않는 경우도 있는 것 같다고 한다.
이글을 쓰는데 도움을 주신 조동욱 수석과 비투엔의 김정삼 책임 오픈메이드 컨설팅의 김중국책임에게 감사드린다.

참조 URL :
1.http://youngcow.net/doc/oracle10g/server.102/b14223/usingpe.htm#CACEJACE
2.메타링크 문서제목 :What is Intra-partition parallelism, 문서 id : 241376.1

신고
Posted by extremedb

댓글을 달아 주세요

  1. 2009.02.26 15:54 신고  댓글주소  수정/삭제  댓글쓰기

    도움이 많이 됐습니다^^ 감사합니다~