"병렬 쿼리 튜닝"의 두 판 사이의 차이

1.1.2 생산자 VS 소비자[편집]

1.1.3 DOP 많을수록 좋을까?[편집]

1.1.4  병렬퀴리튜닝의 관점은? 병렬의 갯수인가? 분산의 정도인가?[편집]

1.1.5 병렬 PLAN 해석하는 방법[편집]

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |    14 |  1638 |     7  (29)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)       | :TQ10002 |    14 |  1638 |     7  (29)| 00:00:01 |  Q1,02 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY           |          |    14 |  1638 |     7  (29)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE             |          |    14 |  1638 |     6  (17)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANGE         | :TQ10001 |    14 |  1638 |     6  (17)| 00:00:01 |  Q1,01 | P->P | RANGE      |
|*  6 |       HASH JOIN            |          |    14 |  1638 |     6  (17)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        BUFFER SORT         |          |       |       |            |          |  Q1,01 | PCWC |            |
|   8 |         PX RECEIVE         |          |     4 |   120 |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   9 |          PX SEND BROADCAST | :TQ10000 |     4 |   120 |     3   (0)| 00:00:01 |        | S->P | BROADCAST  |
|  10 |           TABLE ACCESS FULL| DEPT     |     4 |   120 |     3   (0)| 00:00:01 |        |      |            |
|  11 |        PX BLOCK ITERATOR   |          |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  12 |         TABLE ACCESS FULL  | EMP      |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

1.2 병렬처리 올바른 사용법은?[편집]

SQL> alter session enable parallel dml;

/*+ ENABLE_PARALLEL_DML */

-- 세션에서 병렬 쿼리 작업 절차
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET HASH_AREA_SIZE          = 1024000000;
ALTER SESSION SET SORT_AREA_SIZE          = 2147483647;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE = 2147483647;
ALTER SESSION SET WORKAREA_SIZE_POLICY    = MANUAL; 
-- 사용자가 지정한 sort_area_size가 모든  병렬 서버에게 적용. 
-- sort_area_size를 크게 설정한 상태에서 지나치게 큰 병렬도를 지정하면
-- OS 레벨에서 페이징이 발생하고 심할 경우 시스템을 마비시킬 수 있음.
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256; -- AIX는 최대 512 

ALTER SESSION SET "_sort_multiblock_read_count"     = 256;
ALTER SESSION SET "_db_file_optimizer_read_count"   = 256;
ALTER SESSION SET "_db_file_exec_read_count"        = 256;
ALTER SESSION SET "_serial_direct_read"             = TRUE;

SELECT DECODE(A.QCSERIAL#, NULL, 'PAREMT', 'CHILD') ST_LVL,
       A.SERVER_SET "SET",
       A.SID,
       A.SERIAL#,
       STATUS,
       EVENT,
       WAIT_CLASS
FROM   V$PX_SESSION   A,
       V$SESSION      B
WHERE  A.SID     = B.SID
AND    A.SERIAL# = B.SERIAL#
ORDER  BY A.QCSID,
          ST_LVL DESC,
          A.SERVER_GROUP,
          A.SERVER_SET
;

SELECT *
  FROM V$PARAMETER
 WHERE NAME LIKE '%parallel%' -- 'parallel_threads_per_cpu' -- 2

SELECT *
  FROM 
(SELECT /*+ PQEXPAND_TABLE(A2) */
        AZ.SRV_INSTLID
      , A2.CYKIM_TIMPL_ID
      , A2.CYKIM_WRT_YMD
      , A2.CYKIM_WRT_DGR
      , MIN(A2.DCRY_LNNO) AS DCRY_LNNO 
  FROM TB_BIG_PART A2 -- 파티션테이블 
 WHERE SUBSTR(A2.CYKIM_TMPL_ID, 0, 2) = 'am'
 GROUP BY A2.SRV_INST_ID
        , A2.CYKIM_TMP_ID 
        , A2.CYKIM_WRT_YMD
        , A2.CYKIM_WRT_DGR
) A
, TB_CYKIM_SPRV B
.....

SELECT *
  FROM 
(SELECT /*+ NO_PQEXPAND_TABLE(A2) */
        AZ.SRV_INSTLID
      , A2.CYKIM_TIMPL_ID
      , A2.CYKIM_WRT_YMD
      , A2.CYKIM_WRT_DGR
      , MIN(A2.DCRY_LNNO) AS DCRY_LNNO 
  FROM TB_BIG_PART A2 -- 파티션테이블의  한곳의 파티션에 치중됨, 예를 들어 2000년 이전 데이터는 PT_2001파티션에 1억건 존재 , 나머지는 1백만건 
 WHERE SUBSTR(A2.CYKIM_TMPL_ID, 0, 2) = 'am'
 GROUP BY A2.SRV_INST_ID
        , A2.CYKIM_TMP_ID 
        , A2.CYKIM_WRT_YMD
        , A2.CYKIM_WRT_DGR
) A
, TB_CYKIM_SPRV B
.....

/*+ PQ_FILTER(SERIAL | NONE | HASH | RANDOM) */ -- 4개중 택1

SELECT /* + PARALLEL(2) PQ_FILTER(HASH) */
       *
  FROM T1 A
 WHERE EXISTS (SELECT /*+ NO_UNNEST */
                      1
                 FROM T2 B
                WHERE B.C1 = A.C1)

SELECT /*+ PARALLEL(2) PQ_FILTER(NONE) 
           ORDER_SUBQ(@MAIN @SUB2 @SUB1) QB_NAME(MAIN) */
       * 
 FROM T1 A
WHERE EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB1) */
                      1
                 FROM T2 B
                WHERE B.C1 = A.C1)
  AND EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB2) */
                      1
                 FROM T3 C
                WHERE C.C1 = A.C1)

FILTER
  PX COORDINATOR
    PX SEND QC(RANDOM)
      PX BLOCK ITERATOR
        TABLE ACCESS FULL T1
  INDEX RANGE SCAN IX_T2

PX COORDINATOR
  PX SEND QC(RANDOM)
   FILTER
     PX BLOCK ITERATOR.
        TABLE ACCESS FULL 11
     INDEX RANGE SCAN IX_12

PX COORDINATOR
  PX SEND QC(RANDOM)
    FILTER
      PX BLOCK ITERATOR
         TABLE ACCESS FULL T1
    INDEX RANGE SCAN X_13
    INDEX RANGE SCAN IX_12

PX COORDINATOR
  PX SEND QC(RANDOM)
    BUFFER SORT
      FILTER
        PX RECEIVE
           PX SEND HASH
              PX BLOCK ITERATOR
                TABLE ACCESS FULL T1
           INDEX RANGE SCAN IX_T2

PX COORDINATOR
  PX SEND QC(RANDOM)
    BUFFER SORT
      FILTER
        PX RECEIVE
           PX SEND ROUND-ROBIN
              PX BLOCK ITERATOR 
                TABLE ACCESS FULL T1
              INDEX RANGE SCAN IX_T2

alter system set parallel_execution_message_size = 32768; -- 16384 -- 기본