by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 병렬 Order By와 Group By




02. 병렬 Order By와 Group By

(1) 병렬 Order By
  • 테스트 데이터 생성
    SQL> CREATE TABLE 고객
      2  AS
      3  SELECT ROWNUM 고객ID
      4       , DBMS_RANDOM.STRING('U', 10) 고객명
      5       , MOD(ROWNUM, 10) + 1 고객등급
      6       , TO_CHAR(TO_DATE('20090101', 'yyyymmdd') + (ROWNUM-1), 'yyyymmdd') 가입일
      7    FROM dual
      8   CONNECT BY LEVEL <= 1000
      9  ;
    
    테이블이 생성되었습니다.
    
    SQL> exec dbms_stats.gather_table_stats(user, '고객');
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
  • 고객테이블을 병렬로 읽어 고객명 순으로 정렬
    SQL> set autot traceonly exp
    SQL> SELECT /*+ full(고객) parallel(고객 2) */
      2         고객ID, 고객명, 고객등급
      3    FROM 고객
      4   ORDER BY 고객명
      5  ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2364220803
    
    -----------------------------------------------------------------------------------------------------------------
    | 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 (ORDER) |
    |   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 |            |
    |   7 |        TABLE ACCESS FULL| 고객      |  1000 | 18000 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    -----------------------------------------------------------------------------------------------------------------
    
  • v$pq_tqstat 쿼리로 테이블 큐를 통한 전송 통계 확인
    SQL> set autot off
    SQL> /
    
    ...
    
    1000 개의 행이 선택되었습니다.
    
    SQL> break on dfo_no on tq_id on server_type
    SQL> SELECT tq_id
      2       , server_type
      3       , process
      4       , num_rows
      5       , bytes
      6       , waits
      7    FROM v$pq_tqstat
      8   ORDER BY dfo_number
      9       , tq_id
     10       , DECODE(SUBSTR(server_type, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3)
     11       , process
     12  ;
    
         TQ_ID SERVER_TYPE          PROCESS                NUM_ROWS      BYTES      WAITS
    ---------- -------------------- -------------------- ---------- ---------- ----------
             0 Ranger               QC                          182       7604          0
               Producer             P002                        548      11608          4
                                    P003                        452       9691          3
               Consumer             P000                        475      10068          5
                                    P001                        525      11141          6
             1 Producer             P000                        475      10065          0
                                    P001                        525      11114          0
               Consumer             QC                         1000      21179          1
    
    8 개의 행이 선택되었습니다.
    
  • 병렬처리과정의 도식화
  • 병렬쿼리 수행속도가 예상만큼 빠르지 않다면 테이블 큐를 통한 데이터 전송량에 편차가 크지 않은지 확인해 볼 필요가 있다.
(2) 병렬 Group By
  • Hash Group By(10g)
    SQL> set autot traceonly exp
    SQL> SELECT /*+ full(고객) parallel(고객 2) */
      2         고객명, COUNT(*) cnt
      3    FROM 고객
      4   GROUP BY 고객명
      5  ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3413408456
    
    -----------------------------------------------------------------------------------------------------------------
    | 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| 고객      |  1000 | 11000 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    -----------------------------------------------------------------------------------------------------------------
    
  • v$pq_tqstat 쿼리로 테이블 큐를 통한 전송 통계 확인 : Hash Group By 쿼리 기준
    SQL> set autot off
    SQL> -- Hash Group By 쿼리 실행--
    SQL> /
    
    ...
    
    1000 개의 행이 선택되었습니다.
    
    SQL> break on dfo_no on tq_id on server_type
    SQL> SELECT tq_id
      2       , server_type
      3       , process
      4       , num_rows
      5       , bytes
      6       , waits
      7    FROM v$pq_tqstat
      8   ORDER BY dfo_number
      9       , tq_id
     10       , DECODE(SUBSTR(server_type, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3)
     11       , process
     12  ;
    
         TQ_ID SERVER_TYPE          PROCESS                NUM_ROWS      BYTES      WAITS
    ---------- -------------------- -------------------- ---------- ---------- ----------
             0 Producer             P002                        548       6672          3
                                    P003                        452       5520          2
               Consumer             P000                        533       6492         81
                                    P001                        467       5700         80
             1 Producer             P000                        533       8648          4
                                    P001                        467       7568          2
               Consumer             QC                         1000      16216          3
    
    7 개의 행이 선택되었습니다.
    

  • Sort Group By(Order By 구문 추가)
    SQL> SELECT /*+ full(고객) parallel(고객 2) */
      2         고객명, COUNT(*) cnt
      3    FROM 고객
      4   GROUP BY 고객명
      5   ORDER BY 고객명
      6  ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3764717107
    
    -----------------------------------------------------------------------------------------------------------------
    | 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| 고객      |  1000 | 11000 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
    -----------------------------------------------------------------------------------------------------------------
    
  • v$pq_tqstat 쿼리로 테이블 큐를 통한 전송 통계 확인 : Sort Group By 쿼리 기준
    SQL> -- Sort Group By 쿼리 실행--
    SQL> /
    
    ...
    
    1000 개의 행이 선택되었습니다.
    
    SQL>
    SQL> break on dfo_no on tq_id on server_type
    SQL> SELECT tq_id
      2       , server_type
      3       , process
      4       , num_rows
      5       , bytes
      6       , waits
      7    FROM v$pq_tqstat
      8   ORDER BY dfo_number
      9       , tq_id
     10       , DECODE(SUBSTR(server_type, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3)
     11       , process
     12  ;
    
         TQ_ID SERVER_TYPE          PROCESS                NUM_ROWS      BYTES      WAITS
    ---------- -------------------- -------------------- ---------- ---------- ----------
             0 Ranger               QC                          182       4494          1
               Producer             P002                        548       6732          5
                                    P003                        452       5580          3
               Consumer             P000                        475       5823          5
                                    P001                        525       6423          5
             1 Producer             P000                        475       7720          0
                                    P001                        525       8520          0
               Consumer             QC                         1000      16240          1
    
    8 개의 행이 선택되었습니다.
    
  • Sort Group By 의 실행계획 및 테이블 큐를 통한 전송 통계는 Hash Group By 의 결과와 다름.
  • Sort Group By 의 실행계획 및 테이블 큐를 통한 전송 통계는 Sort Order By 의 결과와 같음.
  • 즉, Group By와 Order By의 병렬처리 수행 원리는 같다.
  • 단, Group By의 방식(Hash/Sort)에 따라 분배과정에서 차이가 날 뿐이다.
Group By 가 두번 나타날때의 처리과정
  • Group By 고객등급
    SQL> set autot traceonly exp
    SQL> SELECT /*+ full(고객) parallel(고객 2) */
      2         고객등급, COUNT(*) cnt
      3    FROM 고객
      4   GROUP BY 고객등급
      5  ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 406695692
    
    ------------------------------------------------------------------------------------------------------------------
    | 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| 고객      |  1000 |  3000 |     2   (0)| 00:00:01 |  Q1,00 | PCWP|             |
    ------------------------------------------------------------------------------------------------------------------
    
  • v$pq_tqstat 쿼리로 테이블 큐를 통한 전송 통계 확인
    SQL> set autot off
    SQL> /
    
      고객등급        CNT
    ---------- ----------
             6        100
             5        100
             1        100
             3        100
             4        100
             7        100
             8        100
             2        100
             9        100
            10        100
    
    10 개의 행이 선택되었습니다.
    
    SQL> break on dfo_no on tq_id on server_type
    SQL> SELECT tq_id
      2       , server_type
      3       , process
      4       , num_rows
      5       , bytes
      6       , waits
      7    FROM v$pq_tqstat
      8   ORDER BY dfo_number
      9       , tq_id
     10       , DECODE(SUBSTR(server_type, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3)
     11       , process
     12  ;
    
         TQ_ID SERVER_TYPE          PROCESS                NUM_ROWS      BYTES      WAITS
    ---------- -------------------- -------------------- ---------- ---------- ----------
             0 Producer             P002                         10        208          0
                                    P003                         10        208          0
               Consumer             P000                         10        208          4
                                    P001                         10        208          3
             1 Producer             P000                          5         64          3
                                    P001                          5         64          3
               Consumer             QC                           10        128          3
    
    7 개의 행이 선택되었습니다.
    
  • 선택도(Selectivity) 확인
    SQL> SELECT column_name
      2       , num_distinct
      3       , num_nulls
      4       , 1/num_distinct selectivity
      5       , ROUND(1/num_distinct * t.num_rows, 2) cardinality
      6    FROM user_tables t
      7       , user_tab_columns c
      8   WHERE t.table_name = '고객'
      9     AND c.table_name = t.table_name
     10   ORDER BY column_id
     11  ;
    
    COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS SELECTIVITY CARDINALITY
    ------------------------------ ------------ ---------- ----------- -----------
    고객ID                                 1000          0        .001           1
    고객명                                      1000          0        .001           1
    고객등급                                      10          0          .1         100
    가입일                                      1000          0        .001           1
    

  • 선택도가 높은 항목으로 그룹바이 할 경우 첫번째 서버집합이 두번째 서버집합에 자료를 전송할때 그룹바이 결과를 전송한다면 프로세스간 통신량이 현격하게 줄어들게 되고 병목현상을 줄일 수 있다.
  • 고객등급의 선택도는 0.1 이므로 Group By 결과집합은 원집합의 1/10이다. 즉 통신량이 1/10으로 줄어든다는 의미.
  • 통신량은 줄었지만 두번째 서버집합은 또다시 Group By를 수행해야만 한다.
참고
  • _groupby_nopushdown_cut_ratio 파라미터
    • 0으로 세팅(세션레벨도 가능)하면 선택도가 낮더라도 Group By를 두번 하는 방식으로 동작한다.
    • 기본값 3은 Group By 기준컬럼의 선택도에 따라 동작방식을 결정
  • gby_pushdown, no_gby_pushdown 힌트
    • 11g에서는 힌트로 제어가 가능하다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.