공간관리와 Direct Path Load

해당 내용의 출처의 블로그를 주로 번역하여 정리하였습니다.

출처 : https://blogs.oracle.com/optimizer/post/space-management-and-oracle-direct-path-load

Space Management and Direct Path Load

 

제가 이글을 정리하게 된 이유는 실행계획에서 아래와 같은 표현을 발견하고 그것이 무엇인지 정리하다가 입니다.

|  4 |      PX RECEIVE                         |                |       1 |    2 |        13 |    +26 |    12 |      24M |      |       |       |       |     . |     0.37 | Cpu (3)                            |
|  5 |       PX SEND RANGE                     | :TQ10000       |       1 |    2 |        17 |    +20 |    12 |      25M |      |       |       |       |     . |          |                                    |
|  6 |        LOAD AS SELECT (HYBRID TSM/HWMB) | EX_FOH007S     |         |      |        37 |     +0 |    12 |       24 | 7069 |   7GB |  5886 |   5GB |     . |    35.82 | Cpu (240)                          |

LOAD AS SELECT (HYBRID TSM/HWMB)라는 것이고, 이게 어떤것을 의미하는지 알고자 이글에 정리합니다.

 

먼저, Direct Path Load에 대해서 기본적으로 알아야 합니다.

 

Direct Path Load란 간단하게 아래와 같은 특징이 있습니다.

1.버퍼 캐시를 거치지 않고 디스크에 직접 씀

2.HWM의 윗부분의 block에 data를 load함. 완전히 새로운 block을 할당받아 data를 입력.

3.테이블 전체의 lock이 걸림. (TM-X mode lock).

 

Direct Path Load 작업 중에 공간관리를 하는 방법 중 하나로 위와 같은 것이 나타나게 됩니다. 

12.1.0.2 부터 이 정보를 실행계획에 표시하게 되고 사용자가 확인할 수 있게 됩니다.

 

공간관리를 위한 획일적인 접근 방식이 없는 이유는, 다양한 상황에서 잘 작동해야 하기 때문입니다.

일반 테이블과 파티션테이블에서 잘 작동해야 합니다.또한 단일 인스턴스와 RAC환경에서도 잘 작동해야 합니다.

그에 따른 데이터 왜곡도 고려되어야 합니다. 

병렬 작업은 여러 병렬 프로세스에 일(워크로드)을 고르게 분산하는것이 매우 중요합니다. 그렇지 않으면 유용한 작업을 수행할 수 없게 됩니다. 이로 인해 병렬 처리 수준이 낮아지고, 리소스 활용도가 낮아지며 수행시간도 늘어나게 될 것입니다.

 

공간관리는 11.2 기준으로 설명하고 12c에서 변경된 사항을 추가로 설명하겠습니다.

 

High Water Mark (HWM) Loading

HWM Loading 방식은 가장 단순하고 직관적인 로드 매커니즘입니다. 아래와 같은 명령어로 수행됩니다.

(병렬 프로세스를 사용하지 않고 APPEND만 사용함)

ALTER SESSION DISABLE PARALLEL DML;
INSERT /*+ APPEND */ INTO sales_copy t1 SELECT ...;

insert 된 데이터는 High Water Mark 위에 할당된 익스텐트에 삽입됩니다. 

해당 데이터는 트랜잭션이 커밋될 때 까지 새 데이터가 보이지 않고, 커밋시  HWM가 새 데이터가 포함된 익스텐트까지 통합된 위치로 이동합니다.

 

HWM Loading은 세그먼트에 대한 exclusive lock을 획득하는 경우 사용할 수 있습니다. 아래와 같은 작업입니다.

Serial load, parallel loads which distribute by the partition key (PKEY distribution) and “equi-partition” loads.

 

예를들어, equi-partition load 는 병렬실행서버를 타겟 테이블의 특정 파티션에 매핑하여 여기서 소스 테이블의 해당파티션에서 모든 데이터 로드를 담당합니다. 동일하게 분할된 두 테이블 간에 병렬로 데이터를 복사하는 경우 equi-partition load 가 자동으로 선택되지만 테이블에 상대적으로 많은 수 의 파티션이 있는 경우에만 가능합니다.

 

아래 그림은 2 노드 RAC에서의 Serial HWM loading을 나타냅니다. 데이터가 테이블에서 채워지면서 HWM 이후의 익스텐트에 삽입되고 커밋시 HWM가 오른쪽으로 이동합니다.

 

 

 

