Microsoft SQL Server 관리 툴의 숨은 팁

SSMS 인텔리센스 기능과 코드 스니펫을 활용한 개발 속도 향상

이 글은 Oracle 11g R2의 new feature인 DBMS_PARALLEL_EXECUTE에 대해 소개하고 활용도를 높이고자 하는 목적으로 작성되었다.

하나의 DML 작업을 병렬로 실행할 때, 크게 두 가지 방법이 있다.

첫 번째는, 하나의 SQL을 실행하면서 parallel degree를 hint로 지정하는 방법(Parallel DML, single transaction)

두 번째는 데이터의 범위를 지정하여 n개의 SQL을 실행하는 방법(Program Parallel DML, multi transaction)

대부분의 경우에는 첫 번째 방법인 Parallel DML 방법이 single transaction으로 처리되므로 데이터 정합성면에서 유리하다. 하지만, 첫 번째 방법을 적용할 수 없는 상황이거나 작업 단위의 크기나 범위를 스스로 정의하고자 할 경우는 두 번째의 방법을 적용하는 것이 좋다. (그래서 두 번째 방법을 DIY(Do It Yourself) Parallel DML이라고도 한다)

Parallel DML을 적용할 수 없는 상황은 예를 들어 다음과 같다.

- LOB 컬럼을 포함한 테이블을 SELECT하는 DML
- DB Link상의 DML
- SQL단위가 아닌 PL/SQL 단위나 복잡한 절차로 구현된 Procedure 단위의 병렬 처리 DML

Program Parallel DML을 실행하는 단계는 상황에 따라 조금씩 다르겠지만, 일반적으로 다음과 같다.

1. 작업 대상 선정
2. 작업 단위 분할(Partition, 날짜, 숫자, ROWID 등) -> 작업 단위로 .sql 파일 생성
3. 작업 실행 및 모니터링 -> 각 .sql 파일을 별도의 sql*plus process에서 실행
4. 작업 완료 확인 -> Row count 비교, Sum 비교 등 검증

여기에서 병렬도(DOP, Degree Of Parallelism)를 변경하거나 재작업할 때 2, 3번 단계가 반복적으로 수행되고 관리와 확인이 간단치 않게 된다. 좀 더 잘 만들더라도, 변경시에 까다로운 것은 마찬가지이다.

