DATOR


Program Parallel 방식의 진화 DBMS_PARALLEL_EXECUTE (4)


 

1. DBMS_PARALLEL_EXECUTE 개념과 ROWID 배분방식 사례

2. 작업 단위(Chunk) 균일성, 누락없음 확인

3. NUMBER Column 배분방식 사례

4. 사용자 정의 SQL 배분방식 사례(DB Link를 통한 ROWID 배분)

5. Serial 성능 비교

6. 모니터링과 오류 처리, 주의사항

 

이전 글에서 ROWID 배분방식과 NUMBER Column 배분방식을 알아보았고, 이 글에서는 사용자 정의 SQL 배분방식 사례를 알아보자.

사용자 정의 SQL을 통한 배분방식은 다음의 경우에 유용하다.

l  ROWID 배분방식이 지원하지 않는 경우의 배분 (: DB Link를 통한 ROWID 배분)

l  NUMBER column외의 다른 컬럼을 기준으로 배분

 

여기에서는 첫 번째 경우의 DB Link를 통한 ROWID 배분 사례를 설명하고자 한다.

 

CREATE_CHUNKS_BY_ROWID 를 사용하여DB Link를 통한 테이블의 ROWID 배분을 시도하면, <ORA-29491: invalid table for chunking, 조각에 부적합한 테이블> 오류가 발생한다.

 

---------------------------------------------------------------------

-- 1단계: 작업생성

BEGIN

  DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'DPE_TEST(BY ROWID, VIA DBLINK)');

END;

/

 

----------------------------------------------------------------------------------------------------

-- 2단계: 작업 단위 분할

BEGIN

  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(TASK_NAME   => 'DPE_TEST(BY ROWID, VIA DBLINK)',
                                               TABLE_OWNER => USER,
                                               TABLE_NAME  => 'T1@DL_MS949',  -- TABLE _NAME “T1@DL_MS949” DB Link 지정
                                               BY_ROW      => TRUE,
                                               CHUNK_SIZE  => 10000);
END;
/

--> 실행 오류 메시지
ORA-29491: 조각에 부적합한 테이블
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE",  27
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE",  121
ORA-06512:  4
 

이 경우, DB Link상의 테이블에 대한 ROWID를 배분해 주는 SQL을 작성하여 CREATE_CHUNKS_BY_SQL 을 통해 적용할 수 있다.

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL (
   task_name  IN  VARCHAR2,
   sql_stmt   IN  CLOB,
   by_rowid   IN  BOOLEAN);

 

sql_stmt CLOB type으로 길이에 거의 제약없이 사용할 수 있으나, 여기에서는 SQL을 직접 기술하기 보다 Pipe-lined function을 사용하는 방법을 제시한다.

 

-- 1. TYPE 생성 (Pipe-Lined function에서 return하기 위함)
CREATE OR REPLACE TYPE TP_ROWID_RANGE AS OBJECT (
    START_ROWID VARCHAR2(50)
   ,END_ROWID   VARCHAR2(50)
);

CREATE
OR REPLACE TYPE TL_ROWID_RANGE AS TABLE OF TP_ROWID_RANGE;

-- 2. Function 생성
CREATE OR REPLACE FUNCTION FN_SPLIT_BY_ROWID(
    I_OWNER IN VARCHAR2, I_TABLE_NAME IN VARCHAR2, I_CHUNKS IN NUMBER)
RETURN TL_ROWID_RANGE
PIPELINED
AS
  CURSOR C_ROWID_RANGE (CP_OWNER VARCHAR2, CP_TABLE_NAME VARCHAR2, CP_CHUNKS NUMBER)
  IS
    SELECT GRP,
           DBMS_ROWID.ROWID_CREATE( 1, DATA_OBJECT_ID, LO_FNO, LO_BLOCK, 0 ) MIN_RID,
           DBMS_ROWID.ROWID_CREATE( 1, DATA_OBJECT_ID, HI_FNO, HI_BLOCK, 10000 ) MAX_RID
      FROM (
            SELECT DISTINCT GRP,
                   FIRST_VALUE(RELATIVE_FNO)
                    OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LO_FNO,
                   FIRST_VALUE(BLOCK_ID)
                   OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LO_BLOCK,
                   LAST_VALUE(RELATIVE_FNO)
                    OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HI_FNO,
                   LAST_VALUE(BLOCK_ID+BLOCKS-1)
                    OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID
                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HI_BLOCK,
                   SUM(BLOCKS) OVER (PARTITION BY GRP) SUM_BLOCKS
              FROM (
                    SELECT RELATIVE_FNO, BLOCK_ID, BLOCKS,
                           TRUNC( (SUM(BLOCKS) OVER (ORDER BY RELATIVE_FNO, BLOCK_ID)-0.01) /
                                  (SUM(BLOCKS) OVER ()/ CP_CHUNKS) ) GRP
                      FROM DBA_EXTENTS@DL_MS949
                     WHERE SEGMENT_NAME = UPPER(CP_TABLE_NAME)
                       AND OWNER = UPPER(CP_OWNER)
                     ORDER BY BLOCK_ID
                   )
           ),
           (SELECT DATA_OBJECT_ID
              FROM DBA_OBJECTS@DL_MS949
             WHERE OWNER = UPPER(CP_OWNER)
               AND OBJECT_NAME = UPPER(CP_TABLE_NAME))
     ORDER BY GRP
    ;