Temp Segment Merge (TSM) Loading

Oracle에서 병렬 데이터 로딩을 달성하는데 사용된 첫번째 매커니즘이며 Oracle 11.2에서 단일 세그먼트로의 병렬 로딩을 위한 기본 매커니즘입니다.

 

일반 테이블에 대한 Parallel Create Table as Select(PCTAS):

CREATE TABLE sales_copy PARALLEL 8 AS SELECT * FROM sales;

일반 테이블 또는 단일 파티션에 대한 Parallel Insert Direct Load(PIDL):

INSERT /*+ APPEND PARALLEL(t1,8) */ INTO sales_copy_nonpart t1 SELECT ...;
INSERT /*+ APPEND PARALLEL(t1,8) */ INTO sales_copy partition (part_p1) t1 SELECT ...;

병렬 데이터 로딩이 시작되면 각 병렬 프로세스는 임시 세그먼트에 할당됩니다.

임시 세그먼트는 로드되는 테이블 또는 파티션과 동일한 테이블스페이스에 상주하며 커밋시 임시 세그먼트는 익스텐트 맵을 조작하여 기본 세그먼트와 병합됩니다. 즉, 임시 세그먼트는 데이터를 두번 이동하지 않고 테이블이나 파티션에 통합됩니다.

 

TSM loading은 각 병렬 프로세스(PX server)가 자체 임시 세그먼트 전용이므로 데이터를 로드하는 각 프로세스 간에 상당한 격리가 있기 때문에 DOP(병렬도 Degree of Parallel)에 따라 매우 잘 확장됩니다. 일반적으로는 리소스가 될때까지 DOP가 높을수록 더 많은 성능을 제공할 것으로 기대할 수 있습니다.

다만, 잠재적인 단점이 있는데, 각 임시 세그먼트는 테이블이나 파티션의 일부가 될 때 적어도 각각 하나의 익스텐트가 됩니다. DOP가 16에서 로드하는 경우 행 수가 상대적으로 적더라도 테이블에 최소한 16개의 새 익스텐트를 추가하게 됩니다.(공간의 낭비가 일어날 수 있다는 것을 의미합니다)

소량의 데이터를 자주 로드하는 것보다 대량의 데이터를 상대적으로 드물게 로드하는 경우 이러한 단점을 보완할 수 있습니다.

 

High Water Mark Brokering (HWMB)

여러 병렬 프로세스가 잠재적으로 동일한 테이블 또는 파티션 세그먼트를 채울 수 있는 경우 HWMB(High Water Mark Brokering)을 사용할 수 있습니다. 여러 파티션으로 Direct Path Loading을 실행할 때 종종 사용됩니다.

INSERT /*+ APPEND PARALLEL(t1,8) */ INTO sales_copy_partitioned t1 SELECT ...;

또한 Auto DOP가 사용되는 경우 Oracle 11.2의 단일 세그먼트 로드(일반 테이블 or 특정 파티션)에 대한 기본값이기도 합니다.

HWM Loading과 마찬가지로, 새로운 데이터가 기존 HWM 위에 직접 추가됩니다. 옵티마이저가 선택한 DOP 및 병렬 실행계획 유형에 따라 여러 병렬 프로세스가 동일한 세그먼트에 데이터를 삽입해야 할 수 있습니다. 이 상황에서는 여러 프로세스와 심지어 데이터베이스 서버간의 HWM의 위치를 조정하거나 중개해야 합니다. 브로커링은 HV enqueue를 사용하여 구현됩니다. 각 세그먼트에는 고유한 HV enqueue가 있으며 이는 트랜잭션이 커밋되면 HWM의 이동해야 하는 위치를 기록하는데 사용됩니다. HV enqueue는 여러 프로세스가 동시에 HWM 의 위치값을 업데이트할 수 없도록 합니다.

위의 그림은 단일 인스턴스에서 DOP가 4인 경우에 HWM 조정 시를 표현하고 있습니다.

일반적으로 HWMB는 TSM보다 테이블(또는 단일 파티션) 세그먼트에 추가되는 범위가 더 적습니다. 이 이점은 데이터를 자주 로드하는 시스템과 높은 DOP를 활용하여 데이터 로드  경과 시간을 줄이는 시스템에서 중요합니다.

 