두 번째 방법과 관련하여, Oracle 11g R2에서 new feature로 소개된 DBMS_PARALLEL_EXECUTE를 활용하면 이 작업을 좀 더 편리하게 실행, 관리, 확인할 수 있다. (Oracle Document: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_parallel_ex.htm#ARPLS233)

(참고로, DBMS_PARALLEL_EXECUTE는 내부적으로 JOB을 사용하므로 실행하는 user에게 CREATE JOB 권한을 부여해야 한다.)

Concept은 거의 동일하다. DBMS_PARALLEL_EXECUTE를 적용할 때의 간략한 단계는 다음과 같다.

1. 작업(Task) 생성 (CREATE_TASK)
2. 작업 단위(Chunk) 분할 (ROWID, NUMBER, SQL 세가지 방식 제공)
- CREATE_CHUNKS_BY_ROWID
- CREATE_CHUNKS_BY_NUMBER_COL
- CREATE_CHUNKS_BY_SQL
3. 작업 실행 (RUN_TASK)
4. 작업 완료 후 삭제 (DROP_TASK)

이중에서 ROWID에 의한 작업 단위 분할과 작업 실행의 실제 사례를 살펴보자. #테스트 환경: Oracle 11g R2 Enterprise 11.2.0.1.0 32 bit(On Windows 7 x64)

0단계: 테스트 테이블과 데이터 생성

먼저 다음 스크립트로 테스트할 테이블과 데이터를 생성한다.

0단계: 테스트 테이블과 데이터 생성
DROP TABLE Z_DPE_TEST_TAB PURGE;
CREATE TABLE Z_DPE_TEST_TAB (
ID NUMBER(10)
,MSG VARCHAR2(100)
,VAL NUMBER
,AUDSID NUMBER
) NOLOGGING;

100만건 테스트 데이터 생성
INSERT /*+ APPEND */ INTO Z_DPE_TEST_TAB (ID, MSG)
SELECT LEVEL AS ID
,'Test Data ID: ' || TO_CHAR(LEVEL)
FROM DUAL
CONNECT BY LEVEL < = 1000000;
COMMIT;
SELECT COUNT(*) FROM Z_DPE_TEST_TAB; -- 1,000,000
SELECT * FROM Z_DPE_TEST_TAB;

1단계. 작업 생성

CREATE_TASK()으로 작업을 생성한다.

참고로, task의 STATUS는 CHUNKED, CHUNKING, CHUNKING_FAILED, CRASHED, CREATED, FINISHED, FINISHED_WITH_ERROR, PROCESSING의 값을 가지며, 각 의미는 다음과 같다. (참조: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_parallel_ex.htm#ARPLS67333)

CHUNKED: chunk가 생성되고, 아직 작업에 할당되지 않음
CHUNKING: chunk 생성 중
CHUNKING_FAILED: chunk 생성 중 실패
CRASHED: Task 실행 중 데이터베이스 crash 또는 job process의 crash가 발생하여 error를 기록하지 못하고 종료됨
CREATED: Task가 생성됨(아직 chunk는 생성되지 않음)
FINISHED: 모든 chunk가 오류 없이 완료함
FINISHED_WITH_ERROR: 모든 chunk를 완료했으나, 일부 오류가 발생함
PROCESSING: Tosk가 실행을 시작했고, chunk의 일부는 진행 중이거나 완료상태임

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

작업 생성 확인
SELECT * FROM USER_PARALLEL_EXECUTE_TASKS;

위 결과에서 Task가 생성되었고, STATUS는 CREATED 임을 확인할 수 있다.

2단계. 작업 단위 분할

CREATE_CHUNKS_BY_ROWID(, , , , )로 작업 단위를 분할한다. 는 BOOLEAN type으로 TRUE이면 가 Row Count를 의미하고, FALSE이면 Block Count를 의미한다.

2단계: 작업 단위 분할
BEGIN
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(TASK_NAME => 'DPE_TEST(BY ROWID)',
TABLE_OWNER => USER,
TABLE_NAME => 'Z_DPE_TEST_TAB',
BY_ROW => TRUE,
CHUNK_SIZE => 10000);
END;
/

작업 분할 상태 확인
SELECT * FROM USER_PARALLEL_EXECUTE_CHUNKS WHERE TASK_NAME = 'DPE_TEST(BY ROWID)';

분할된 시점의 각 chunk의 STATUS가 UNASSIGNED로 되어 있음을 확인할 수 있다. 참고로, chunk의 STATUS는 UNASSIGNED, ASSIGNED, PROCESSED, PROCESSED_WITH_ERROR 의 값을 가지며, 각 의미는 다음과 같다. (참조: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_parallel_ex.htm#ARPLS67333)

UNASSIGNED: chunk가 생성되고, 아직 작업에 할당되지 않음
ASSIGNED: chunk가 작업에 할당되어 실행중임
PROCESSED: 작업이 오류 없이 완료되었음
PROCESSED_WITH_ERROR: 작업이 완료되었으나 실행 중 오류가 발생함

3단계. 작업 실행

RUN_TASK(, , , )로 작업을 실행한다. 여기에서 LANGUAGE_FLAG는 Oracle이 SQL_STMT를 처리할 기준 version의 의미이며, default로 DBMS_SQL.NATIVE 를 사용하면 된다. 참고로, Oracle Document의 내용은 다음과 같다.

V6 (or 0) specifies version 6 behavior
NATIVE (or 1) specifies normal behavior for the database to which the program is connected
V7 (or 2) specifies Oracle database version 7 behavior

PARALLEL_LEVEL은 동시에 실행할 작업(job)의 개수, 즉 병렬도(DOP, Degree Of Parallelism)를 의미하며, 작업 단위인 chunk의 개수와 같을 수도 있고, 작을 수도 있다. 같은 경우는 하나의 job이 하나의 chunk를 처리하고, 작은 경우는 하나의 job이 여러 개의 chunk를 처리하게 된다.

동시 실행현황 확인
SELECT AUDSID, COUNT(*) FROM Z_DPE_TEST_TAB GROUP BY AUDSID ORDER BY AUDSID;

작업 실행 전에 AUDSID별로 COUNT를 확인해 보면, 모든 데이터가 null로 설정되어, 아직 실행되지 않았음을 확인할 수 있다.

3단계: 작업 실행
DECLARE
L_SQL_STMT VARCHAR2(32767);
BEGIN
L_SQL_STMT := 'UPDATE Z_DPE_TEST_TAB
SET VAL = ROUND(DBMS_RANDOM.VALUE(1,10000))
,AUDSID = SYS_CONTEXT(''USERENV'',''SESSIONID'')
WHERE ROWID BETWEEN :START_ID AND :END_ID';
DBMS_PARALLEL_EXECUTE.RUN_TASK(TASK_NAME => 'DPE_TEST(BY ROWID)',
SQL_STMT => L_SQL_STMT,
LANGUAGE_FLAG => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 10);
END;
/

동시 실행현황 확인
SELECT AUDSID, COUNT(*) FROM Z_DPE_TEST_TAB GROUP BY AUDSID ORDER BY AUDSID;

위 결과에서 동시에 job이 실행되면서 서로 다른 SESSIONID(V$SESSION.AUDSID)값을 가지고 실행됨을 확인할 수 있다.

작업의 Chunk별 진행상황/완료 확인
SELECT * FROM USER_PARALLEL_EXECUTE_CHUNKS WHERE TASK_NAME = 'DPE_TEST(BY ROWID)';

위 결과에서 STATUS가 PROCESSED로 변경되고, START_TS(시작시각), END_TS(종료시각)가 관리됨을 확인할 수 있다.

Chunk의 상태별 진행상황
SELECT STATUS, COUNT(*) FROM USER_PARALLEL_EXECUTE_CHUNKS
WHERE TASK_NAME = 'DPE_TEST(BY ROWID)' GROUP BY STATUS;

위 결과에서 172개 chunk에 대해 PROCESSED 상태로 완료되었음을 확인할 수 있다.

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

DROP_TASK()로 작업을 삭제한다. 참고로, 삭제하지 않았을 경우, 나중에 동일한 TASK_NAME으로 작업을 생성할 때 ORA-29497: 중복된 작업 이름(DUPLICATE_TASK_NAME) 오류가 발생한다.

작업 완료 확인
SELECT * FROM USER_PARALLEL_EXECUTE_TASKS;

위 결과에서 Task의 STATUS가 FINISHED로 완료되었음을 확인할 수 있다.

4단계: 작업 완료 확인 및 작업 삭제
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK(TASK_NAME => 'DPE_TEST(BY ROWID)');
END;
/

DROP_TASK 실행 후 USER_PARALLEL_EXECUTE_TASKS view를 다시 확인해 보면 작업이 삭제되었음을 확인할 수 있다.

여기까지 ROWID에 의한 작업 단위 분할과 작업 실행의 간단한 사례를 살펴보았다. 다음에는 NUMBER 값에 의한 작업 단위 분할과 사용자 정의 SQL을 통한 작업 단위 분할의 사례를 살펴보고, 발생가능한 오류와 이에 대한 처리를 살펴볼 예정이다.