오라클 성능 고도화 원리와 해법 II (2012년)
목록
병렬 처리에 관한 기타 상식 0 0 99,999+

by 구루비스터디 병렬처리 Direct Path Read 병렬쿼리 [2018.04.01]

  1. 병렬 처리 기타 상식
    1. Direct Path Read
    2. 병렬 DML
    3. 병렬 DML 수행 중 Lock
    4. Parallel Index Scan
    5. Index NL Join
    6. 스칼라 서브쿼리
    7. 함수 사용 시 parallel_enable
    8. Rownum + Order by
    9. 기타 주의사항


병렬 처리 기타 상식

Direct Path Read

  • 버퍼 없이 Disk - PGA 로 읽어 일반적 Disk Read 에 비해 비교적 빠르나
  • 사용 빈도가 높아 버퍼에 Cachied 된 데이터 일 경우 오히려 병렬 처리시 느려질 수 있음


병렬 DML

  • Parallel Query(SELECT), Parallel DDL 은 기본 enable 되어 있음
  • DML 작업에서는 Paralle 힌트를 주어도 QC 가 작업 담당
  • 아래와 같은 Altering 작업 해 주어야 병렬 DML 가능

SQL> alter session enable parallel dml;


병렬 DML 수행 중 Lock

  • 테이블 전체에 Exclusive 모드로 Lock 획득하므로 주의


Parallel Index Scan

  • Index Past full scan 이 아닐 경우는 병렬 인덱스 스캔 동작하지 않음
  • partitioned index 일 경우는 파티션 별로 병렬 스캔 가능


Index NL Join

  • 인덱스 스캔 기반의 병렬 NL 조인 가능


Outer full scan --> Inner index scan*




EXPLAIN PLAN FOR
SELECT
/*+ PARALLEL(A 2) ORDERED USE_NL(B) FULL(A) */
*
FROM
T_PART A,
T_NON_PART B
WHERE A.VAL=B.VAL
AND A.ID = 5
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   110K|  2484K| 36938   (1)|       |       |        |      |            |
|   1 |  PX COORDINATOR               |             |       |       |            |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10000    |     1 |    12 |     1   (0)|       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_NON_PART  |     1 |    12 |     1   (0)|       |       |  Q1,00 | PCWP |            |
|   4 |     NESTED LOOPS              |             |   110K|  2484K| 36938   (1)|       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR        |             |   110K|  1188K|    47   (3)|     5 |     5 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL       | T_PART      |   110K|  1188K|    47   (3)|     5 |     5 |  Q1,00 | PCWP |            |
|*  7 |      *INDEX RANGE SCAN*         | I_NON_PART2 |     1 |       |     1   (0)|       |       |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------


2. 병렬 인덱스 스캔 드라이빙*
  • 드라이빙하는 인덱스가 파티션 인덱스여야 하며, Inner 테이블/인덱스, Outer Table 파티션 여부는 상관 없음



EXPLAIN PLAN FOR
SELECT
/*+ ORDERED USE_NL(B) PARALLEL_INDEX(A I_PART 3) */
*
FROM
T_PART A,
T_NON_PART B
WHERE A.VAL=B.VAL
AND A.VAL <= 2000000
;

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   249K|  5845K|   152K  (1)|       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_NON_PART  |     1 |    12 |     1   (0)|       |       |
|   2 |   NESTED LOOPS              |             |   249K|  5845K|   152K  (1)|       |       |
|   3 |    PARTITION LIST ALL       |             |   249K|  2922K|  2834   (4)|     1 |     5 |
|*  4 |     TABLE ACCESS FULL       | T_PART      |   249K|  2922K|  2834   (4)|     1 |     5 |
|*  5 |    INDEX RANGE SCAN         | I_NON_PART2 |     1 |       |     1   (0)|       |       |
------------------------------------------------------------------------------------------------


  • 인덱스 패러럴 안됨