Hybrid TSM/HWMB (Oracle 12c)

Oracle 12.1부터의 변경사항은 특히 일반 테이블 또는 단일 파티션 세그먼트를 insert할 때 사용되는 병렬 Direct Path Loading의 확장성을 향상시킵니다. 일반 테이블과 단일 파티션 간에 데이터를 이동하고 변환하는 고성능 환경에서 특히 중요하고, Partition Exchange 작업을 사용하여 파티션된 테이블에 일반 테이블이 채워지는 환경에서도 유용합니다.

INSERT /*+ APPEND PARALLEL(t1,8) */ 
INTO   sales_copy t1              /* sales_copy is not partitioned */
SELECT /*+ PARALLEL(t2,8) */ * 
FROM sales t2; /* sales is not partitioned */

 

실행계획은 다음과 같습니다.

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |               |       |       |    73 (100)|          |        |      |            |
|   1 |  PX COORDINATOR                    |               |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000      |  1000K|  8789K|    73   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| SALES_DL_COPY |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR              |               |  1000K|  8789K|    73   (2)| 00:00:01 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL             | SALES_DL      |  1000K|  8789K|    73   (2)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------

이름에서 알 수 있듯이 TSM과 HWMB의 장점을 결합한 하이브리드 솔루션입니다. 

해당 방식의 주된 목적은 단일 세그먼트에 insert 하기 위해 큰 DOP를 사용하는 경우 HWMB 방식은 불리합니다.

단일 HV enqueue는 자주 업데이트가 되어야 하므로 병렬 프로세스가 RAC 환경에 분산되어 있는 경우에는 잠재적인 경합지점이 더 중요해집니다. 전체 클러스터에서 사용중인 HV enqueue에 대한 엑세스를 직렬화하는 것은 시간이 많이 소요될 수 있으므로 HV enque에 대한 인스턴스 간의 경합을 제거하는 동시에 새로운 익스텐트의 수를 줄이도록 TSM와 HWMB를 결합합니다. 

일반 테이블이나 단일 파티션에서 병렬 로딩할때 Hybrid TSM/HWMB가 어떻게 작동하는지 설명하겠습니다.

(여기서 최적화가 발생할 가능성이 가장 높기 떄문) 병렬 로드작업이 시작되면 로드와 관련된 각 인스턴스에 대해 임시세그먼트가 생성됩니다. (클러스터 전체에 분산된 경우 2노드 RAC 클러스터라면 2개의 임시 세그먼트가 있음)

각 임시 세그먼트는 여러 병렬 프로세스에서 로드할 수 있지만 데이터베이스는 각 임시 세그먼트가 단일 인스턴스노드에서 병렬 프로세스에 의해 로드되도록 합니다. 이러한 방식으로 각 임시 세그먼트와 연결된 HV enqueue는 각 데이터베이스 인스턴스에 대해 로컬로 중개되며 전체 클러스터에서 중개될 필요가 없습니다.

 

아래 그림은 DOP 4의 Hybrid TSM/HWMB로드를 나타냅니다.

요약하면 Hybrid TSM/HWMB의 이점은 다음과 같습니다.

- 높은 DOP 및 RAC에서 효과적일 수 있습니다.

- 더적은 테이블 익스텐트

- 병렬 프로세스의 성능이 저하되는 위험이 줄어듬.

 

Oracle 12c에서

- Parallel CTAS, Insert및 Merge 작업은 단일 세그먼트 로드를 위한 TSM대신 Hybrid TSM/HWMB를 사용합니다. 

Auto DOP나 Manual DOP에서 모두 사용됩니다.

- TSM은 확장성이 뛰어나고(일회성 작업이므로) 단편화 및 익스텐트 팽창과 관련된 잠재적인 단점을 방지하기 때문에 일부 파티션 테이블의 Parallel CTAS 작업에 여전히 사용될 수 있습니다.

- Hybrid TSM/HWMB는 일부 파티션 테이블의 Parallel CTAS 작업에 대해 TSM 대신 사용됩니다.

- 공간 관리 데코레이션은 Parallel Loading 작업에 대한 실행계획에 명확하게 표시됩니다.

Load as select 는 "TEMP SEGMENT MERGE","HIGH WATER MARK BROKERED","HIGH WATER MARK","HYBRID TSM/HWMB","EQUI-PARTITION"으로 표시됩니다.