BEGIN
  FOR ROWID_RANGE IN C_ROWID_RANGE(I_OWNER, I_TABLE_NAME, I_CHUNKS) LOOP
      PIPE ROW(TP_ROWID_RANGE(ROWID_RANGE.MIN_RID, ROWID_RANGE.MAX_RID));
  END LOOP;
  RETURN;
END;
/
 

Function에서 사용하는 SQLDBA_EXTENTS를 기준으로 하는 Block 단위의 ROWID 분할이고, Thomas Kyte가 제시한 기법을 참조하여 DB Link를 사용하도록 약간 변형하였다.

(참조 URL: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10498431232211)

 

이 함수를 이용한 ROWID 분할의 예시는 다음과 같다.

-- DL_MS949 DB Link상의 LEG owner, SUB_MON_STAT table에 대해 4개의 Chunk ROWID 분할
SELECT START_ROWID, END_ROWID FROM TABLE(FN_SPLIT_BY_ROWID('LEG', 'SUB_MON_STAT', 4));

 

■ 실행결과

Row#

START_ROWID

END_ROWID

1

AAAQXFAAEAAAACIAAA

AAAQXFAAEAAAAC3CcQ

2

AAAQXFAAEAAAAC4AAA

AAAQXFAAEAAAADHCcQ

3

AAAQXFAAEAAAADIAAA

AAAQXFAAEAAAADXCcQ

4

AAAQXFAAEAAAADYAAA

AAAQXFAAEAAAADnCcQ

 

0단계: 테스트 환경 테스트 테이블 생성

Target DB <ORAUTF>에서 <DL_MS949> DB Link를 이용하여 Source DB <ORAMSWIN949><SUB_MON_STAT> table <SUB_MON_STAT_COPY>로 가져오는 테스트 시나리오를 가정하고, 다음의 환경 구성으로 진행한다.

 

clip_image002[6]

(Target DB에서 Source DB의 데이터를 가져오는 Pull 방식으로 진행)

 

Target DB<SUB_MON_STAT_COPY> table은 다음 DDL로 미리 생성한다.

CREATE TABLE SUB_MON_STAT_COPY
AS
SELECT USE_MON, LINE_NUM, SUB_STA_ID, SUB_STA_NM, RIDE_PASGR_NUM, ALIGHT_PASGR_NUM, WORK_DT
   FROM SUB_MON_STAT@DL_MS949
 WHERE 1=2;

 

1단계. 작업 생성

---------------------------------------------------------------------
-- 1단계: 작업생성
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)');
END;
/

 

-- 작업 생성 확인
SELECT * FROM USER_PARALLEL_EXECUTE_TASKS;
clip_image004[6]

 

 

2단계. 작업 단위 분할

FN_SPLIT_BY_ROWID 함수를 이용하여, 작업단위를 4로 지정/분할한다.

----------------------------------------------------------------------------------------------------
-- 2단계: 작업 단위 분할
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(
          TASK_NAME  => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)',
          SQL_STMT   => 'SELECT START_ROWID, END_ROWID FROM TABLE(FN_SPLIT_BY_ROWID(''LEG'', ''SUB_MON_STAT'', 4))',
          BY_ROWID   => TRUE);
END;
/
 

-- 작업 분할 상태 확인
SELECT * FROM USER_PARALLEL_EXECUTE_CHUNKS WHERE TASK_NAME = 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)';
clip_image006[6]

 

3단계. 작업 실행

ROWID 조건을 WHERE 절에 지정하여 작업을 실행한다. 여기에서는 작업의 수를 작업단위 개수와 동일하게 4로 지정하였다.

----------------------------------------------------------------------------------------------------
-- 3단계: 작업 실행
DECLARE
  L_SQL_STMT VARCHAR2(32767);
BEGIN
  L_SQL_STMT := 'INSERT INTO SUB_MON_STAT_COPY
                 SELECT USE_MON, LINE_NUM, SUB_STA_ID, SUB_STA_NM,RIDE_PASGR_NUM, ALIGHT_PASGR_NUM, WORK_DT
                   FROM SUB_MON_STAT@DL_MS949
                  WHERE ROWID BETWEEN :START_ID AND :END_ID';

  DBMS_PARALLEL_EXECUTE.RUN_TASK(TASK_NAME      => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)',
                                 SQL_STMT       => L_SQL_STMT,
                                 LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
                                 PARALLEL_LEVEL => 4);
END;
/

 

-- 작업 실행상황, 오류코드/메시지 확인
SELECT  *
  FROM  USER_PARALLEL_EXECUTE_CHUNKS
 WHERE  TASK_NAME = 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)';
clip_image008[6]

 

4단계. 작업 완료 확인 삭제

 

-- 작업 완료 확인
SELECT * FROM USER_PARALLEL_EXECUTE_TASKS;
clip_image010[6]

 

-- 4단계: 작업삭제
BEGIN
  DBMS_PARALLEL_EXECUTE.DROP_TASK(TASK_NAME => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)');
END;
/

Leave Comments


profile기술 너머의 통찰 

Recent Trackback