행위

병렬 쿼리 튜닝

DB CAFE

notifications_active 데이터베이스 전문기업 안내


목차

1 병렬 쿼리 튜닝[편집]

1.1 병렬처리 핵심은?[편집]

1.1.1 그래뉼[편집]

android The basic unit of work in parallelism is a called a granule.

Oracle Database divides the operation executed in parallel (for example, a table scan, table update, or index creation) into granules.


  1. 병렬로 처리할때 일의 최소 단위
  2. 병렬 서버는 한번에 하나의 그래뉼씩 처리 함
  3. 그래뉼 갯수와 크기는 병렬도와 관련되고 분산처리에 영향을 미침
  4. 블록 그래뉼 과 파티션 그래뉼로 나뉨
  5. 병렬 쿼리 granule
1.1.1.1 블록 그래뉼[편집]
  1. PX BLOCK ITERATOR 라고 표시
  2. QC는 테이블로부터 읽어야할 범위의 블록 GRANULE로서 각 병렬 서버에게 할당
  3. 파티션 여부,파티션 갯수 와 무관하게 병렬도 지정이 가능
-------------------------------------------------------------------------------------------------
|Id|      Operation          |  Name  |Rows|Bytes|Cost%CPU|  Time  |Pst|Pst|  TQ |INOUT|PQDistri|
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT         |        |  17| 153 |565(100)|00:00:07|   |   |     |     |        |
| 1| PX COORDINATOR          |        |    |     |        |        |   |   |     |     |        |
| 2|  PX SEND QC(RANDOM)     |:TQ10001|  17| 153 |565(100)|00:00:07|   |   |Q1,01|P->S |QC(RAND)|
| 3|   HASH GROUP BY         |        |  17| 153 |565(100)|00:00:07|   |   |Q1,01|PCWP |        |
| 4|    PX RECEIVE           |        |  17| 153 |565(100)|00:00:07|   |   |Q1,01|PCWP |        |
| 5|     PX SEND HASH        |:TQ10000|  17| 153 |565(100)|00:00:07|   |   |Q1,00|P->P | HASH   |
| 6|      HASH GROUP BY      |        |  17| 153 |565(100)|00:00:07|   |   |Q1,00|PCWP |        |
==========> 블록 이터레이터로 표시됨 
| 7|       PX BLOCK ITERATOR |        | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWC |        |
|*8|        TABLE ACCESS FULL|  SALES | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWP |        |
-------------------------------------------------------------------------------------------------
1.1.1.2 파티션 그래뉼[편집]
  1. PX PARTITION RANGE ALL
    1. 전체 파티션을 읽을때 표시
  2. PX PARTITION RANGE ITERATOR 라고 표시
    1. 일부 파티션만 읽을 때 표시
  3. 사용되는 시기
    1. Partition-Wise 조인 시
    2. 파티션 인덱스를 병렬로 스캔할 시
    3. 파티션 인덱스를 병렬로 갱신할 때
    4. 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때
  4. 병렬도는 파티션 갯수 이하로만 지정 할수 있음(튜닝 요소)
    1. 1개 파티션을 2개의 프로세스가 함께 처리 할수 없음
    2. 예시) WHERE 조건에 파티션 컬럼이 1개만 타도록 제한된 경우 아래 예시 참조
  5. 병렬 서버가 한 파티션 처리를 끝마치면 다른 파티션을 할당 받아서 진행 함(병렬도가 파티션 갯수 보다 적을때)
---------------------------------------------------------------------------------------------------
|Id|      Operation                |  Name  |Rows|Byte|Cost%CPU|  Time  |Ps|Ps|  TQ |INOU|PQDistri|
---------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT               |        |  17| 153|   2(50)|00:00:01|  |  |     |    |        |
| 1| PX COORDINATOR                |        |    |    |        |        |  |  |     |    |        |
| 2|  PX SEND QC(RANDOM)           |:TQ10001|  17| 153|   2(50)|00:00:01|  |  |Q1,01|P->S|QC(RAND)|
| 3|   HASH GROUP BY               |        |  17| 153|   2(50)|00:00:01|  |  |Q1,01|PCWP|        |
| 4|    PX RECEIVE                 |        |  26| 234|    1(0)|00:00:01|  |  |Q1,01|PCWP|        |
| 5|     PX SEND HASH              |:TQ10000|  26| 234|    1(0)|00:00:01|  |  |Q1,00|P->P| HASH   |
==========> 파티션 RANGE ... 로 표시됨 
| 6|      PX PARTITION RANGE ALL   |        |  26| 234|    1(0)|00:00:01|  |  |Q1,00|PCWP|        |
| 7|       TABLEACCESSLOCAL INDEX ROWID|SALES| 26| 234|    1(0)|00:00:01| 1|16|Q1,00|PCWC|        |
|*8|        INDEX RANGE SCAN       |SALES_CUST|26|    |    1(0)|00:00:01| 1|16|Q1,00|PCWP|        |
---------------------------------------------------------------------------------------------------

1.1.2 파티션-와이즈 조인 Partition-wise Join[편집]

  • 기본적인 원리는 커다란 하나의 조인을 분할하여 여러개의 작은 조각으로 나누는것
  • 머지조인, 해시조인시 적용하는 최적화 기법
  • 파티션 와이즈 조인은 파티션 테이블이 필수 임
  • 조인처리에 사용되는 cpu,memory,네트워크 리소스를 줄이는 방법임
  • '-wise' 의미 [1]
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-9.42.23.png
  • 조인 과 병렬도 성능 비교
1.1.2.1 풀 파티션-와이즈 조인[편집]
  • Full Partition-Wise Join (완전 동등하게 조인)
  • 동등하게 파티션된 2개 테이블을 조인 함
  • 대규모 조인이 예상되는 테이블은 동등한 파티션으로 설계할것을 모델링 단계에서 고려할것
    • (주의사항) 리스트 파티션은 동일 갯수,동일 순서가 맞도록 파티션 되어야 함
  • 서브파티션과도 조인 가능
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-9.44.35.png


  • Hash Join 앞에 Partition Hash All 발생
SELECT *
FROM tIp, t2p
WHERE tIp.id = t2p.id;
---------------------------------------
|Id|	Operation	       | Name |
---------------------------------------
|0|	SELECT STATEMENT	
======>> 
|1|	 PARTITION HASH ALL	
=======>>> 
|2|	  HASH JOIN	
|3|	   TABLE ACCESS FULL   | T1P  |	
|4|	   TABLE ACCESS FULL   | T2P  |	
---------------------------------------
2- access(“TIP PID:T2P"."TD"



1.1.2.2 부분 파티션-와이즈 조인[편집]
  • Patial Partition-Wise Join (부분)
  • 병렬로만 수행이 가능함
  • 한쪽 테이블만 조인키 기준으로 파티션 된 경우임
  • 부분 파티션 조인시 일반 조인보다는 빠르지만 큰 성능 향상을 기대하기는 어렵다.(오히려 성능이 나빠질수도 있다)
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-10.03.26.png


  1. 부분 파티션 조인은 실행계획에서 PX_SEND 오퍼레이션이 Partition (Key) 로 나타남
SELECT /*+ ordered use hash(t2p) pq_distribute(t2p none partition) */* 
  FROM t1p, t2p
 WHERE t1p. id = t2p.id

------------------------------------------------------------
|	Id |	Operation                       | Name
------------------------------------------------------------
|	0  | 	SELECT STATEMENT                |
|	1  | 	 PX COORDINATOR                 |
|	2  |	  PX SEND QC (RANDOM)           | :TQ10001
|*	3  |	   HASH JOIN BUFFERED           |
|	4  |	    PX PARTITION HASH ALL       |
|	5  |	     TABLE ACCESS FULL          | T1P
|	6  |	    PX RECEIVE                  |
==================>>>
|	7  |	     PX SEND PARTITION (KEY)    | :T010000
|	8  |	      PX BLOCK ITERATOR         |
|	9  |	       TABLE ACCESS FULL        | T2P
-------------------------------------------------------------

3 - access("T1P"_"ID"="T2P","ID"



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

?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2Fy9Qa0%2FbtqytV7dIAq%2FK95i8dY3X43nzrZhvhkPrk%2Fimg.png

  • intra vs. inter
    intra : 내부의(within)
    inter : ~와 연관된(among,between)


  1. intra-operation parallelism
    - 서로 배타적인 범위를 독립적으로 동시에 수행하는것.
    - 할당받은 작업을 하나씩 분배,정렬 수행
    - 프로세스간 통신이 아님
  2. inter-operation parallelism
    - 다른 서버 집합에게 분배 하거나,정렬결과를 QC에게 전송
    - 프로세스간 통신이 발생함
  3. 실행 계획상의 생산자 소비자 식별
    1. 생산자 (Producer) : PX SEND 로 표시됨
    2. 소비자 (Consumer) : PX RECEiVE 로 표시됨
    3. 생산자에서 소비자로 데이터 재분배시에는 TQ:0000 (테이블 큐)로 표시




1) S -> P : QC가 읽은 데이터를 테이블큐를 통해서 병렬 프로세스로 전송 하는것, 직렬(serial) 오퍼레이션

( Paralell_From_Serial )

2) P -> P : 데이터 재분배, 병렬도가 2배가 됨. Inter Operation Parallelism , 병렬 오퍼레이션

