SQL> desc hddrv_cus_sgn
Name Null? Type
----------- -------- --------------------------------------------------------
RCS_WBL_NUM NOT NULL VARCHAR2(14)
RCS_RLT_COD VARCHAR2(2)
RCS_SGN_IMG BLOB
RCS_SGN_YMD DATE
RCS_DLV_ENO VARCHAR2(7)
RCS_CFM_CND VARCHAR2(1)
RCS_BAT_CND VARCHAR2(1)
CREATE OR REPLACE FUNCTION fn_get_cus_sign2(wbl_num IN VARCHAR2) RETURN LONG raw AS
rbuf LONG raw(2000);
len NUMBER(7);
tmp VARCHAR2(4000);
hb BLOB;
BEGIN
SELECT rcs_sgn_img
INTO hb
FROM rtsrv_cus_sgn
WHERE rcs_wbl_num = wbl_num;
len := dbms_lob.getlength(hb);
dbms_lob.READ(hb, len, 1, rbuf);
RETURN rbuf;
EXCEPTION
WHEN others THEN
rbuf := hextoraw('47494638396108000100800000FFFFFF00000021F90400000000002C0000000008000100000203846F05003B');
RETURN rbuf;
END fn_get_cus_sign2;
2009년 2월 24일 화요일
bulk and forall sample
DECLARE
CURSOR c1 IS
SELECT
RCS_WBL_NUM,
RCS_RLT_COD,
-- RCS_SGN_IMG,
RCS_SGN_YMD,
RCS_DLV_ENO,
RCS_CFM_CND,
RCS_BAT_CND
FROM HDDRV_CUS_SGN
WHERE RCS_SGN_YMD BETWEEN SYSDATE - 210 AND SYSDATE - 200
AND ROWNUM <= 10000;
TYPE t_RCS_WBL_NUM IS TABLE OF HDDRV_CUS_SGN.RCS_WBL_NUM%TYPE;
TYPE t_RCS_RLT_COD IS TABLE OF HDDRV_CUS_SGN.RCS_RLT_COD%TYPE;
-- TYPE t_RCS_SGN_IMG IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_IMG%TYPE;
TYPE t_RCS_SGN_YMD IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_YMD%TYPE;
TYPE t_RCS_DLV_ENO IS TABLE OF HDDRV_CUS_SGN.RCS_DLV_ENO%TYPE;
TYPE t_RCS_CFM_CND IS TABLE OF HDDRV_CUS_SGN.RCS_CFM_CND%TYPE;
TYPE t_RCS_BAT_CND IS TABLE OF HDDRV_CUS_SGN.RCS_BAT_CND%TYPE;
x_RCS_WBL_NUM t_RCS_WBL_NUM ;
x_RCS_RLT_COD t_RCS_RLT_COD ;
-- x_RCS_SGN_IMG t_RCS_SGN_IMG ;
x_RCS_SGN_YMD t_RCS_SGN_YMD ;
x_RCS_DLV_ENO t_RCS_DLV_ENO ;
x_RCS_CFM_CND t_RCS_CFM_CND ;
x_RCS_BAT_CND t_RCS_BAT_CND ;
inx1 PLS_INTEGER;
v_InsertStmt VARCHAR2(2000);
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO
x_RCS_WBL_NUM,
x_RCS_RLT_COD,
-- x_RCS_SGN_IMG,
x_RCS_SGN_YMD,
x_RCS_DLV_ENO,
x_RCS_CFM_CND,
x_RCS_BAT_CND;
CLOSE c1;
v_InsertStmt := 'INSERT INTO HDDRV_CUS_SGN_TRG(RCS_WBL_NUM,RCS_RLT_COD,RCS_SGN_IMG,RCS_SGN_YMD,RCS_DLV_ENO,RCS_CFM_CND,RCS_BAT_CND) VALUES (:1, :2, :3, :4, :5, :6, :7)';
FORALL x IN x_RCS_WBL_NUM.FIRST..x_RCS_WBL_NUM.LAST
INSERT INTO HDDRV_CUS_SGN_TRG(RCS_WBL_NUM,RCS_RLT_COD,RCS_SGN_IMG,RCS_SGN_YMD,RCS_DLV_ENO,RCS_CFM_CND,RCS_BAT_CND)
VALUES (x_RCS_WBL_NUM(x),x_RCS_RLT_COD(x),fn_get_cus_sign2@hddrts (x_RCS_WBL_NUM(x)),x_RCS_SGN_YMD(x),x_RCS_DLV_ENO(x),x_RCS_CFM_CND(x),x_RCS_BAT_CND(x));
END;
/
CURSOR c1 IS
SELECT
RCS_WBL_NUM,
RCS_RLT_COD,
-- RCS_SGN_IMG,
RCS_SGN_YMD,
RCS_DLV_ENO,
RCS_CFM_CND,
RCS_BAT_CND
FROM HDDRV_CUS_SGN
WHERE RCS_SGN_YMD BETWEEN SYSDATE - 210 AND SYSDATE - 200
AND ROWNUM <= 10000;
TYPE t_RCS_WBL_NUM IS TABLE OF HDDRV_CUS_SGN.RCS_WBL_NUM%TYPE;
TYPE t_RCS_RLT_COD IS TABLE OF HDDRV_CUS_SGN.RCS_RLT_COD%TYPE;
-- TYPE t_RCS_SGN_IMG IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_IMG%TYPE;
TYPE t_RCS_SGN_YMD IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_YMD%TYPE;
TYPE t_RCS_DLV_ENO IS TABLE OF HDDRV_CUS_SGN.RCS_DLV_ENO%TYPE;
TYPE t_RCS_CFM_CND IS TABLE OF HDDRV_CUS_SGN.RCS_CFM_CND%TYPE;
TYPE t_RCS_BAT_CND IS TABLE OF HDDRV_CUS_SGN.RCS_BAT_CND%TYPE;
x_RCS_WBL_NUM t_RCS_WBL_NUM ;
x_RCS_RLT_COD t_RCS_RLT_COD ;
-- x_RCS_SGN_IMG t_RCS_SGN_IMG ;
x_RCS_SGN_YMD t_RCS_SGN_YMD ;
x_RCS_DLV_ENO t_RCS_DLV_ENO ;
x_RCS_CFM_CND t_RCS_CFM_CND ;
x_RCS_BAT_CND t_RCS_BAT_CND ;
inx1 PLS_INTEGER;
v_InsertStmt VARCHAR2(2000);
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO
x_RCS_WBL_NUM,
x_RCS_RLT_COD,
-- x_RCS_SGN_IMG,
x_RCS_SGN_YMD,
x_RCS_DLV_ENO,
x_RCS_CFM_CND,
x_RCS_BAT_CND;
CLOSE c1;
v_InsertStmt := 'INSERT INTO HDDRV_CUS_SGN_TRG(RCS_WBL_NUM,RCS_RLT_COD,RCS_SGN_IMG,RCS_SGN_YMD,RCS_DLV_ENO,RCS_CFM_CND,RCS_BAT_CND) VALUES (:1, :2, :3, :4, :5, :6, :7)';
FORALL x IN x_RCS_WBL_NUM.FIRST..x_RCS_WBL_NUM.LAST
INSERT INTO HDDRV_CUS_SGN_TRG(RCS_WBL_NUM,RCS_RLT_COD,RCS_SGN_IMG,RCS_SGN_YMD,RCS_DLV_ENO,RCS_CFM_CND,RCS_BAT_CND)
VALUES (x_RCS_WBL_NUM(x),x_RCS_RLT_COD(x),fn_get_cus_sign2@hddrts (x_RCS_WBL_NUM(x)),x_RCS_SGN_YMD(x),x_RCS_DLV_ENO(x),x_RCS_CFM_CND(x),x_RCS_BAT_CND(x));
END;
/
bulk sample
DECLARE
rbuf RAW(2000);
CURSOR c1 IS
SELECT
RCS_WBL_NUM,
RCS_RLT_COD,
-- RCS_SGN_IMG,
RCS_SGN_YMD,
RCS_DLV_ENO,
RCS_CFM_CND,
RCS_BAT_CND
FROM HDDRV_CUS_SGN
WHERE RCS_SGN_YMD BETWEEN SYSDATE - 210 AND SYSDATE - 200
AND ROWNUM <= 10000;
TYPE t_RCS_WBL_NUM IS TABLE OF HDDRV_CUS_SGN.RCS_WBL_NUM%TYPE;
TYPE t_RCS_RLT_COD IS TABLE OF HDDRV_CUS_SGN.RCS_RLT_COD%TYPE;
-- TYPE t_RCS_SGN_IMG IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_IMG%TYPE;
TYPE t_RCS_SGN_YMD IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_YMD%TYPE;
TYPE t_RCS_DLV_ENO IS TABLE OF HDDRV_CUS_SGN.RCS_DLV_ENO%TYPE;
TYPE t_RCS_CFM_CND IS TABLE OF HDDRV_CUS_SGN.RCS_CFM_CND%TYPE;
TYPE t_RCS_BAT_CND IS TABLE OF HDDRV_CUS_SGN.RCS_BAT_CND%TYPE;
x_RCS_WBL_NUM t_RCS_WBL_NUM ;
x_RCS_RLT_COD t_RCS_RLT_COD ;
-- x_RCS_SGN_IMG t_RCS_SGN_IMG ;
x_RCS_SGN_YMD t_RCS_SGN_YMD ;
x_RCS_DLV_ENO t_RCS_DLV_ENO ;
x_RCS_CFM_CND t_RCS_CFM_CND ;
x_RCS_BAT_CND t_RCS_BAT_CND ;
inx1 PLS_INTEGER;
v_InsertStmt VARCHAR2(2000);
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO
x_RCS_WBL_NUM,
x_RCS_RLT_COD,
-- x_RCS_SGN_IMG,
x_RCS_SGN_YMD,
x_RCS_DLV_ENO,
x_RCS_CFM_CND,
x_RCS_BAT_CND;
CLOSE c1;
v_InsertStmt := 'INSERT INTO HDDRV_CUS_SGN_TRG(RCS_WBL_NUM,RCS_RLT_COD,RCS_SGN_IMG,RCS_SGN_YMD,RCS_DLV_ENO,RCS_CFM_CND,RCS_BAT_CND) VALUES (:1, :2, :3, :4, :5, :6, :7)';
FOR x IN x_RCS_WBL_NUM.FIRST..x_RCS_WBL_NUM.LAST LOOP
get_cus_sign2@hddrts (x_RCS_WBL_NUM(x), rbuf);
EXECUTE IMMEDIATE v_InsertStmt USING IN x_RCS_WBL_NUM(x),
x_RCS_RLT_COD(x),
-- x_RCS_SGN_IMG(x),
rbuf,
x_RCS_SGN_YMD(x),
x_RCS_DLV_ENO(x),
x_RCS_CFM_CND(x),
x_RCS_BAT_CND(x);
END LOOP;
END;
/
rbuf RAW(2000);
CURSOR c1 IS
SELECT
RCS_WBL_NUM,
RCS_RLT_COD,
-- RCS_SGN_IMG,
RCS_SGN_YMD,
RCS_DLV_ENO,
RCS_CFM_CND,
RCS_BAT_CND
FROM HDDRV_CUS_SGN
WHERE RCS_SGN_YMD BETWEEN SYSDATE - 210 AND SYSDATE - 200
AND ROWNUM <= 10000;
TYPE t_RCS_WBL_NUM IS TABLE OF HDDRV_CUS_SGN.RCS_WBL_NUM%TYPE;
TYPE t_RCS_RLT_COD IS TABLE OF HDDRV_CUS_SGN.RCS_RLT_COD%TYPE;
-- TYPE t_RCS_SGN_IMG IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_IMG%TYPE;
TYPE t_RCS_SGN_YMD IS TABLE OF HDDRV_CUS_SGN.RCS_SGN_YMD%TYPE;
TYPE t_RCS_DLV_ENO IS TABLE OF HDDRV_CUS_SGN.RCS_DLV_ENO%TYPE;
TYPE t_RCS_CFM_CND IS TABLE OF HDDRV_CUS_SGN.RCS_CFM_CND%TYPE;
TYPE t_RCS_BAT_CND IS TABLE OF HDDRV_CUS_SGN.RCS_BAT_CND%TYPE;
x_RCS_WBL_NUM t_RCS_WBL_NUM ;
x_RCS_RLT_COD t_RCS_RLT_COD ;
-- x_RCS_SGN_IMG t_RCS_SGN_IMG ;
x_RCS_SGN_YMD t_RCS_SGN_YMD ;
x_RCS_DLV_ENO t_RCS_DLV_ENO ;
x_RCS_CFM_CND t_RCS_CFM_CND ;
x_RCS_BAT_CND t_RCS_BAT_CND ;
inx1 PLS_INTEGER;
v_InsertStmt VARCHAR2(2000);
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO
x_RCS_WBL_NUM,
x_RCS_RLT_COD,
-- x_RCS_SGN_IMG,
x_RCS_SGN_YMD,
x_RCS_DLV_ENO,
x_RCS_CFM_CND,
x_RCS_BAT_CND;
CLOSE c1;
v_InsertStmt := 'INSERT INTO HDDRV_CUS_SGN_TRG(RCS_WBL_NUM,RCS_RLT_COD,RCS_SGN_IMG,RCS_SGN_YMD,RCS_DLV_ENO,RCS_CFM_CND,RCS_BAT_CND) VALUES (:1, :2, :3, :4, :5, :6, :7)';
FOR x IN x_RCS_WBL_NUM.FIRST..x_RCS_WBL_NUM.LAST LOOP
get_cus_sign2@hddrts (x_RCS_WBL_NUM(x), rbuf);
EXECUTE IMMEDIATE v_InsertStmt USING IN x_RCS_WBL_NUM(x),
x_RCS_RLT_COD(x),
-- x_RCS_SGN_IMG(x),
rbuf,
x_RCS_SGN_YMD(x),
x_RCS_DLV_ENO(x),
x_RCS_CFM_CND(x),
x_RCS_BAT_CND(x);
END LOOP;
END;
/
prf 통계정보 용어
parse
SQL문이 파싱되는 단계에 대한 통계입니다. 새로 파싱을 했거나,
공유 풀에서 찾아 온 것도 포함됩니다. 단, PL/SQL 내에서
반복 수행(Loop)된 SQL이나 PL*SQL에서 보존커서(Hold cursor)를
지정한 경우에는 한번만 파싱 됩니다.
execute
SQL문의 실행 단계에 대한 통계입니다.
UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타나게 됩니다.
전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이
나타나며 fetch에는 아주 적은 값이 나타납니다
fetch
SQL문이 실행되면서 fetch된 통계입니다.
부분범위 방식으로 처리된 SELECT문들이나 전체범위 처리를 한 후 한 건을
추출하는 경우(AGGREGATE, 전체집계, Count 등)는 주로 여기에 많은
값들이 나타나고 execute에는 아주 적은 값이 나타납니다.
count
SQL문이 파싱된 횟수, 실행된 횟수, fetch가 수행된 횟수입니다.
cpu
pares, execute, fetch가 실제로 사용한 CPU 시간입니다.(1/100초 단위)
elapsed
작업의 시작에서 종료시까지 실제 소요된 총 시간입니다.
disk
디스크에서 읽혀진 데이타 블록의 수
query
메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해
변경 되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을
읽은 블록의 수입니다. SELECT문에서는 거의가 여기에 해당하며 UPDATE,
DELETE, INSERT 시에는 소량만 발생됩니다.
current
현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한
블록(Dirty Block)을 액세스한 블록 수 입니다. 주로 UPDATE, INSERT,
DELETE 작업시 많이 발생합니다.
SELECT 문에서는 거의 없으나 아주 적은 양인 경우가 대부분입니다.
rows
SQL문을 수행한 결과에 의해 최종적으로 액세스된 row의 수입니다.
서브쿼리에 의해서 추출된 row는 제외됩니다. ·만약 SUM, AVG, MAX, MIN,
COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없습니다.
SQL문이 파싱되는 단계에 대한 통계입니다. 새로 파싱을 했거나,
공유 풀에서 찾아 온 것도 포함됩니다. 단, PL/SQL 내에서
반복 수행(Loop)된 SQL이나 PL*SQL에서 보존커서(Hold cursor)를
지정한 경우에는 한번만 파싱 됩니다.
execute
SQL문의 실행 단계에 대한 통계입니다.
UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타나게 됩니다.
전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이
나타나며 fetch에는 아주 적은 값이 나타납니다
fetch
SQL문이 실행되면서 fetch된 통계입니다.
부분범위 방식으로 처리된 SELECT문들이나 전체범위 처리를 한 후 한 건을
추출하는 경우(AGGREGATE, 전체집계, Count 등)는 주로 여기에 많은
값들이 나타나고 execute에는 아주 적은 값이 나타납니다.
count
SQL문이 파싱된 횟수, 실행된 횟수, fetch가 수행된 횟수입니다.
cpu
pares, execute, fetch가 실제로 사용한 CPU 시간입니다.(1/100초 단위)
elapsed
작업의 시작에서 종료시까지 실제 소요된 총 시간입니다.
disk
디스크에서 읽혀진 데이타 블록의 수
query
메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해
변경 되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을
읽은 블록의 수입니다. SELECT문에서는 거의가 여기에 해당하며 UPDATE,
DELETE, INSERT 시에는 소량만 발생됩니다.
current
현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한
블록(Dirty Block)을 액세스한 블록 수 입니다. 주로 UPDATE, INSERT,
DELETE 작업시 많이 발생합니다.
SELECT 문에서는 거의 없으나 아주 적은 양인 경우가 대부분입니다.
rows
SQL문을 수행한 결과에 의해 최종적으로 액세스된 row의 수입니다.
서브쿼리에 의해서 추출된 row는 제외됩니다. ·만약 SUM, AVG, MAX, MIN,
COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없습니다.
일괄 처리 방식
I. 개 요
- ORACLE PL/SQL에서 일괄DATA 처리방식에 대한 방식에 알아본다
- 각 방식의 장단점과 시스템 TEST결과를 비교 분석하여 최적의 솔루션을 찾아본다.
- ORACLE에서 일괄로 DATA를 처리하는 방식은 크게 5가지로 구분할 수 있다.
CASE1 : CURSOR FOR LOOP 방식
CASE2 : BULK COLLECTION 방식
CASE3 : BULK COLLECTION & FORALL문을 이용한 방식
CASE4 : TEMP TABLE 방식
CAES5 : 단일SQL을 통한 집합처리 방식
그럼 새부적으로 하나씩 관련 소스와 테스트 결과를 분석 해보도록 하겠습니다.
II. 일괄처리방식 분석
(1) CUROSR FOR LOOP 방식
- 고전적인 절차적 방식에 의한 순차적 DML문을 사용하는 방식
- 일괄처리 방식을 PL/SQL로 절차적 방식만 사용할경우 성능에 심각한 문제발생 가능성 존재
<개발 시나리오>
PL./SQL문법책을 보면 제일 앞부분에 나오는게 CURSOR 입니다. 아.. 오라클에서도 절차적 언어를
지원하는구나... IF도 있고. FOR도 있고..JAVA나 C#만큼의 패키지는 지원하지 않겠지만 그래도 이정도면
웬만한 프로그램은 짜겠는걸?
이렇게 생각한 저는 1만건의 쿼리를 커서를 지정하여 하나씩 Fetch하면서 Looping처리를 합니다. 루핑
안에는 각종 IF문과 다른 두 개 테이블 Select문을 넣고 해당 결과가 있으면 변수에 넣고 이런식으로
프로그램 을 짜게 됩니다.
이 경우의 DB 부하의 상당부분을 차지하는 DBMS Call 횟수를 간단히 계산해 보면
DBMS Call 횟수 = Cursor선언 + Rows Fetch건수 + Looping내 Select(건수) * Fetch건수
= 1 + 10000 + (2 * 10000) = 30,001
즉, 이러한 프로시져를 돌리면 한번 실행에 DBMS는 3만번의 DBMS CALL을 수행하는 꼴이 됩니다.
이러한 프로시져를 100명이 동시에 돌린다고 생각해 보십시요... ㅠ.ㅠ
<실전 TEST>
위와 같은 프로시져를 만들어 만건의 테이블을 커서로 패치해서 루핑안에 다른 테이블에 Insert/Update
하는 프로시져입니다. (여기서 Merge Into문은 해당 테이블에 Insert/Update여부를 내부적으로 Outer Join
방식에 의해 스스로 판단해서 처리해주는 문장입니다. 즉 1000건중에 900건은 Insert 100은 Update 이런
식으로요..시간이 되면 따로 이 문장에대한 내용도 올리도록 하겠습니다. - 사용시 주의가 요구된답니다. ^^)
결과는 아래와 같습니다.
===================================================
# ALL INSERT : 1.594 seconds (10000 rows INSERT)
# ALL UPDATE : 110.797 seconds (10000 rows UPDATE)
===================================================
다음번에는 BULK COLLECTION 방식에 대한 내용을 올리도록 하겠습니다.
(2) BULK COLLECT BINDING 사용을 통한 대용량 처리
- COLLECTION을 LOOP FETCH방식이 아닌 BULK COLLECT 방식으로 일괄 FETCH
- PL/SQL과 SQL엔진 사이의 문맥전환수를 줄여주고 데이터 조회 오버해드를 대폭 줄여준다
- PL/SQL 루프를 사용한 한행 패치보다 훨씬 빠름
음.. BULK? COLLECTION? 생소한 용어입니다.. 저한테는... 뭔가 배열스러운 곳에 임시로 넣는다 뭐 이런
비스무리한 거 같긴 한데... "문맥전환" 이건 또 뭔가요?
일단 아래 소스를 보시죠..
> TYPE AWB_REC IS TABLE OF XXXXX%ROWTYPE INDEX BY BINARY_INTEGER;
일단 ROWTYPE으로 배열선언을 했군요...
> SELECT * BULK COLLECT INTO R_REC .....
새로운 구문이 나왔습니다 "BULK COLLECT INTO" 이것은 해당 결과를 배열에 일괄로 넣는 것입니다.
즉 SELECT문이 1,000건이면 1,000건이 한방에 배열로 들어간다는 것이죠.. 괜찮아 보이네요..
> FOR inx1 IN 1..R_REC.COUNT LOOP....END LOOP
해당배열을 가지고 루핑을 돌면서 어떤 처리를 할때 쓰입니다.
음.. 일단 패치에 대한 부하는 없겠군요.. 왜나하면 한방에 배열에 패치가 되었기 때문에..
만약 루핑속에 두개의 SELECT문을 돌리는 문장을 구성한다면
DBMS_CALL횟수 = 커서선언(1) + Fetch(1) + (SELECT (2)) * 배열.COUNT
= 1 + 1 + 2*10,000 = 20,002번
오.. LOOP방식보다 DBMS_CALL이 그래도 많이 줄었습니다.
아래 결과에서도 INSERT와 UPDATE가 LOOP보다는 상대적으로 빠르게 나왔습니다.
===================================================
# ALL INSERT : 1.469 seconds (10000 rows INSERT)
# ALL UPDATE : 106.672 seconds (10000 rows UPDATE)
===================================================
(3) BULK COLLECT & FOR ALL문 사용을 통한 대용량 처리
- PL/SQL에서 Array Processing을 지원하는 방법 (집합적 처리 방법)
- FORALL을 사용할 때, 문장은 모음의 각 항목에 대해 한번씩 실행 하지만 PL/SQL에서 SQL로의 문맥전환은 오직 한번
- FORALL문은 COMPOSITE TYP(%ROWTYPE)의 필드를 직접 핸들링 할 수 없다 . 이것은 매우 불편한 제약사항 (하지만 11g에서는 가능하다고 합니다...)
COMPOSITE TYPE이란 ORACLE에서 ROWTYPE등과 같이 여러개의 타입을 가지는 배열 정도로 생각하시면 되겠습니다. 즉 PL.SQL에서는 ROWTYPE.컬럼이름.. 이렇게 접근이 가능한데...
FORALL에서는 이게 안된다는 말입니다.. 그럼..어떻게 하란 말이지?
결국 10g 이하 버전에서는 각 컬럼 타입별로 별도 배열을 만들던지, 아니면 ROWTYPE과 같은 컬럼을 가지는 RECODE OBJECT를 만들어야 합니다. (구조체 처럼) 굉장이 불편해 보이네요... 하지만 컬럼이 얼마 안된다면 감수할 만 한듯 합니다.
문맥전환이란 ? : PL/SQL블럭에 포함되어 있는 SQL문은 SQL 파싱엔진에서 별도 처리해서 PL/SQL문 실행기로 넘기게 되는데 이런 흐름을 문맥전환이라 한다.
음 FOR ALL이란 새로운게 나왔습니다.. 추측해 보건데.. 뭔가 한방에 처리를 하는 듯 한거 같은데 이것도 역시 소스를 먼저 보시죠.
> FORALL I IN R_AWB_MNGNO.FIRST..R_AWB_MNGNO.LAST SAVE EXCEPTIONS
: 이문장은 아래에 나오는 MERGE INTO를 단 한번의 문맥전환 처리로 한번에 처리하게 합니다.
1000건을 한번에 INSERT/UPDATE 한다는 말이죠.. 1건씩이 아닌..
> FETCH CR1 BULK COLLECT INTO R_AWB_MNGNO, .... LIMIT 1000;
: 아까 위에서 나온 벌크 바인딩인데..뒤에 LIMIT라는게 붙었네요.. 이것은 1000건 까지만 바인딩 하라는 말입니다. 즉 10,000건을 1000건씩 나눠 처리하는 겁니다.
실행결과는 아래와 같습니다.
===================================================
# ALL INSERT : 0.922 seconds (10000 rows INSERT PER 1000)
# ALL UPDATE : 1.672 seconds (10000 rows UPDATE PER 1000)
===================================================
허걱... 이건 충격에 가깝습니다.. 전에 있었던 LOOP방식과 비교해서 굉장히
빠른 속도를 보여주었습니다. 수치상으론 100배인데... 문맥전환의 오버해드가 이렇게
클 줄은...
결국은 LOOPING처리 할때 DBMS CALL + 문맥전환의 오버해드가 시스템 부하의 주요 원인이었음을 추측해 볼수 있겠습니다.
휴.. 쓰다보니 30분이 넘었네요..다음 시간에는 ORACLE에서 제공하는 TEMP_TABLE을 이용한 대용량 처리에 대해 이야기 해보도록 하겠습니다.
(4) Temp Table
- 세션내에 임시로 사용할 data들을 영구적인 segment형태가 아닌 temporary structure에서 관리 생성문장에 의해서 definition이 dictionary에 저장
- 각 세션에서 해당 table을 사용할 때마다 definition을 이용 memory에 table 구조를 생성
- 임시 테이블은 실제로 테이블에 데이터가 추가되기 전까지는 공간을 할당받지 않음
- 데이터를 보관하기 위한 공간으로 일반적인 객체를 위한 데이터 저장 공간이 임시 테이블스페이스 이용
위의 TempTable정의를 좀 자세히 보도록 하겠습니다.
일단. "세션내에서 임시로 사용" 이라는 말이 나오는데 즉 세션별로 임시테이블을 만들어 사용할 수 있다는 의미 입니다. 그리고 TempTable은 Heap Table (일반 테이블) 과 달리 Create문을 사용해서 생성시
데이터 추가되기 전까지는 공간을 할당 받지 않습니다.
우리가 일반 테이블 생성시에는 데이터가 아무것도 없더라도, 오라클은 일정한 연속된 블럭(Block) 즉, 익스텐트를 바로 할당해 주게 됩니다. 하지만 TempTable은 데이터가 들어와야만 할당해 주는군요. 그리고 메모리에 테이블 구조를 생성하게 된다는 것도 매우 중요합니다. 어딘가에 숨어있다가, 우리가 그 테이블에 Insert할 때 나타나서 테이블을 구성해 준다 뭐 이정도로 이해 해도 큰 무리 없을듯 합니다.
일단 소스 보시죠..
먼저 TempTable 생성 문장부터 보겠습니다
(1) 생성문장
> CREATE GLOBAL TEMPORARY TABLE TMP_COM_AWB(........) ON COMMIT DELETE ROWS;
또는
> CREATE GLOBAL TEMPORARY TABLE MYTEMP [ON COMMIT PRESERVE ROWS / ON COMMIT
DELETE ROWS] as select * from <테이블명> where 1=0
(1=0은 부정형 조건이므로 false가 되어 실제로 들어가는 데이터는 없고 테이블 스펙만 만들짐)
(2) 옵션사용
'ON COMMIT DELETE ROWS' : transaction단위이며 이때 data는 commit이 되는 시점에 자동 제거
'ON COMMIT PRESERVE ROWS' : session단위이며 이때 data는 해당 session이 종료되면 제거.
default는 'ON COMMIT DELETE ROWS'
아.. 이제 좀 감이 잡히시나요? 결국 TempTable은 자신의 세션내에서만 유효하고 (즉 다른세션에서는
볼수가 없네요) 옵션에 따라. 하나의 트랜잭션내에서 사용할껀지 세션을 유지하는 내내 사용하다가
세션을 끊으면 삭제할 것인지를 선택할 수 있다는 뜻입니다.
(3) 특징 및 주요 사항
사용시 주의사항이나 특징에 대해 알아보도록 하겠습니다.
1. 세션간의 Data에 대한 동시제어에 의한 충돌이 없음 (당연한거 아닌가?)
2. 임시적 관리되는 Data이므로 Redo Log를 발생시키지 않습니다. (즉 장애시 복구가 불가능하겟네요)
3. Index, view Trigger를 생성하여 사용할 수 있다. (일반 테이블과 똑같네요..)
4. Table 정의를 Export 할수 있으나 그 데이터는 Export할 수 없다. (이것도 당연한 듯)
5. foreign key constraint를 설정할 수 없다.
6. parallel DML이나 parallel query는 지원되지 않는다.
7. storage나 tablespace는 지정할 수 없다.
8. 분산 transaction은 지원되지 않는다. (중요합니다)
실행 결과는
===================================================
# ALL INSERT : 0.75 seconds (10000 rows INSERT)
# ALL UPDATE : 0.359 seconds (10000 rows UPDATE)
===================================================
오호.. 이것 역시 굉장이 빠릅니다...
속도 하나는 정말 매력적이네요.
문맥전환 오버헤드도 없고, Fetch에 의한 DBMS_CALL도 없고, 그리고 메모리 테이블이니...
하지만 너무 큰 데이터를 메모리에 올린다면.. 이건 쫌 그렇네요.. 또는 여러사람이 돌리는 프로그램에 이런 문장이 있으면 모든 Session별로 임시 테이블이 생길수도 있을 것이고..
즉. 단독으로 수행되는 Batch JOB에 5천건 이하 정도의 처리에 쓰면 정말 최강일듯 합니다.
(5) 단일 SQL을 통한 집합 처리
- 한건처리하는 SQL을 10,000번 호출하는 방식과 하나의 SQL로 10,000건을 범위 처리하는 것은 결과는 같지만 성능적인 차이는 10,000 배 차이가 남
- OPTIMIZER가 최적화된 실행계획을 수행하는 단위는 SQL임
- APPLICATION LVL의 SQL 사용은 유지보수, 단순, 생산성 향상을 보장함
저번 시간에서도 이야기 했지만.. DBMS에 부하를 주는 중요한 원인 중 하나가 DBMS_CALL입니다.
즉 100번 날리는 SQL (Loop fetch방식처럼)를 하나의 SQL로 집합처리 해준다면 100배 빨라지는건 당연한 일이겠죠?
오늘도 예제 보시죠..
단일 SQL로 한번에 처리했습니다. 실행결과는
===================================================
# ALL INSERT : 0.547 seconds (10000 rows INSERT)
# ALL UPDATE : 0.438 seconds (10000 rows UPDATE)
===================================================
역시 빠릅니다. 문맥전환도 없고, DBMS_CALL도 없고...하지만 단일 SQL로 작성하기 위해서는 위의 간단한 문제는 가능하지만 복잡한 로직이 들어가야 할 경우 생각보다 쉽지 않습니다. 하지만, SQL은 우리가 생각한 것 이상 강력하며 여러가지 집합처리(합집함,교집합,차집합), 집합복제(연산곱) 등의 연산을 통해 무한한 확장이 가능합니다. SQL은 처음 배우기는 쉽지만, 제대로 활용하기는 어렵다.. 라는 말이 맞는듯 합니다.
단일SQL로 작성하는 잇점중 또 하나는 DBMS 옵티마이저와 관계가 있습니다. ORACLE의 구매비용 중에 상당부분이 옵티마이저 값이라고 하더군요. 그만큼 구현하기도 힘들고 각 DBMS업체들의 노하우라고 합니다. 옵티마이저는 우리 날린 SQL의 수행 단계(파싱->실행계획->세부실행계획->수행) 단계 중 실행계획 수립 단계에 역할을 하게 되며 단일 SQL단위로 실행계획을 수립하게 됩니다.
즉, PL/SQL 또는 3GL 언어에서 SQL을 쪼개서 작성하게 되면 옵티마이저는 각각의 SQL마다 실행 계획을 최적화하려 노력하지만 전체적인 로직을 파악해서 최적화 해주지는 못합니다.
여러분은 옵티마이저를 얼마나 활용하고 계신가요? SQL작성 시 실행계획을 확인하고 옵티마이저가 동작하는 원리를 알게되면 생각 보다 많은 삽질을 줄일 수 있고 성능을 높일 수 있습니다.
말이 길었는데... 정리를 하자면...
1. 최대한 SQL을 집합적으로 처리하여 옵티마이저의 활용을 극대화 하라.
2. 옵티마이져 실행계획을 확인하고 최대한 활용하라.
> SQL의 집합적 처리를 위해 간단한 문제를 하나 내겠습니다. (오라클 기준입니다)
SELECT TO_DATE('20081101','YYYYMMDD') FROM DUAL;
이 문장을 가지고 해당 월에 해당하는 달력을 출력하는 SQL을 작성하라 (답은 맨 아래에..)
반드시 한방 SQL로 만드셔야 합니다. 이걸 푸셨다면 SQL의 고수십니다.
(이걸 PL/SQL로 Loop돌면서 배열에 넣고 빼고... 이렇게 짜면 안됩니다..ㅋㄷㅋㄷ)
Hint : Connect By 문을 사용해서 집합을 복제해야 합니다... (오라클만 가능...ㅠ.ㅠ)
MSSQL이나 DB2는 1~100까지 존재하는 테이블을 하나 만드셔서 활용해야 합니다.
위의 문제처럼 SQL은 하나의 쿼리문장이 아닌 하나의 Application으로 봐야 합니다.
(6) 결론
- 10000건의 일괄 INSERT / UPDATE의 성능 비교 결과, PL/SQL엔진과 SQL엔진 사이에 일어나는 문맥전환 및 DBMS_CALL 횟수가 성능에 큰 저하 를 가져 온다는것을 알수 있음
- 최대한 문맥전환이 일어나지 않도록 FORALL/TempTable/단일SQL 집합 처리 방식으로 일괄처리하는것이 좋음
- TempTable은 메모리의 크기나 수행사용횟수를 고려해야 함
- BULK COLLECTION과 FORALL 사용시 TYPE에 속한 필드를 직접 접근할 수 없는 문제로 사용에 불편한 면 존재 (11g에서는 직접가능)
- 원본 데이터의 큰 가공 없는 INSERT / UPDATE문은 단일 SQL방식을 사용하는것이 성능에 유리
- 많은 수정과 가공 할경우 BULK COLLECTION / TempTable을 통한 방식이 유리
<모범답안>
문제 해설을 원하시면 댓글달아 주세요..
- ORACLE PL/SQL에서 일괄DATA 처리방식에 대한 방식에 알아본다
- 각 방식의 장단점과 시스템 TEST결과를 비교 분석하여 최적의 솔루션을 찾아본다.
- ORACLE에서 일괄로 DATA를 처리하는 방식은 크게 5가지로 구분할 수 있다.
CASE1 : CURSOR FOR LOOP 방식
CASE2 : BULK COLLECTION 방식
CASE3 : BULK COLLECTION & FORALL문을 이용한 방식
CASE4 : TEMP TABLE 방식
CAES5 : 단일SQL을 통한 집합처리 방식
그럼 새부적으로 하나씩 관련 소스와 테스트 결과를 분석 해보도록 하겠습니다.
II. 일괄처리방식 분석
(1) CUROSR FOR LOOP 방식
- 고전적인 절차적 방식에 의한 순차적 DML문을 사용하는 방식
- 일괄처리 방식을 PL/SQL로 절차적 방식만 사용할경우 성능에 심각한 문제발생 가능성 존재
<개발 시나리오>
PL./SQL문법책을 보면 제일 앞부분에 나오는게 CURSOR 입니다. 아.. 오라클에서도 절차적 언어를
지원하는구나... IF도 있고. FOR도 있고..JAVA나 C#만큼의 패키지는 지원하지 않겠지만 그래도 이정도면
웬만한 프로그램은 짜겠는걸?
이렇게 생각한 저는 1만건의 쿼리를 커서를 지정하여 하나씩 Fetch하면서 Looping처리를 합니다. 루핑
안에는 각종 IF문과 다른 두 개 테이블 Select문을 넣고 해당 결과가 있으면 변수에 넣고 이런식으로
프로그램 을 짜게 됩니다.
이 경우의 DB 부하의 상당부분을 차지하는 DBMS Call 횟수를 간단히 계산해 보면
DBMS Call 횟수 = Cursor선언 + Rows Fetch건수 + Looping내 Select(건수) * Fetch건수
= 1 + 10000 + (2 * 10000) = 30,001
즉, 이러한 프로시져를 돌리면 한번 실행에 DBMS는 3만번의 DBMS CALL을 수행하는 꼴이 됩니다.
이러한 프로시져를 100명이 동시에 돌린다고 생각해 보십시요... ㅠ.ㅠ
<실전 TEST>
위와 같은 프로시져를 만들어 만건의 테이블을 커서로 패치해서 루핑안에 다른 테이블에 Insert/Update
하는 프로시져입니다. (여기서 Merge Into문은 해당 테이블에 Insert/Update여부를 내부적으로 Outer Join
방식에 의해 스스로 판단해서 처리해주는 문장입니다. 즉 1000건중에 900건은 Insert 100은 Update 이런
식으로요..시간이 되면 따로 이 문장에대한 내용도 올리도록 하겠습니다. - 사용시 주의가 요구된답니다. ^^)
결과는 아래와 같습니다.
===================================================
# ALL INSERT : 1.594 seconds (10000 rows INSERT)
# ALL UPDATE : 110.797 seconds (10000 rows UPDATE)
===================================================
다음번에는 BULK COLLECTION 방식에 대한 내용을 올리도록 하겠습니다.
(2) BULK COLLECT BINDING 사용을 통한 대용량 처리
- COLLECTION을 LOOP FETCH방식이 아닌 BULK COLLECT 방식으로 일괄 FETCH
- PL/SQL과 SQL엔진 사이의 문맥전환수를 줄여주고 데이터 조회 오버해드를 대폭 줄여준다
- PL/SQL 루프를 사용한 한행 패치보다 훨씬 빠름
음.. BULK? COLLECTION? 생소한 용어입니다.. 저한테는... 뭔가 배열스러운 곳에 임시로 넣는다 뭐 이런
비스무리한 거 같긴 한데... "문맥전환" 이건 또 뭔가요?
일단 아래 소스를 보시죠..
> TYPE AWB_REC IS TABLE OF XXXXX%ROWTYPE INDEX BY BINARY_INTEGER;
일단 ROWTYPE으로 배열선언을 했군요...
> SELECT * BULK COLLECT INTO R_REC .....
새로운 구문이 나왔습니다 "BULK COLLECT INTO" 이것은 해당 결과를 배열에 일괄로 넣는 것입니다.
즉 SELECT문이 1,000건이면 1,000건이 한방에 배열로 들어간다는 것이죠.. 괜찮아 보이네요..
> FOR inx1 IN 1..R_REC.COUNT LOOP....END LOOP
해당배열을 가지고 루핑을 돌면서 어떤 처리를 할때 쓰입니다.
음.. 일단 패치에 대한 부하는 없겠군요.. 왜나하면 한방에 배열에 패치가 되었기 때문에..
만약 루핑속에 두개의 SELECT문을 돌리는 문장을 구성한다면
DBMS_CALL횟수 = 커서선언(1) + Fetch(1) + (SELECT (2)) * 배열.COUNT
= 1 + 1 + 2*10,000 = 20,002번
오.. LOOP방식보다 DBMS_CALL이 그래도 많이 줄었습니다.
아래 결과에서도 INSERT와 UPDATE가 LOOP보다는 상대적으로 빠르게 나왔습니다.
===================================================
# ALL INSERT : 1.469 seconds (10000 rows INSERT)
# ALL UPDATE : 106.672 seconds (10000 rows UPDATE)
===================================================
(3) BULK COLLECT & FOR ALL문 사용을 통한 대용량 처리
- PL/SQL에서 Array Processing을 지원하는 방법 (집합적 처리 방법)
- FORALL을 사용할 때, 문장은 모음의 각 항목에 대해 한번씩 실행 하지만 PL/SQL에서 SQL로의 문맥전환은 오직 한번
- FORALL문은 COMPOSITE TYP(%ROWTYPE)의 필드를 직접 핸들링 할 수 없다 . 이것은 매우 불편한 제약사항 (하지만 11g에서는 가능하다고 합니다...)
COMPOSITE TYPE이란 ORACLE에서 ROWTYPE등과 같이 여러개의 타입을 가지는 배열 정도로 생각하시면 되겠습니다. 즉 PL.SQL에서는 ROWTYPE.컬럼이름.. 이렇게 접근이 가능한데...
FORALL에서는 이게 안된다는 말입니다.. 그럼..어떻게 하란 말이지?
결국 10g 이하 버전에서는 각 컬럼 타입별로 별도 배열을 만들던지, 아니면 ROWTYPE과 같은 컬럼을 가지는 RECODE OBJECT를 만들어야 합니다. (구조체 처럼) 굉장이 불편해 보이네요... 하지만 컬럼이 얼마 안된다면 감수할 만 한듯 합니다.
문맥전환이란 ? : PL/SQL블럭에 포함되어 있는 SQL문은 SQL 파싱엔진에서 별도 처리해서 PL/SQL문 실행기로 넘기게 되는데 이런 흐름을 문맥전환이라 한다.
음 FOR ALL이란 새로운게 나왔습니다.. 추측해 보건데.. 뭔가 한방에 처리를 하는 듯 한거 같은데 이것도 역시 소스를 먼저 보시죠.
> FORALL I IN R_AWB_MNGNO.FIRST..R_AWB_MNGNO.LAST SAVE EXCEPTIONS
: 이문장은 아래에 나오는 MERGE INTO를 단 한번의 문맥전환 처리로 한번에 처리하게 합니다.
1000건을 한번에 INSERT/UPDATE 한다는 말이죠.. 1건씩이 아닌..
> FETCH CR1 BULK COLLECT INTO R_AWB_MNGNO, .... LIMIT 1000;
: 아까 위에서 나온 벌크 바인딩인데..뒤에 LIMIT라는게 붙었네요.. 이것은 1000건 까지만 바인딩 하라는 말입니다. 즉 10,000건을 1000건씩 나눠 처리하는 겁니다.
실행결과는 아래와 같습니다.
===================================================
# ALL INSERT : 0.922 seconds (10000 rows INSERT PER 1000)
# ALL UPDATE : 1.672 seconds (10000 rows UPDATE PER 1000)
===================================================
허걱... 이건 충격에 가깝습니다.. 전에 있었던 LOOP방식과 비교해서 굉장히
빠른 속도를 보여주었습니다. 수치상으론 100배인데... 문맥전환의 오버해드가 이렇게
클 줄은...
결국은 LOOPING처리 할때 DBMS CALL + 문맥전환의 오버해드가 시스템 부하의 주요 원인이었음을 추측해 볼수 있겠습니다.
휴.. 쓰다보니 30분이 넘었네요..다음 시간에는 ORACLE에서 제공하는 TEMP_TABLE을 이용한 대용량 처리에 대해 이야기 해보도록 하겠습니다.
(4) Temp Table
- 세션내에 임시로 사용할 data들을 영구적인 segment형태가 아닌 temporary structure에서 관리 생성문장에 의해서 definition이 dictionary에 저장
- 각 세션에서 해당 table을 사용할 때마다 definition을 이용 memory에 table 구조를 생성
- 임시 테이블은 실제로 테이블에 데이터가 추가되기 전까지는 공간을 할당받지 않음
- 데이터를 보관하기 위한 공간으로 일반적인 객체를 위한 데이터 저장 공간이 임시 테이블스페이스 이용
위의 TempTable정의를 좀 자세히 보도록 하겠습니다.
일단. "세션내에서 임시로 사용" 이라는 말이 나오는데 즉 세션별로 임시테이블을 만들어 사용할 수 있다는 의미 입니다. 그리고 TempTable은 Heap Table (일반 테이블) 과 달리 Create문을 사용해서 생성시
데이터 추가되기 전까지는 공간을 할당 받지 않습니다.
우리가 일반 테이블 생성시에는 데이터가 아무것도 없더라도, 오라클은 일정한 연속된 블럭(Block) 즉, 익스텐트를 바로 할당해 주게 됩니다. 하지만 TempTable은 데이터가 들어와야만 할당해 주는군요. 그리고 메모리에 테이블 구조를 생성하게 된다는 것도 매우 중요합니다. 어딘가에 숨어있다가, 우리가 그 테이블에 Insert할 때 나타나서 테이블을 구성해 준다 뭐 이정도로 이해 해도 큰 무리 없을듯 합니다.
일단 소스 보시죠..
먼저 TempTable 생성 문장부터 보겠습니다
(1) 생성문장
> CREATE GLOBAL TEMPORARY TABLE TMP_COM_AWB(........) ON COMMIT DELETE ROWS;
또는
> CREATE GLOBAL TEMPORARY TABLE MYTEMP [ON COMMIT PRESERVE ROWS / ON COMMIT
DELETE ROWS] as select * from <테이블명> where 1=0
(1=0은 부정형 조건이므로 false가 되어 실제로 들어가는 데이터는 없고 테이블 스펙만 만들짐)
(2) 옵션사용
'ON COMMIT DELETE ROWS' : transaction단위이며 이때 data는 commit이 되는 시점에 자동 제거
'ON COMMIT PRESERVE ROWS' : session단위이며 이때 data는 해당 session이 종료되면 제거.
default는 'ON COMMIT DELETE ROWS'
아.. 이제 좀 감이 잡히시나요? 결국 TempTable은 자신의 세션내에서만 유효하고 (즉 다른세션에서는
볼수가 없네요) 옵션에 따라. 하나의 트랜잭션내에서 사용할껀지 세션을 유지하는 내내 사용하다가
세션을 끊으면 삭제할 것인지를 선택할 수 있다는 뜻입니다.
(3) 특징 및 주요 사항
사용시 주의사항이나 특징에 대해 알아보도록 하겠습니다.
1. 세션간의 Data에 대한 동시제어에 의한 충돌이 없음 (당연한거 아닌가?)
2. 임시적 관리되는 Data이므로 Redo Log를 발생시키지 않습니다. (즉 장애시 복구가 불가능하겟네요)
3. Index, view Trigger를 생성하여 사용할 수 있다. (일반 테이블과 똑같네요..)
4. Table 정의를 Export 할수 있으나 그 데이터는 Export할 수 없다. (이것도 당연한 듯)
5. foreign key constraint를 설정할 수 없다.
6. parallel DML이나 parallel query는 지원되지 않는다.
7. storage나 tablespace는 지정할 수 없다.
8. 분산 transaction은 지원되지 않는다. (중요합니다)
실행 결과는
===================================================
# ALL INSERT : 0.75 seconds (10000 rows INSERT)
# ALL UPDATE : 0.359 seconds (10000 rows UPDATE)
===================================================
오호.. 이것 역시 굉장이 빠릅니다...
속도 하나는 정말 매력적이네요.
문맥전환 오버헤드도 없고, Fetch에 의한 DBMS_CALL도 없고, 그리고 메모리 테이블이니...
하지만 너무 큰 데이터를 메모리에 올린다면.. 이건 쫌 그렇네요.. 또는 여러사람이 돌리는 프로그램에 이런 문장이 있으면 모든 Session별로 임시 테이블이 생길수도 있을 것이고..
즉. 단독으로 수행되는 Batch JOB에 5천건 이하 정도의 처리에 쓰면 정말 최강일듯 합니다.
(5) 단일 SQL을 통한 집합 처리
- 한건처리하는 SQL을 10,000번 호출하는 방식과 하나의 SQL로 10,000건을 범위 처리하는 것은 결과는 같지만 성능적인 차이는 10,000 배 차이가 남
- OPTIMIZER가 최적화된 실행계획을 수행하는 단위는 SQL임
- APPLICATION LVL의 SQL 사용은 유지보수, 단순, 생산성 향상을 보장함
저번 시간에서도 이야기 했지만.. DBMS에 부하를 주는 중요한 원인 중 하나가 DBMS_CALL입니다.
즉 100번 날리는 SQL (Loop fetch방식처럼)를 하나의 SQL로 집합처리 해준다면 100배 빨라지는건 당연한 일이겠죠?
오늘도 예제 보시죠..
단일 SQL로 한번에 처리했습니다. 실행결과는
===================================================
# ALL INSERT : 0.547 seconds (10000 rows INSERT)
# ALL UPDATE : 0.438 seconds (10000 rows UPDATE)
===================================================
역시 빠릅니다. 문맥전환도 없고, DBMS_CALL도 없고...하지만 단일 SQL로 작성하기 위해서는 위의 간단한 문제는 가능하지만 복잡한 로직이 들어가야 할 경우 생각보다 쉽지 않습니다. 하지만, SQL은 우리가 생각한 것 이상 강력하며 여러가지 집합처리(합집함,교집합,차집합), 집합복제(연산곱) 등의 연산을 통해 무한한 확장이 가능합니다. SQL은 처음 배우기는 쉽지만, 제대로 활용하기는 어렵다.. 라는 말이 맞는듯 합니다.
단일SQL로 작성하는 잇점중 또 하나는 DBMS 옵티마이저와 관계가 있습니다. ORACLE의 구매비용 중에 상당부분이 옵티마이저 값이라고 하더군요. 그만큼 구현하기도 힘들고 각 DBMS업체들의 노하우라고 합니다. 옵티마이저는 우리 날린 SQL의 수행 단계(파싱->실행계획->세부실행계획->수행) 단계 중 실행계획 수립 단계에 역할을 하게 되며 단일 SQL단위로 실행계획을 수립하게 됩니다.
즉, PL/SQL 또는 3GL 언어에서 SQL을 쪼개서 작성하게 되면 옵티마이저는 각각의 SQL마다 실행 계획을 최적화하려 노력하지만 전체적인 로직을 파악해서 최적화 해주지는 못합니다.
여러분은 옵티마이저를 얼마나 활용하고 계신가요? SQL작성 시 실행계획을 확인하고 옵티마이저가 동작하는 원리를 알게되면 생각 보다 많은 삽질을 줄일 수 있고 성능을 높일 수 있습니다.
말이 길었는데... 정리를 하자면...
1. 최대한 SQL을 집합적으로 처리하여 옵티마이저의 활용을 극대화 하라.
2. 옵티마이져 실행계획을 확인하고 최대한 활용하라.
> SQL의 집합적 처리를 위해 간단한 문제를 하나 내겠습니다. (오라클 기준입니다)
SELECT TO_DATE('20081101','YYYYMMDD') FROM DUAL;
이 문장을 가지고 해당 월에 해당하는 달력을 출력하는 SQL을 작성하라 (답은 맨 아래에..)
반드시 한방 SQL로 만드셔야 합니다. 이걸 푸셨다면 SQL의 고수십니다.
(이걸 PL/SQL로 Loop돌면서 배열에 넣고 빼고... 이렇게 짜면 안됩니다..ㅋㄷㅋㄷ)
Hint : Connect By 문을 사용해서 집합을 복제해야 합니다... (오라클만 가능...ㅠ.ㅠ)
MSSQL이나 DB2는 1~100까지 존재하는 테이블을 하나 만드셔서 활용해야 합니다.
위의 문제처럼 SQL은 하나의 쿼리문장이 아닌 하나의 Application으로 봐야 합니다.
(6) 결론
- 10000건의 일괄 INSERT / UPDATE의 성능 비교 결과, PL/SQL엔진과 SQL엔진 사이에 일어나는 문맥전환 및 DBMS_CALL 횟수가 성능에 큰 저하 를 가져 온다는것을 알수 있음
- 최대한 문맥전환이 일어나지 않도록 FORALL/TempTable/단일SQL 집합 처리 방식으로 일괄처리하는것이 좋음
- TempTable은 메모리의 크기나 수행사용횟수를 고려해야 함
- BULK COLLECTION과 FORALL 사용시 TYPE에 속한 필드를 직접 접근할 수 없는 문제로 사용에 불편한 면 존재 (11g에서는 직접가능)
- 원본 데이터의 큰 가공 없는 INSERT / UPDATE문은 단일 SQL방식을 사용하는것이 성능에 유리
- 많은 수정과 가공 할경우 BULK COLLECTION / TempTable을 통한 방식이 유리
<모범답안>
문제 해설을 원하시면 댓글달아 주세요..
Oracle Demo Build
create user scott identified by tiger
default tablespace users
temporary tablespace temp;
grant connect,resource to scott;
connect scott/tiger;
@$ORACLE_HOME/sqlplus/demo/demobld.sql
--@$ORACLE_HOME/sqlplus/demo/demodrop.sql
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET Timed_Statistics = TRUE;
$ tkprof ora_11651.trc ora_11651.prf explain=scott/tiger sys=no
default tablespace users
temporary tablespace temp;
grant connect,resource to scott;
connect scott/tiger;
@$ORACLE_HOME/sqlplus/demo/demobld.sql
--@$ORACLE_HOME/sqlplus/demo/demodrop.sql
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET Timed_Statistics = TRUE;
$ tkprof ora_11651.trc ora_11651.prf explain=scott/tiger sys=no
피드 구독하기:
글 (Atom)