사용 필요한 경우
  • Outer - Inner 가 둘 대 대용량 테이블
  • Outer Table 조건에 대한 선택도가 낮은데(distinct 한 키카 많음) 인덱스가 없음
  • (--> 수행 빈도가 낮아서 인덱스 안만들었을 경우)
  • Inner Table 조인 컬럼에 인덱스 있을 때
  • 수행 빈도가 낮을 때


책 예시
  • Table: 현물채결 t Outer / 현물호가 o Inner - 둘 다 일별 파티션
  • 조건: t.채결일자, t.채결 수량으로 걸러진 건수가 매우 적음
  • 결과: 5건 선택을 위해 현물채결 전체 테이블 스캔은 피할 수 없지만 현물호가 테이블은 NL 로 Index unique scan 가능


스칼라 서브쿼리

  • 일반적으로 QC 가 클라이언트에 전송하면서 수행
  • Order by 절 주면 Parallel Process 가 수행
  • 병렬 처리는 기본적으로 FTS 처럼 대량 처리를 하는데 Row 마다 random access 발생시키면 성능 저하 가능


스칼라 서브쿼리 사용

SELECT
/*+ PARALLEL(A 4) */
ID,
VAL,
(SELECT ID FROM T_NON_PART WHERE VAL=A.VAL)
FROM
T_PART A
WHERE A.VAL <= 2000000


Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.001        0.004          0         18          0          0
Fetch     2001    0.475        0.514          0      62053          0      20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2003    0.476        0.518          0      62071          0      20000

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
  20000   *TABLE ACCESS BY INDEX ROWID T_NON_PART (cr=62053 pr=0 pw=0 time=343712 us)*
  20000    *INDEX RANGE SCAN I_NON_PART2 (cr=42053 pr=0 pw=0 time=247262 us)OF I_NON_PART2 (NONUNIQUE)*
  20000   PX COORDINATOR  (cr=18 pr=0 pw=0 time=93225 us)
      0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0     PX BLOCK ITERATOR PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
      0      TABLE ACCESS FULL T_PART PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)


스칼라 서브쿼리 + ORDER BY

SELECT
/*+ PARALLEL(A 4) */
ID,
VAL,
(SELECT ID FROM T_NON_PART WHERE VAL=A.VAL)
FROM
T_PART A
WHERE A.VAL <= 2000000
*ORDER BY ID*

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.004        0.004          0          1          3          0
Execute      1    0.004        0.123          0         18          0          0
Fetch     2001    0.007        0.595          0          0          0      20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2003    0.015        0.721          0         19          3      20000


Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      0   *TABLE ACCESS BY INDEX ROWID T_NON_PART (cr=0 pr=0 pw=0 time=0 us)*
      0    *(INDEX RANGE SCAN I_NON_PART2 (cr=0 pr=0 pw=0 time=0 us)OF I_NON_PART2 (NONUNIQUE)*
  20000   PX COORDINATOR  (cr=18 pr=0 pw=0 time=700630 us)
      0    PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0        PX BLOCK ITERATOR PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL T_PART PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)


성능개선 방안
  • QC 수행 유도 불가능할 시 가급적 일반 Join 문으로 변환 필요