( Parallel_To_Parallel )

3) P -> S : 각 병렬서버가 처리한 데이터를 QC에게 전송 하는 것, 병렬 오퍼레이션

( Parallel_To_Serial )



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

1.1.4.1 DOP 32 => DOP 16 으로 줄여서 성능개선[편집]
  • 튜닝 전
MERGE /*+ENABLE_PARALLEL_DML PARALLEL(32) FULL(T) USE_HASH(T)*/
INTO TB_TEST1 T
USING (
SELECT PLAN_ID
, GRFNLID
, IDMB_ID
, SBWDC_ID
.... 생략 ....


  • 튜닝 후
    - 분배 방식 및 ACCESS 방식 변경을 위한 힌트 추가.
  1. 약 1천 9백만건 이상 MERGE 되는 대량 DML 문으로 원본도 병렬 처리가 잘되고 있어 크게 개선 될 포인트는 없음.
  2. parallel degree는 16으로 내렸으며 약 583초(8분23초) 수행.(변경전 700초)
  3. SQL 변경
MERGE /*+ ENABLE_PARALLEL_DML PARALLEL(T 16) LEADING(S) FULL(T) USE_HASH(T) PQ_DISTRIBUTE(T HASH HASH) */
 INTO TB_TEST1 T
USING (
SELECT PLAN_ID
     , GRFM_ID
     , IDM8_ID
.... 생략 ....

1.1.5  병렬퀴리튜닝의 관점은?[편집]

각 병렬 프로세스들이 독립적으로 일 할 수 있도록 데이터 적절하게 분배



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

  • XPLAN 조회 ( ‘+PARTITION +PARALLEL +OUTLINE' )
-- 병렬 쿼리 확인 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST ADAPTIVE PARTITION PARALLEL OUTLINE'));
--------------------------------------------------------------------------------------------------------------------
| 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            |          |       |       |            |          |        |      |            |

----------[3] 서버집합(Q1,02)은 전송받은 레코드를 정렬하고 나서 QC에게 전송한다.
|   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 |            |

-------------[2] 1번째 서버집합(Q1,01)은 EMP테이블을 병렬로 읽으면서 QC에서 받은 DEPT테이블과 조인하고,조인에 성공한 레코드는 2번째 서버집합(Q1,02)에게 전송한다.

-------------[생산자]
|   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  |

------------------[1] QC가 DEPT 읽어서 1번째 서버집합(Q1,01)에게 전송 한다 
|  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. S->P 직렬 PARALLEL_FROM_SERIAL QC가 읽은 데이터를 테이블 큐를 통해 병렬서버 프로세스에게 전송
  2. P->S 병렬 PARALLEL_TO_SERIAL 각 병렬서버 프로세스가 처리한 데이터를 QC에게 전송
  3. P->P 병렬 PARALLEL_TO_PARALLEL 데이터를 재분배(redistribution)하는 오퍼레이션 ,데이터를 정렬 또는 그룹핑하거나 조인을 위해 동적으로 파티셔닝할 때 사용
  4. PCWP 병렬 PARALLEL_COMBINED_WITH_PARENT 한 서버집합이 현재스텝과 부모스텝을 모두 처리
  5. PCWC 병렬 PARALLEL_COMBINED_WITH_CHILD 한 서버집합이 현재스텝과 자식스텝을 모두 처리
  6. [공백] 직렬 SERIAL 공백인 경우 SERIAL 방식으로 처리



  1. RANGE
    1. Order By /Sort Group By 를 병렬로 처리할 때 사용
    2. 정렬작업을 맡은 두번째 서버집합의 프로세스마다 처리범위를 지정하고 나서
    3. 데이터를 읽는 첫번째 서버집합이 정렬키값에 따라 정해진 범위에 해당하는 두번째 프로세스에게 분배하는 방식
    4. QC는 작업범위를 할당하며, 정렬작업에는 참여하지 않는다.
    5. 정렬결과를 순서대로 받아서 사용자에게 전송하는 역할만 담당
  2. HASH
    1. 조인 / Hash Group By 를 병렬로 처리할 때 사용
    2. 조인 키나 Group By 키값을 해시함수에 적용하여 리턴되는 값에 따라 데이터를 분배
    3. P->P 뿐만 아니라 S->P 방식으로 이루어 질수도 있다.
  3. BROADCAST
    1. QC 또는 첫번째 서버집합의 프로세스들이 각각 읽은 데이터를 두번째 서버집합의 "모든" 병렬프로세스에게 전송하는 방식
    2. 병렬 조인에서 크기가 매우 작은 테이블이 있을 때 사용되며 P->P 뿐만 아니라 S->P 방식으로 이루어 진다.
    3. 작은 테이블은 병렬로 읽지 않을 때가 많으므로 오히려 S->P가 일반적임
  4. KEY
    1. 특정 컬럼(들)을 기준으로 테이블 또는 인덱스를 파티셔닝할때 사용하는 분배 방식
    2. 실행계획에는 'PARTITION (KEY)'로 표시된다.(줄여서 'PART (KEY)'). 부분 파티션 와이즈 조인
    3. CTAS 문으로 파티션 테이블 생성시
    4. 병렬로 글로벌 파티션 인덱스 생성시
  5. ROUND-ROBIN
    1. 파티션키, 정렬키, 해시함수에 의존하지 않고 반대편 정렬 서버에 무작위로 데이터 분배
    2. 골고루 분배되도록 ROUND-ROBIN 방식 사용



1.1.6.1 SQL 플랜상 튜닝 검토 사항[편집]

android # broadcast 는 소량테이블에 적합(임시로 생성한 테이블에 통계정보가 있는지 확인필요)

  1. hash 는 대량 테이블에 적합
  2. S->P 는 튜닝 대상임. P->P로 바꿀수 있는 방법을 검토.sql수정도 좋다.
  3. round-robin 은 튜닝 대상임
  4. merge 나 insert ,delete문에만 주로 Parallel 힌트 사용. 하위 select 문에서는 가급적 지양 , 1:1 관계를 지향한다
  5. part key 플랜도 튜닝 대상. pq_disiribute(A hash hash) 힌트로 변경 검토


  • 오라클은 내부적 으로 어떤 힌트를 사용하고 있는지 볼까 ?
    • OUTLINE
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.09.25.png

1.2 슬기로운 병렬처리 사용법[편집]

  1. 병렬도를 같게 지정하는 것이 바람직 함.
  2. 테이블별 개별 힌트- PARALLEL(A 8) 보다 글로벌 힌트- PARALLEL(8)로 적용

1.2.1 병렬 힌트를 어디에 어떻게 써야 하나?[편집]

1.2.1.1 SELECT 절 병렬 힌트[편집]
  1. 조인 순서 지정 - leading() , ordered 힌트사용
  2. 조인 방식 지정 - use_nl(),use_hash(),use_merge()
    * hash 조인일때 - SWAP_JOIN INPUTS() , NO_SWAP_JOIN INPUTS() - Build Input 지정
  3. FULL() 힌트 사용 - 옵티마이저가 인덱스 스캔을 선택하면 parallel 힌트가 무시됨
  4. PO_DISTRIBUTE() 분산 방식 지정
SELECT 
-- 튜닝 전
/* FULL(BA) FULL(BB) FULL(BC) FULL(BD)
PARALLEL(BA 16) 
PARALLEL(BB 16) 
PARALLEL(BC 16) 
PARALLEL(BD 16) */

-- 튜닝 후 
/*+ PARALLEL(16) 
LEADING(BB) USE HASH(BA BB BC BD )
FULL(BA) PO_DISTRIBUTE(BA HASH HASH) NO_SWAP_JOIN INPUTS(BA)
FULL(BB) PO_DISTRIBUTE(BB HASH HASH) SWAP_ JOIN_INPUTS(BB)
FULL(BC) PQ_DISTRIBUTE(BC HASH HASH) NO_SWAP_JOIN_INPUTS(BC)
FULL(BD) PQ_DISTRIBUTE(BD HASH HASH) NO_SWAP_JOIN_INPUTS (BD)
*/
....
 FROM TB_MB.  BA  -- 20G
    , TB_FM   BB  -- 5G
    , TB_FMBR BC  -- 8G
    , TB_IDM  BD  -- 13G
WHERE 1 = 1
AND BA.Q_ID IN (
SELECT DISTINCT O_ID
......
1.2.1.2 INSERT 절 병렬 힌트 위치[편집]
INSERT /*+ PARALLEL (4) ENABLE_PARALLEL_DML */ -- PQ_DISTRIBUTE(T NONE)
  INTO TB_WM_DTLS T
1.2.1.3 INSERT 절 병렬 힌트 적용 안되는 경우[편집]
-- insert 앞에 힌트 
/*+ PARALLEL (4) ENABLE_PARALLEL_DML*/ -- PQ_DISTRIBUTE(T NONE)
INSERT INTO
TB_XM_DTLS T
.... 

-- into 뒤에 힌트
INSERT INTO /*+ PARALLEL (4) ENABLE_PARALLEL_DML */ -- PQ_DISTRIBUTE(T NONE)
TB_XM_DTLS T
....
1.2.1.4 UPDATE/ DELETE / MERGE 힌트 위치[편집]
UPDATE /*+ PARALLEL(4) ENABLE_PARALLEL_DML */ 테이블명 ~
DELETE /*+ PARALLEL(4) ENABLE_PARALLEL_DML */ FROM 테이블 ~
MERGE  /*+ PARALLEL(4) ENABLE_PARALLEL_DML */ INTO 테이블명 ~



1.2.2 DDL 병렬 처리[편집]

  1. CREATE TABLE [테이블명] (.......) PARALLEL ( DEGREE [병렬범위] ) ;
  2. CREATE INDEX [인덱스명] PARALLEL ( DEGREE [병렬범위] ) ;
  3. ALTER TABLE [테이블명] PARALLEL ( 병렬범위 ) ;
  4. ALTER INDEX [테이블명] PARALLEL ( 병렬범위 ) ;


SQL> alter session enable parallel ddl;
1.2.2.1 CREATE 예시[편집]
-- 테이블 생성 
CREATE TABLE name ( column1 [data-type], ... )
Parallel 8;

-- 인덱스 생성 DOP 32 
CREATE INDEX CYKIM.IX_TBL_X01
          ON (MY_TABLE)
TABLESPACE TS_CY PARALLEL 32 NOLOGGING;
ALTER INDEX CYKIM.IX_TBL_X01 NOPARALLEL LOGGING;
1.2.2.2 ALTER 예시[편집]
ALTER TABLE name Parallel 8 ;

1.2.3 DML 병렬 처리[편집]

  • DML 작업에서는 Paralle 힌트를 주어도 QC 가 작업 담당
  • 병렬 DML 가능하도록 처리
  • 12c 이전 에는 세션에 적용
SQL> alter session enable parallel dml;
  • 12c 부터는 힌트로 적용
/*+ ENABLE_PARALLEL_DML */
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.25.27.png

1.2.3.1 DML에서 CONVENTIONAL PATH 와 DIRECT PATH 차이점[편집]
  • 버퍼캐시에 적재, Default 방식
  • Storage -> SGA -> PGA
  • 처리 속도 느림
  • 로우레벨 락(Row-level Lock) 발생
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.19.58.png



  • 병렬방식으로 Full Scan 할 때는 버퍼 캐시를 거치지 않고 곧바로 PGA 영역으로 읽어들이는 Direct Path Read 방식을 사용
  • Storage -> PGA
  • 테이블 레벨 락(Table-level lock) 발생
  • /*+ append */
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.22.43.png



1.2.3.2 DML 병렬 처리시 주의사항[편집]

android *DML 병렬 처리시 주의사항

  1. 테이블 전체에 Exclusive 모드로 Lock 획득하므로 주의
  2. 커밋/롤백을 해야 SELECT 가능 함.
  • DML 처리시 플랜에서 항상 QC 아래에 INSERT/UPDATE/DELETE 가 존재 해야 한다. (QC가 아닌 병렬서버에서 처리 토록 해야 한다.)



1.2.4 동일 테이블에 병렬로 입력 하는 방법[편집]

  1. Oracle에서 파티션 테이블을 이용하여 데이터를 병렬로 입력하는 방법
  2. 파티션 테이블은 데이터를 물리적 또는 논리적 파티션으로 분할한 테이블입니다.
  3. 아래 예시에서 INSERT INTO ... SELECT 문을 사용하여 병렬로 데이터를 입력하는 방법을 보여줍니다.
-- 파티션 테이블 생성
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    product_id NUMBER,
    quantity NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_202301 VALUES LESS THAN (TO_DATE('2023-02-01', 'YYYY-MM-DD')),
    PARTITION sales_202302 VALUES LESS THAN (TO_DATE('2023-03-01', 'YYYY-MM-DD'))
);

-- 병렬로 데이터 입력
-- 세션 1에서 데이터 입력 
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(sales, 4) */ INTO sales
SELECT sale_id, TO_DATE('2023-01-15', 'YYYY-MM-DD'), product_id, quantity FROM sales_data_january;
commit; -- 커밋을 해야 다른세션에서도 커밋이 가능하다 


-- 세션 2에서 데이터 입력 
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(sales, 4) */ INTO sales
SELECT sale_id, TO_DATE('2023-02-15', 'YYYY-MM-DD'), product_id, quantity FROM sales_data_february;
commit;
  • ORA-12838 : 병렬로 수정한 후 객체를 읽거나 수정할 수 없습니다. 오류 발생시 커밋 여부를 확인할것. 같은 세션에서도 커밋하지 않으면 조회도 불가능하다.


  1. ALTER SESSION ENABLE PARALLEL DML 문을 사용하여 세션을 병렬 DML 모드로 변경합니다.
  2. INSERT 문에 /*+ APPEND PARALLEL(table_name, degree_of_parallelism) */ 힌트를 추가하여 병렬로 데이터를 입력하도록 지시합니다.
    table_name은 파티션 테이블의 이름이며 degree_of_parallelism은 병렬로 실행될 프로세스 수를 나타냅니다.
  3. 각 세션은 다른 데이터 범위(월)에 대한 입력을 수행합니다. 이렇게 하면 데이터가 서로 충돌하지 않고 병렬로 처리됩니다.
  • 반드시 데이터 입력 작업을 테스트하고 성능 튜닝을 위해 적절한 병렬 처리 수를 설정해야 합니다.

1.3 병렬 처리 진행 사항 모니터링[편집]

1.3.1 진행 사항 모니터링 관련 뷰[편집]

  1. DISK I/O 확인
    - V$SESS_IO
  2. LONG OPS
    - V$SESSION_LONGOPS
  3. CURRENT STATMENT
    - V$PX_SESSION
  4. Session Wait Event
    - V$SESSSION_EVENT

1.3.2 REAL MONITOR[편집]

  1. 사용법
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('5yfzxpu5593jw') FROM DUAL;
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.32.10.png
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.33.44.png
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.35.01.png
ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.36.21.png


  • html 포멧으로 출력
select dbms_sqltune.report_sql_monitor(sql_id=>'5yfzxpu5593jw',type=>'html', report_level=>'ALL') from dual;
sqlmonhtmlpng.png

1.3.3 병렬_쿼리_모니터링[편집]

1.3.3.1 병렬 세션 조회[편집]
select  c.sid as qcsid
     ,  s.sid as slasid
     ,  p.server_name   
  from  v$session c
     ,  v$px_session s
     ,  v$px_process p
 where  c.sid = s.qcsid  
   and c.sid = &SID_OF_SESSION_1  
   and p.sid = s.sid;
1.3.3.2 병렬 처리 데이터 전송 통계 확인[편집]
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;

1.3.4 토드에서 모니터링 하는 방법[편집]

  1. Database - Session Browser
    1. IO 탭
    2. Waits 탭
    3. Current Statsment 탭
    4. Long Ops 탭
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4ed9b84-8.26.48.png
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4ed9b84-8.28.08.png
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4ed9b84-8.29.14.png
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4ed9b84-8.30.51.png
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4ed9b84-8.31.50.png



1.3.5 병렬 처리 WAIT EVENT 확인 방법[편집]

1.3.5.1 병렬 처리 대기 이벤트 발생 순서[편집]
  1. PX Deq: Execute Replay,작업 끝날때까지 대기중
  2. SQL*Net message from client, 클라이언트로 부터 추가 Fetch Call이 오기를 기다리고 있음을 의미함.
1.3.5.2 병렬 처리 대기이벤트 종류[편집]
  1. PX Deq: Parse Reply
    - PEC 가 PES 에게 파싱 요청을 한 후 응답이 올 때까지 대기하는 이벤트
    - 10G 에서 도입된 PSC(Parallel Single Cursor) 모델에서는 PEC가 생성한 커서를 공유하기 때문에 이러한 과정은 생략된다.
    - 단. RAC 에서는 여전히 PEC 와 다른 노드에 존재하는 PES는 PEC가 생성한 SQL문을 파싱하는 역할을 수행
  2. PX Deq: Execute Reply
    - QC가 각 병렬 서버에게 작업을 배분하고 작업이 완료 되기를 기다리는 대기이벤트.
  3. PX Deq Credit : need buffer
    - 데이터를 전송하기 전에 수신 병렬서버 또는 QC로 부터 credit 비트를 얻으려고 대기하는 상태
    - PX Deq Credit:send blkd(로컬 시스템) 과 PX Deq Credit:need buffer(RAC 에서)은 거의 같은 대기 이벤트임.
    - 오라클은 두 프로세스 중 한 순간에 오직 하나의 프로세스만이 테이블 큐에 데이터를 집어넣을 수 있도록 보장한다.
    - 테이블 큐에 데이터를 집어넣을 수 있는 자격을 확보할 때까지 기다리는 이벤트다.
  4. PX Deq: Execution Msg
    - 병렬서버가 자신의 작업을 마치고 다른 병렬서버가 일을 끝나기를 기다리는 이벤트.
    - QC 또는 소비자 병렬 서버에게 데이터 전손을 완료했을때 나타남
  5. PX Deq: Table Q Normal
    - 테이블 큐에 데이터가 들어오기를 기다리는 이벤트
  6. direct path read
    - 버퍼 캐시를 경유하지 않고 데이터 파일로부터 직접 데이터를 읽는 과정에서 발생하는 이벤트
    - 테이블로부터 데이터를 페치하는 작업은 대부분 데이터 파일에서 직접 데이터를 읽는 방식을 사용.
  7. enq: TC Contention
    - QC가 Direct Path I/O를 수행하려면, 해당 테이블에 대한 체크 포인트(Checkpoint)작업이 선행 되어야 한다.
    - 버퍼 캐시의 더티 버퍼가 모두 데이타 파일에 기록되어야 버퍼 캐시를 경유하지 않고 데이터 파일에서 직접 데이터를 읽을 수 있기 때문이다.
    - 작업을 지시하기 전에 체크포인트 요청을 하고 작업이 끝날 때 까지 기다려야 하며 그 동안 enq: TC Contention 이벤트 대기

1.3.5.3 병렬 세션 대기,대기 이벤트,대기 클래스 조회[편집]
  • V$PX_SESSION + V$SESSION
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
;
1.3.5.4 WAIT EVENT - 상세 대기시간 확인[편집]
  • V$SESSION + V$SESSSION_EVENT
SELECT S.INST_ID, S.SID, S.SQL_ID
     , S.MACHINE, S.PROGRAM, S.EVENT
     , S.BLOCKING_SESSION_STATUS
     , SE.EVENT AS EVENT_DTL
     , SE.WAIT_CLASS , SE.TIME_WAITED
     , SE.MAX WAIT   , SE.TOTAL_TIMEOUTS
  FROM GV$SESSION S
     , GV$SESSION_EVENT SE
 WHERE S.SID = SE.SID
   AND S.INST ID = 1
   AND S SID=8281
-- AND SE.EVENT = 'gc current grant busy'
-- AND S.SQL ID = ''
   AND SOL_ID IS NOT NULL
ORDER BY SE.TIME_WAITED DESC
1.3.5.5 대기항목별 WAIT CLASS 확인[편집]
  • V$SESSION_WAIT + V$SESSSION_WAIT_CLASS
SELECT A.SID, A.SEQ#, A.EVENT,P1TEXT
     , A.STATE , A.WAIT_TIME_MICRO
     , A.TIME_REMAINING_MICRO
     , B.WAIT_CLASS#, B.WAIT_CLASS
     , B.TOTAL_WAITS, B. TIME_WAITED
  FROM GV$SESSION WAIT A
     , GV$SESSION_WAIT_CLASS B
 WHERE A.INST_ID = B.INST_ID 
   AND A.INST_ID =1
   AND A.SID = B.SID
   AND A.SID = :SID -- 1510
   AND A.SERIAL# = B.SERIAL#

1.4 PARALLEL 힌트를 사용해도 병렬 처리가 안되는 경우[편집]

1.4.1 병렬 처리 갯수 확인[편집]

1.4.1.1 병렬처리 제약 조건[편집]
SELECT * FROM V$PARAMETER 
 WHERE NAME IN ('parallel_degree_limit') ; -- CPU
  1. CPU
    1. 최대 병렬 처리 갯수가 시스템의 CPU 수에 따라 제한(기본값)
    2. 제한을 계산하는 데 사용되는 공식은 PARALLEL_THREADS_PER_CPU * CPU_COUNT * 사용 가능한 인스턴스 수입니다(기본적으로 클러스터에서 열려 있는 모든 인스턴스이지만 PARALLEL_INSTANCE_GROUP 또는 서비스 사양을 사용하여 제한할 수 있음).
  2. AUTO
    1. CPU 값과 동일
  3. IO
    1. 옵티마이져가 사용할 수 있는 최대 병렬 처리 갯수은 시스템의 I/O 용량에 따라 제한
    2. 이 값은 전체 시스템 처리량을 프로세스당 최대 I/O 대역폭으로 나누어 계산
    3. IO 설정을 사용하려면 시스템에서 DBMS_RESOURCE_MANAGER.CALIBRATE_IO 프로시저를 실행해야 하고 이 절차에서는 전체 시스템 처리량과 프로세스당 최대 I/O 대역폭을 계산함
  4. 숫자
    1. 이 파라메터의 숫자값은 자동 병렬 처리 수준이 활성화된 경우 최적화 프로그램이 SQL 문에 대해 선택할 수 있는 최대 병렬 처리 수준을 지정 함
    2. 자동 병렬 처리 갯수는 PARALLEL_DEGREE_POLICY가 ADAPTIVE, AUTO 또는 LIMITED로 설정된 경우에만 활성화 됨


  • DOP 최대 갯수 = parallel_threads_per_cpu * cpu_count
SELECT *
  FROM V$PARAMETER
 WHERE NAME in ('parallel_threads_per_cpu','cpu_count')

1.4.2 병렬 환경 파리미터 상세 조회[편집]

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


  1. PARALLEL_FORCE_LOCAL
    1. 병렬로 실행되는 SQL 문이 Oracle RAC 환경의 단일 인스턴스로 제한되는지 여부를 지정
    2. 이 매개변수를 TRUE로 설정하면 쿼리 코디네이터가 실행 중인 단일 Oracle RAC 인스턴스로 처리되는 병렬 서버의 범위를 제한
    3. PARALLEL_FORCE_LOCAL 매개변수의 권장 값은 FALSE
  2. PARALLEL_MAX_SERVERS
    1. 인스턴스에 대한 최대 병렬 실행 프로세스 및 병렬 복구 프로세스 수를 지정
    2. 수요가 증가함에 따라 Oracle Database는 인스턴스 시작 시 생성된 수에서 이 값까지 프로세스 수를 늘림.
    3. DOP(병렬 처리 수준) 특성이 더 높은 테이블의 병렬 쿼리에는 기본값이 충분하지 않을 수 있습니다.
    4. 더 높은 DOP의 쿼리를 실행하려면 2 x DOP x NUMBER_OF_CONCURRENT_USERS
  3. PARALLEL_MIN_PERCENT
    1. 사용 중인 애플리케이션에 따라 사용자가 허용 가능한 DOP를 기다림.
    2. PARALLEL_MIN_PERCENT 매개변수의 권장 값은 0.
    3. 0 이외의 값으로 설정하면 지정된 시간에 시스템에서 요청된 DOP를 충족할 수 없을 때 Oracle 데이터베이스에서 오류를 반환 함.
    4. 예를 들어 PARALLEL_MIN_PERCENT를 50으로 설정하고 DOP가 적응형 알고리즘이나 리소스 제한으로 인해 50% 이상 감소하면 Oracle Database는 ORA-12827을 반환.
    5. ORA-12827: insufficient parallel query slaves available
  4. PARALLEL_MIN_SERVERS
    1. 병렬 작업을 위해 예약된 단일 인스턴스에서 시작할 프로세스 수를 지정
    2. PARALLEL_MIN_SERVERS를 설정하면 시작 비용과 메모리 사용량의 균형을 맞춤.
    3. PARALLEL_MIN_SERVERS를 사용하여 시작된 프로세스는 데이터베이스가 종료될 때까지 종료되지 않음.
  5. PARALLEL_MIN_TIME_THRESHOLD
    1. 쿼리가 자동 병렬 처리 수준을 고려하여 쿼리가 가져야 하는 최소 실행 시간을 지정.
    2. 자동 병렬 처리 수준은 PARALLEL_DEGREE_POLICY가 AUTO 또는 LIMITED로 설정된 경우에만 활성화 됨.
  6. PARALLEL_SERVERS_TARGET
    1. 명령문 큐잉이 사용되기 전에 병렬 명령문을 실행할 수 있는 병렬 서버 프로세스 수를 지정
    2. PARALLEL_DEGREE_POLICY가 AUTO로 설정된 경우 시스템에서 현재 사용 중인 병렬 프로세스 수가 PARALLEL_SERVERS_TARGET보다 크거나 같으면 병렬 실행이 필요한 명령문이 대기함.
    3. 시스템에서 허용되는 최대 병렬 서버 프로세스 수가 아님(PARALLEL_MAX_SERVERS에 의해 제어됨).
    4. 그러나 PARALLEL_SERVERS_TARGET 및 병렬 명령문 큐잉은 병렬 실행이 필요한 각 명령문에 필요한 병렬 서버 리소스가 할당되고 시스템이 너무 많은 병렬 서버 프로세스로 플러딩되지 않도록 하는 데 사용
  7. SHARED_POOL_SIZE
    1. 병렬 실행에는 직렬 SQL 실행에 필요한 것 외에 메모리 리소스가 필요
    2. 추가 메모리는 쿼리 서버 프로세스와 쿼리 코디네이터 간의 통신 및 데이터 전달에 사용
    3. Oracle Database는 공유 풀에서 쿼리 서버 프로세스를 위한 메모리를 할당. 다음과 같이 공유 풀을 조정.
      1. 공유 커서 및 저장 프로시저와 같은 공유 풀의 다른 클라이언트를 허용.
      2. 값이 크면 다중 사용자 시스템에서 성능이 향상되지만 값이 작으면 메모리 사용량이 줄어듬.
      3. 그런 다음 병렬 실행에 사용되는 버퍼 수를 모니터링하고 shared pool PX msg pool을 V$PX_PROCESS_SYSSTAT 보기의 출력에 보고된 현재 최고 수위 표시와 비교




1.4.3 병렬처리가 안되는경우[편집]

  • 병렬 요청/할당 확인
select sid,serial#
     , server_group,server_set
     , degree , req_degree  
  from v$PX_SESSION:

1.4.3.1 서버에서 프로세스를 할당 받지 못할때[편집]

1.4.3.2 insert ~ select 의 병렬도가 다를때[편집]

1.4.3.3 파티션닝 테이블에 1개파티션만 타는경우[편집]
  • 튜닝 전
  • LINK_ID 가 파티션키임.
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.37.06.png
  • 플랜
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.39.50.png

  • 튜닝 후
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.42.38.png
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.44.23.png
  • 튜닝 후 플랜
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.45.48.png
1.4.3.4 LOB 컬럼 포함시[편집]
  1. lob 컬럼 포함시 => 오라클 19c 부터기능

1.4.3.6 기타 요소[편집]
  1. 할당된 process 갯수가 작을때 (오라클 파라미터 확인,v$parameter)
  2. OS상 디스크 I/O가 너무 느릴때

1.5 병렬 힌트 활용[편집]

1.5.1 PQ_DISTRIBUTE[편집]

pq_distribute_hint.gif


  1. 병렬 조인 시, 생산자 프로세스와 소비자 프로세스 간의 데이터 전달 방식을 지정
  2. 작성방법
    1. 조인되는 inner테이블을 첫번째 인자로 , outer테이블 분배방식을 두번째 인자로 , inter테이블 분배방식을 세번째 인자로 지정
  3. 용도
    1. 옵티마이져가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
    2. 기존 파티션 키를 무시하고 다른 키 값으로 동작 재분할하고 싶을 때
    3. 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정시키고자 할 때
    4. 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때
  4. 구문
  • PQ_DISTRIBUTE(inner, none, none)
    • Full-parition Wise 조인으로 유도할 때 사용.양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티션되어 있어야 작동
    • 각 병렬 쿼리 서버는 각 테이블에서 하나씩 일치하는 한 쌍의 파티션 간에 조인 작업을 수행
    • 반드시 두 테이블 모두 조인 키에 동등하게 분할되어야 함.
  • PQ_DISTRIBUTE(inner, partition, none)
    • inner 테이블의 파티션을 사용하여 outer 테이블의 행을 매핑
    • inner 테이블은 조인 키로 분할되어야 함
    • 이 힌트는 outer 테이블의 파티션 수가 병렬 쿼리 서버 수의 배수와 같거나 거의 같을 때 권장
  • PQ_DISTRIBUTE(inner, none, partition)
    • outer 테이블의 파티션을 사용하여 inner 테이블의 행을 매핑
    • outer 테이블은 조인 키에 분할되어야 함
    • 이 힌트는 outer 테이블의 파티션 수가 쿼리 서버 수의 배수와 같거나 거의 같을 때 권장
  • PQ_DISTRIBUTE(inner, hash, hash)
    • 조인키의 해시 함수를 사용하여 각 테이블의 행을 소비자 병렬 쿼리 서버에 매핑.
    • 매핑이 완료되면, 각 쿼리 서버는 결과 파티션 쌍 사이에 조인을 수행
    • 테이블의 크기가 비슷하고 조인 작업이 해시 조인 또는 정렬 병합 조인으로 구현될 때 권장
  • PQ_DISTRIBUTE(inner, broadcast, none)
    • outer 테이블의 모든 행이 각 병렬 쿼리 서버로 브로드캐스트되고 inner 테이블 행은 무작위로 분할되도록 함
    • 이 힌트는 outer 테이블이 inner 테이블에 비해 매우 작을 때 권장
  • PQ_DISTRIBUTE(inner, none, broadcast)
    • inner 테이블의 모든 행을 각각의 소비자 병렬 쿼리서버로 브로드캐스트하도록 함
    • outer 테이블 행은 무작위로 분할됩니다.
    • inner 테이블이 outer 테이블에 비해 매우 작을 때 권장



1.5.1.1 가능한 조합[편집]
  1. HASH - HASH : OUTER, INNER 크기가 비슷할 때
  2. BROADCAST - NONE : OUTER 테이블이 작을때
  3. NONE - BROADCAST : INNER 테이블이 작을때
  4. PARTITION - NONE : INNER 테이블 파티션 기준으로 OUTER 테이블을 파티션 하여 분배
  5. NONE - PARTITION : OUTER 테이블 파티션 기준으로 INNER 테이블을 파티션 하여 분배
  6. NONE - NONE : 두 테이블이 조인컬럼 기준으로 파티션 되어 있을때

1.5.2 PQ_REPLICATE / NO_PQ_REPLICATE(대량테이블명)[편집]

  1. 각각 병렬서버에서 테이블 전체를 읽음
  2. BROADCAST 문제점을 보완 하는 힌트
    • BROADCAST처럼 분산 하여 읽지 않음(병렬도가 많을수록 BROADCAST 방식은 분배시 부하 발생)
  3. 로컬 캐시(SGA) 에서 빠르게 읽는 방식
  4. 복제라기보다는 조인처럼 생각
  5. 매우 작은 테이블 처리시 유리
    1. 튜닝 예시 (소량테이블(3MB)이 BROADCAST 가 아닌 조인 형태로 병렬 처리됨)
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4ed9b84-8.55.41.png


  • 소량테이블(3MB)이 BROADCAST 처리 하도록 힌트 추가 (NO_PQ_REPLICATE)
  • 힌트 사용시 NO_PQ_REPLICATE(소량테이블명) 이 아닌 NO_PQ_REPLICATE(대량테이블명) 임을 주의
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4ed9b84-8.59.12.png

1.5.3 PQ_SKEW/NO_PQ_SKEW[편집]

  1. 다수의 로우가 같은 조인키값을 가지고 있어서 조인키의 분산값이 한쪽으로 치우친 경우
  2. 오라클에서 히스토그램을 생성해야 하지만 자동으로 병렬조인시 SKEW를 핸들링함
  3. 제약사항
    1. INNER 조인시
    2. 단일 컬럼 조인시만 가능, 여러개 컬럼은 안됨
    3. 병렬 HASH JOIN만 가능
    4. MERGE JOIN 은 안됨
    5. SKEW테이블은 일반 테이블만 (뷰, 결과셋은 기능제한됨)
  • PQ_SKEW 힌트는 옵티마이저에게 병렬 조인에 대한 조인 키 값의 분포가 매우 왜곡되어 있다고 조언합니다.
  • 즉, 높은 비율의 행이 동일한 조인 키 값을 가지고 있습니다.
  • Tablespec에 지정된 테이블은 해시 조인의 프로브 테이블이다.

1.5.4 BF 블름필터(Bloom Filter)[편집]

  1. 어떤값이 어떤 집합에 속해 있는가를 검사하는 필터
  2. 패러럴 조인시 소비자간의 커뮤니케이션 데이터량 과 해시조인시 부하를 감소하기 위해 사용됨
  3. JOIN FILTER PRUNNING , RESULT CACHE
  4. 플랜에서 JOIN FILTER CREATE / USE
     :BF0000
1.5.4.1 블름필터 제어 힌트 ( PX_JOIN_FILTER / NO_PX_JOIN_FILTER )[편집]
  1. 사용 조건
    1. 해시/머지 조인시
    2. 파티션 조인시
    3. 병렬 PARALLEL 쿼리시
    4. 파티션/PARALLEL 둘다 아닌경우, 인라인뷰의 GROUP BY
  2. 선행 테이블의 상수조건이 없는 경우 오라클은 블름필터를 사용하지 않는다.
  3. 블름 필터를 만들때 선행 테이블은 필터집합을 만들때 사용.

1.5.5 PQ_DISTRIBUTE_WINDOW[편집]

  1. PQ_DISTRIBUTE_WINDOW 힌트는 윈도우 함수에 의해 생성된 행을 분배하는 방법에 대해 옵티마이져에게 지시함.
  2. PQ_DISTRIBUTE_WINDOW(@Query_block N) => N=1 for hash, N=2 for range, N=3 for list(예전 방식 9i)
  • PQ_DISTRIBUTE_WINDOW 힌트는 아직 문서화되지 않았으며 MOS(내 oracle 지원)에는 참조가 없습니다.
select /*+parallel PQ_DISTRIBUTE_WINDOW(1)*/ table_name
     , count(1) over (partition by table_name) cnt 
 from t_tab t\;
Plan hash value: 4185789934  
  
----------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
----------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT           |            |  2426 | 46094 |     4  (25)| 00:00:01 |        |      |            |  
|   1 |  PX COORDINATOR            |            |       |       |            |          |        |      |            |  
|   2 |   PX SEND QC (RANDOM)      | :TQ10001   |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
|   3 |    WINDOW SORT             |            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | PCWP |            |  
|   4 |     PX RECEIVE             |            |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
|   5 |      PX SEND HASH          | :TQ10000   |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |  
|   6 |       PX BLOCK ITERATOR    |            |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
|   7 |        INDEX FAST FULL SCAN| T_TAB_IDX1 |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
----------------------------------------------------------------------------------------------------------------------  
  
Outline Data  
-------------  
  
  /*+  
      BEGIN_OUTLINE_DATA  
      PQ_DISTRIBUTE_WINDOW(@"SEL$1" 1)  
      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_TAB"."OWNER" "T_TAB"."TABLE_NAME"))  
      OUTLINE_LEAF(@"SEL$1")  
      SHARED(2)  
      ALL_ROWS  
      OPT_PARAM('star_transformation_enabled' 'true')  
      DB_VERSION('12.1.0.2')  
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
      IGNORE_OPTIM_EMBEDDED_HINTS  
      END_OUTLINE_DATA  
  */
select /*+parallel PQ_DISTRIBUTE_WINDOW(2)*/ table_name
     , count(1) over (partition by table_name) cnt 
  from t_tab t
Plan hash value: 1125815052  
  
------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT             |            |  2426 | 46094 |     4  (25)| 00:00:01 |        |      |            |  
|   1 |  PX COORDINATOR              |            |       |       |            |          |        |      |            |  
|   2 |   PX SEND QC (RANDOM)        | :TQ10001   |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
|   3 |    WINDOW CONSOLIDATOR BUFFER|            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | PCWP |            |  
|   4 |     PX RECEIVE               |            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | PCWP |            |  
|   5 |      PX SEND HASH            | :TQ10000   |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,00 | P->P | HASH       |  
|   6 |       WINDOW SORT            |            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,00 | PCWP |            |  
|   7 |        PX BLOCK ITERATOR     |            |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
|   8 |         INDEX FAST FULL SCAN | T_TAB_IDX1 |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
------------------------------------------------------------------------------------------------------------------------  
  
Outline Data  
-------------  
  
  /*+  
      BEGIN_OUTLINE_DATA  
      PQ_DISTRIBUTE_WINDOW(@"SEL$1" 2)  
      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_TAB"."OWNER" "T_TAB"."TABLE_NAME"))  
      OUTLINE_LEAF(@"SEL$1")  
      SHARED(2)  
      ALL_ROWS  
      OPT_PARAM('star_transformation_enabled' 'true')  
      DB_VERSION('12.1.0.2')  
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
      IGNORE_OPTIM_EMBEDDED_HINTS  
      END_OUTLINE_DATA  
  */

1.5.6 PQ_EXPAND_TABLE / NO_PQ_EXPAND_TABLE[편집]

  1. PQ_EXPAND_TABLE
    1. 파티션테이블인 경우 한곳에 편중된 파티션이 있을때, 옵티마이저가 UNION 절로 변경하여 편중된 파티션과 다른 파티션을 나누어 병렬처리로 수행함.
    2. (예) 인라인뷰내 파티션테이블을 (GROUP BY하는 경우)발생함
  • 19c New Feature
  • 튜닝 사례 1 : 한곳에 치중된 파티션을 UNION ALL 로 분리
SELECT *
  FROM 
(SELECT /*+ PQ_EXPAND_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
.....


  • 튜닝 사례 2 : 한곳에 치중된 파티션을 UNION ALL 로 분리 하지 않음.
SELECT *
  FROM 
(SELECT /*+ NO_PQ_EXPAND_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
.....

1.5.7 PQ_CONCURRENT_UNION[편집]

  1. UNION ALL 성능향상
  2. UNION은 각각 SQL을 1개씩 SERIAL 하게 처리하는게 기본방식임
  3. 12C 부터 병렬쿼리 실행시 동시(CONCURRENT)에 처리토록 함
  4. 전체데이터 처리시 유리
  5. 부분범위 처리시 비추
  6. DEFAULT
select /*+PQ_CONCURRENT_UNION*/ * from (select * from t_obj union all select * from t_obj1)

Plan hash value: 1664138491  
  
----------------------------------------------------------------------------------------------------------------  
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
----------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT       |          |   184K|    64M|   863   (1)| 00:00:01 |        |      |            |  
|   1 |  PX COORDINATOR        |          |       |       |            |          |        |      |            |  
|   2 |   PX SEND QC (RANDOM)  | :TQ10000 |   184K|    64M|   863   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  
|   3 |    VIEW                |          |   184K|    64M|   863   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
|   4 |     UNION-ALL          |          |       |       |            |          |  Q1,00 | PCWP |            |  
|   5 |      PX SELECTOR       |          |       |       |            |          |  Q1,00 | PCWP |            |  
|   6 |       TABLE ACCESS FULL| T_OBJ    | 92299 |    10M|   431   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
|   7 |      PX SELECTOR       |          |       |       |            |          |  Q1,00 | PCWP |            |  
|   8 |       TABLE ACCESS FULL| T_OBJ1   | 92299 |    10M|   431   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
----------------------------------------------------------------------------------------------------------------  
  
Outline Data  
-------------  
  
  /*+  
      BEGIN_OUTLINE_DATA  
      FULL(@"SEL$2" "T_OBJ"@"SEL$2")  
      FULL(@"SEL$3" "T_OBJ1"@"SEL$3")  
      PQ_CONCURRENT_UNION(@"SET$1")  
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")  
      OUTLINE_LEAF(@"SEL$1")  
      OUTLINE_LEAF(@"SET$1")  
      OUTLINE_LEAF(@"SEL$3")  
      OUTLINE_LEAF(@"SEL$2")  
      ALL_ROWS  
      OPT_PARAM('star_transformation_enabled' 'true')  
      OPT_PARAM('_px_concurrent' 'false')  
      DB_VERSION('12.1.0.2')  
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
      IGNORE_OPTIM_EMBEDDED_HINTS  
      END_OUTLINE_DATA  
  */

1.5.8 PQ_FILTER[편집]

/*+ PQ_FILTER(SERIAL | NONE | HASH | RANDOM) */ -- 4개중 택1
  1. 병렬서버에서 서브쿼리를 필터링할수 있는 기능.
  2. 서브쿼리 필터링은 일반적으로 메인쿼리가 모두 수행된 후 수행함.
  3. HASH방식과 RANDOM방식은 추가적인 버퍼링이 필요하므로 특별한 경우가 아니면 NONE 방식으로 사용하는것이 일반적일것으로 판단함.
  4. 다수의 서브쿼리 수행시 2개의 서브쿼리 모두 병렬서버에서 필터링됨.


  • PQ_FILTER 사용예시 (with NO_UNNEST 힌트)
select /*+parallel PQ_FILTER(HASH)*/ * from t_obj1 o 
 where created in (select /*+ NO_UNNEST */last_analyzed 
                     from t_tab t 
                    where tablespace_name like :A)
;
Plan hash value: 2440581449  
  
------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT         |          |   110 | 12650 | 22159   (1)| 00:00:01 |        |      |            |  
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |  
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
|   3 |    BUFFER SORT           |          |   110 | 12650 |            |          |  Q1,01 | PCWP |            |  
|*  4 |     FILTER               |          |       |       |            |          |  Q1,01 | PCWP |            |  
|   5 |      PX RECEIVE          |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,01 | PCWP |            |  
|   6 |       PX SEND HASH       | :TQ10000 | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | P->P | HASH       |  
|   7 |        PX BLOCK ITERATOR |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWC |            |  
|   8 |         TABLE ACCESS FULL| T_OBJ1   | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
|*  9 |      TABLE ACCESS FULL   | T_TAB    |     2 |    30 |    26   (0)| 00:00:01 |        |      |            |  
------------------------------------------------------------------------------------------------------------------  
  
Outline Data  
-------------  
  
  /*+  
      BEGIN_OUTLINE_DATA  
      FULL(@"SEL$2" "T"@"SEL$2")  
      PQ_FILTER(@"SEL$1" HASH)  
      FULL(@"SEL$1" "O"@"SEL$1")  
      OUTLINE_LEAF(@"SEL$1")  
      OUTLINE_LEAF(@"SEL$2")  
      SHARED(2)  
      ALL_ROWS  
      OPT_PARAM('star_transformation_enabled' 'true')  
      OPT_PARAM('_px_filter_parallelized' 'false')  
      DB_VERSION('12.1.0.2')  
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
      IGNORE_OPTIM_EMBEDDED_HINTS  
      END_OUTLINE_DATA  
  */


  • 서브 쿼리가 2개일 때 순서 조정 예시
=> ORDER_SUBQ 힌트(12c 이후) 로 서브쿼리의 수행순서 조정가능함.
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)


  1. SERIAL
    1. QC 에서 서브쿼리를 필터링함
  2. NONE
    1. 메인쿼리를 분배하지 않고 병렬서버에서 서브쿼리를 필터링함.
    2. 병렬서버가 T1 테이블을 블록 그래뉼로 조회하고, 조회결과로 서브쿼리를 필터링하여 QC로 결과를 전송하는 방식
  3. HASH
    1. 메인 쿼리를 HASH 방식으로 분배 한후, 병렬서버에서 서브쿼리를 필터링함.
    2. HASH방식은 조인조건인 C1 컬럼의 해시값으로 분배 되기 때문에 서브쿼리 캐싱 효율을 높이는 목적으로 활용가능.
  4. RANDOM
    1. 메인쿼리를 RANDOM 방식으로 분배한후, 병렬서버에서 서브쿼리를 필터링함.
    2. RANDOM 방식은 read by other session등 의 블럭경합을 해소하는 목적으로 활용가능



1.5.9 파티션에 사용 힌트[편집]

1.5.9.1 USE_PARTITION_WISE_DISTINCT[편집]
  1. 12c 이상
  2. USE_PARTITION_WISE_DISTINCT 힌트를 사용하면 파티션 그래뉼로 중복 값을 제거
SELECT /*+ PARALLEL(T1 2) USE_PARTITION_WISE_DISTINCT */ DISTINCT c1, c2 FROM t1;
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |       |       |        |      |            |
|   1 |  PX COORDINATOR          |          |      1 |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000 |      0 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION RANGE ALL|          |      0 |     1 |     3 |  Q1,00 | PCWC |            |
----------- [!!!]
|   4 |     HASH UNIQUE          |          |      0 |       |       |  Q1,00 | PCWP |            |
|   5 |      TABLE ACCESS FULL   | T1       |      0 |     1 |     3 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------
1.5.9.2 USE_PARTITION_WISE_GBY[편집]
  1. USE_PARTITION_WISE_GBY 힌트를 사용하면 파티션 그래뉼로 그룹핑 수행 할 수 있음
  2. 병렬 서버 간의 데이터가 분배가 발생하지 않음 (P->P 분배가 없음)
SELECT /*+ PARALLEL(T1 2) USE_PARTITION_WISE_GBY */ c1, COUNT(c2) FROM t1 GROUP BY c1;
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |       |       |        |      |            |
|   1 |  PX COORDINATOR          |          |      1 |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000 |      0 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION RANGE ALL|          |      0 |     1 |     3 |  Q1,00 | PCWC |            |
----------> [!!!]
|   4 |     HASH GROUP BY        |          |      0 |       |       |  Q1,00 | PCWP |            | 
|   5 |      TABLE ACCESS FULL   | T1       |      0 |     1 |     3 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------
1.5.9.3 USE_PARTITION_WISE_WIF[편집]
  1. 18c 이상
  2. USE_PARTITION_WISE_WIF 힌트를 사용하면 파티션 그래뉼로 분석 함수 수행
SELECT /*+ PARALLEL(T1 2) USE_PARTITION_WISE_WIF */ ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) FROM t1;
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |       |       |        |      |            |
|   1 |  PX COORDINATOR          |          |      1 |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000 |      0 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION RANGE ALL|          |      0 |     1 |     3 |  Q1,00 | PCWC |            |
|   4 |     WINDOW SORT          |          |      0 |       |       |  Q1,00 | PCWP |            | -- !
|   5 |      TABLE ACCESS FULL   | T1       |      0 |     1 |     3 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------

1.6 병렬 쿼리 튜닝 포인트[편집]

1.6.1 테이블 정보를 수집 하라[편집]

  1. 테이블 사이즈 조사
  2. 테이블 파티셔닝 여부
  3. 파티션 키
  4. 병렬처리가 가능한 인덱스(index_ffs , 파티션 로컬 인덱스)

1.6.2 스칼라퀴리는 인라인뷰로 변경을 검토하라[편집]

  1. 쿼리 결과를 전송하는 단계에서 수행되는 스칼라 서브쿼리는 QC가 담당
  2. 스칼라 쿼리를 인라인뷰로 변경 => lateral 쿼리 활용법
    1. 스칼라쿼리
      ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.25.09.png
    2. 인라인뷰로 변경
      ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.26.35.png

1.6.3 플랜에서 QC(Query Cordinator) 위치를 확인 하라[편집]

1.6.3.1 DELETE 구문[편집]
  • 튜닝전
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.00.40.png
  • 튜닝 후
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.03.01.png

1.6.3.2 MERGE 구문[편집]
  • 튜닝 전
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.07.58.png
  • 튜닝 후
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.10.04.png
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-1.11.17.png

1.6.4 BROADCAST 테이블을 찾아라[편집]

  1. 대용량 임시/템프 테이블에 주의하라
    - 데이터 전환 이나 인터페이스를 위한 중간(임시) 테이블 생성시 반드시 중간 테이블에 대한 통계정보를 생성토록 한다.
    - 임시테이블은 주로 통계 정보가 생성되어 있지 않아. 통계정보 오류로 인해 브로드캐스트로 처리되는 경우가 많음.
  2. TEMP테이블 스페이스가 Full 차서 중지(ORA-01652 에러) 되는 경우가 많음

1.6.5 SQL/PLAN에서 튜닝 대상을 찾아라[편집]

  1. rownum => row_number() 윈도우 함수로 변경
  2. S->P 분산 프로세스
  3. round - robin
    ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.51.17.png


  • 플랜
    ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.53.04.png
    - NL조인으로 round - robin 으로 처리중

  • 튜닝 조치
    ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.55.28.png
    - HASH 조인 으로 변경
  • 튜닝 결과 플랜 정보
    ec8aa4ed81aceba6b0ec83b7-2023-11-26-ec98a4ed9b84-11.58.21.png

1.6.6 조인이 효율적인지 검토 하라[편집]

ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-12.05.19.png
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-12.06.52.png
ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-12.08.19.png

1.6.7 심플하게 튜닝 하는 방법은 없을까?[편집]

  1. opt_param('_parallel_broadcast_enabled','false')
    ec8aa4ed81aceba6b0ec83b7-2023-11-27-ec98a4eca084-12.43.57.png
  2. pq_distribute(A hash hash)

1.6.8 병렬 처리시 주의 사항[편집]

  1. 실행계획에서 P -> P가 나타날 때면 지정한 병렬도의 2배수만큼 병렬 프로세스 필요, 정렬이나 그룹핑작업 수행시
  2. 쿼리 블록마다 병렬도를 다르게 지정한 경우, 여러 가지 우선 순위와 규칙에 따라 최종 병렬도가 결정됨, 병렬도를 같게 지정하는 것이 바람직 함.
  3. parallel_index 힌트를 사용할 때는 반드시 index 또는 index_ffs 힌트를 사용
    1. 옵티마이저에 의해 Full table Scan이 선택될 경우 parallel_index 힌트는 무시 됨
  4. 병렬 DML 수행시 Exculsive 모드 테이블 Lock이 걸리므로 업무 트랜잭션이 발생하는 주간에는 삼가
  5. 테이블이나 인덱스를 빠르게 생성하려고 parallel 옵션 을 사용했다면 작업을 완료 후 즉시 noparallel로 변경 할것
  6. 부분범위처리 방식으로 조회하면서 병렬 쿼리를 사용한 때에는 필요한 만큼 데이터를 Fetch 하고 나서 곧바로 커서를 닫아 주어야 함
    1. Toad나 Orange 처럼 부분범위처리를 지원하는 쿼리 툴에서는 EOF에 도달하기 전까지
    2. 커서를 오픈한 채로 유지하기 때문에 오라클은 병렬 서버들을 해제하지 못하고 대기 상태에 머물도록 한다.
    3. 이는 불필요한 리소스를 낭비하는 결과를 초래하므로 조회가 끝나자마자
    4. select * from dual 같은 문장을 수행해 병렬 쿼리의 커서를 닫아 주어야 한다.



1.7 입력/수정 성능저하시 검토 사항[편집]


1.7.1 INSERT 처리가 느릴때(SELECT~INSERT시 SELECT는 빠른데 INSERT가 느린경우)[편집]

  1. 테이블 빈공간 정리/재구성(HWM ReOrg)

1.7.2 시퀀스를 사용하는경우[편집]

  1. sequence cache size 증가 검토
  2. default 20 => 2000이상

1.7.3 DB링크 병렬처리 체크사항[편집]

  1. 소스디비의 프로세스 최대갯수 확인 필요
  2. 옵티마이져가 쿼리 변형을 수행함
    1. 쿼리 변형 방지 힌트 /*+ no_query_transformation */
  3. dblink로 가져올때는 병렬처리 불가

1.8 데이터 전환시 사용하는 병렬처리[편집]

1.8.1 데이터 전환을 위한 최적 세션 옵션[편집]

-- 세션에서 병렬 쿼리 작업 절차
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET HASH_AREA_SIZE          = 1024000000;
ALTER SESSION SET SORT_AREA_SIZE          = 2147483647; -- 최대 2기가 
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;
-- 병렬 실행 메시지 사이즈 32K
alter system set parallel_execution_message_size = 32768; -- 16384 -- 기본

1.8.2 대량 테이블 건수 조회시 index_ffs,parallel_index 힌트[편집]

  • index fast full scan은 병렬 조회 가능
  • parallel_index 힌트는 는 index_ffs, index 힌트와 같이 사용
  • 파티션 인덱스이면 병렬 조회 가능
select /*+ parallel_index(A TB_XXX_PK 8) index_ffs(A TB_XXX_PK) */ count(*) 
  from TB_XXX
  1. -wise : (접미사)~방식으로 뜻