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 |            |

 

l  v$pq_tqstat 의 활용법 소개

order by를 병렬로 수행하려면 테이블 큐를 통한 데이터 재분배가 필요한데,

쿼리 수행이 완료된 직후에 같은 세션에서 v$pq_tqstat를 쿼리해 보면 테이블 큐를 통한

데이터 전송통계를 확인해 볼 수 있다.

 

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       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 |            |

 

 

 

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 |            |

 

병렬 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 |            |

 

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 방식을 조정 할 수 있다.