*Join 변환 + ORDER BY*
SELECT
/*+ ORDERED FULL(A) FULL(B) PARALLEL(A 4) PARALLEL(B 4) */
A.ID,
A.VAL,
B.ID
FROM
T_PART A,
T_NON_PART B
WHERE A.VAL=B.VAL
AND A.VAL <= 2000000
*ORDER BY A.ID*
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.003        0.010          0         21          0          0
Fetch     2001    0.033        2.887          0          0          0      20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2003    0.036        2.897          0         21          0      20000

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
  20000   PX COORDINATOR  (cr=21 pr=0 pw=0 time=4815773 us)
      0    PX SEND QC (ORDER) :TQ10003 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND RANGE :TQ10002 (cr=0 pr=0 pw=0 time=0 us)
      0        HASH JOIN BUFFERED (cr=0 pr=0 pw=0 time=0 us)
      0         PX JOIN FILTER CREATE :BF0000 (cr=0 pr=0 pw=0 time=0 us)
      0          PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0           PX SEND HASH :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0            PX BLOCK ITERATOR PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
      0             TABLE ACCESS FULL T_PART PARTITION: 1 5 (cr=0 pr=0 pw=0 time=0 us)
      0         PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0          PX SEND HASH :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0           PX JOIN FILTER USE :BF0000 (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 T_NON_PART (cr=0 pr=0 pw=0 time=0 us)


병렬 쿼리를 부분집합 처리

SELECT
ID,
VAL,
(SELECT ID FROM T_NON_PART WHERE VAL=A.VAL)
FROM
(SELECT /*+ FULL(A) PARALLEL(A 4) NO_MERGE */ *
 FROM T_PART
 *WHERE VAL <= 2000000*
 ORDER BY ID
 ) A

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch     2001    1.256        1.577          0      74470          0      20000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2003    1.256        1.577          0      74470          0      20000

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: LIM (ID=58)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
  20000   TABLE ACCESS BY INDEX ROWID T_NON_PART (cr=62053 pr=0 pw=0 time=331868 us)
  20000    INDEX RANGE SCAN I_NON_PART2 (cr=42053 pr=0 pw=0 time=239675 us)OF I_NON_PART2 (NONUNIQUE)
  20000   PARTITION LIST ALL PARTITION: 1 5 (cr=12417 pr=0 pw=0 time=317292 us)
  20000    VIEW  (cr=12417 pr=0 pw=0 time=1119944 us)
  20000     SORT ORDER BY (cr=12417 pr=0 pw=0 time=1119933 us)
  20000      TABLE ACCESS FULL T_PART PARTITION: 1 5 (cr=12417 pr=0 pw=0 time=346 us)
  • 테스트 시 그냥 처음 쿼리(스칼라 서브 쿼리)가 제일 빠름


함수 사용 시 parallel_enable

  • 병렬 처리 시에는 처음 수행한 Session(QC) 외에 별도의 Parallel Session 생성하여 작업 처리
  • Session 레벨에서 관리되는 값 사용 시 Serial 과 Parallel 차이에 따른 결과 값 생길 수 있어 강제로 Serial 하게 변환 됨
  • parallel_enabld 은 사용자가 강제로 Parallel 로 동작하도록 하는 키워드
  • 병렬 처리로 인한 결과값에 대해서는 사용자의 책임


SQL> SELECT /*+ PARALLEL(A 4) */ CALL_COUNTER.FUNCALLCOUNTER , VAL FROM T_NON_PART A
  2  WHERE A.VAL < 10
ORDER BY 1
;  3    4

FUNCALLCOUNTER VAL
-------------- --------------------------------------------------
             1 1
             2 6
             3 2
             4 7
             5 3
             6 8
             7 4
             8 9
             9 5

9 rows selected.

SQL> SELECT /*+ PARALLEL(A 4) */ CALL_COUNTER.FUNCALLCOUNTER2 , VAL FROM T_NON_PART A
WHERE A.VAL < 10
ORDER BY 1
;  2    3    4

FUNCALLCOUNTER2 VAL
--------------- --------------------------------------------------
              1 3
              1 1
              1 2
              1 4
              2 8
              2 6
              2 9
              2 7
              3 5

9 rows selected.



SQL> EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(A 4) */ FUNCALLCOUNTER , VAL FROM T_NON_PART A;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

------------------------------------------------------------------------
| Id  | Operation            | Name       |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |        |      |            |
|   1 |  PX COORDINATOR      |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000   |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |            |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| T_NON_PART |  Q1,00 | PCWP |            |
------------------------------------------------------------------------


SQL> EXPLAIN PLAN FOR
CREATE TABLE T_PERMANENT3
PARALLEL 4
AS
SELECT /*+ PARALLEL(A 4) */ CALL_COUNTER.FUNCALLCOUNTER , VAL FROM T_NON_PART A;


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT       |              |        |      |            |
|   1 |  *PX COORDINATOR FORCED SERIAL*|              |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000     |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT            | T_PERMANENT3 |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR        |              |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL       | T_NON_PART   |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------
  • 그냥 select 시에는 강제 Serial 변환 안됨, CTAS 작업 할때만 변환


Rownum + Order by

  • Table Scan 은 Parallel Server 가, Ordering 은 QC 가 수행함
ROWNUM




SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ PARALLEL(A 4) */ ROWNUM AS RNUM1 FROM T_NON_PART A WHERE VAL < 10000000
  3  ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |   245K|  2401K|   787   (4)|        |      |            |
|   1 |  COUNT                |            |       |       |            |        |      |            |
|   2 |   PX COORDINATOR      |            |       |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000   |   245K|  2401K|   787   (4)|  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |            |   245K|  2401K|   787   (4)|  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| T_NON_PART |   245K|  2401K|   787   (4)|  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------


ROWNUM + ORDER BY

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ PARALLEL(A 4) */ ROWNUM AS RNUM1 FROM T_NON_PART A WHERE VAL < 10000000
     ORDER BY VAL
  3  ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |   245K|  2401K|       |  1046   (4)|        |      |            |
|   1 |  *SORT ORDER BY*         |            |   245K|  2401K|  7736K|  1046   (4)|        |      |            |
|   2 |   COUNT                |            |       |       |       |            |        |      |            |
|   3 |    PX COORDINATOR      |            |       |       |       |            |        |      |            |
|   4 |     PX SEND QC (RANDOM)| :TQ10000   |   245K|  2401K|       |   787   (4)|  Q1,00 | P->S | QC (RAND)  |
|   5 |      PX BLOCK ITERATOR |            |   245K|  2401K|       |   787   (4)|  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T_NON_PART |   245K|  2401K|       |   787   (4)|  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------


ORDER BY

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ PARALLEL(A 4) */ ID FROM T_NON_PART A WHERE VAL < 10000000
     ORDER BY VAL
  3  ;

----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |   245K|  2882K|       |  1082   (4)|        |      |            |
|   1 |  PX COORDINATOR         |            |       |       |       |            |        |      |            |
|   2 |   PX SEND QC (ORDER)    | :TQ10001   |   245K|  2882K|       |  1082   (4)|  Q1,01 | P->S | QC (ORDER) |
|   3 |    *SORT ORDER BY*        |            |   245K|  2882K|  9656K|  1082   (4)|  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |            |   245K|  2882K|       |   787   (4)|  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE      | :TQ10000   |   245K|  2882K|       |   787   (4)|  Q1,00 | P->P | RANGE      |
|   6 |       PX BLOCK ITERATOR |            |   245K|  2882K|       |   787   (4)|  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL| T_NON_PART |   245K|  2882K|       |   787   (4)|  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------


기타 주의사항

  • workarea_size_policy = manual 시 sort_area_size 가 모든 병렬 서버에게 적용
  • 사이즈 계산 잘못하면 OS 페이징 등 유발 가능


  • parallel degree 지정 안하면 num_cpu * parallel_threads_per_cpu 만큼 할당됨
  • adaptive multiuser 사용하는게 아니면 반드시 degree 지정
  • adaptive multiuser 란?
  • 쿼리 안에 Parallel degree 는 가급적 동일하게 맞추자
  • 안그러면 대충 알아서 지정 됨
  • Optimizer 에 의해 인덱스 스캔이 선택 될 경우 Parallel 무시됨
  • parallel 힌트 사용 시 FTS 힌트 함께 사용
  • Optimizer 에 의해 Full table scan 선택 될 경우 Parallel_index 무시됨
  • parallel_index 힌트 사용 시 index / index_ffs 힌트 사용
  • 부분범위 처리 방식에는 일부 Fetch 하고 나서도 Parallel session 은 대기상태로 떠있음
  • Resource 낭비 상황 가능하니 주의
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3313

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입