이펙티브 오라클 (2008년)
대량 처리 0 0 615

by 구루비스터디 대량처리 BULK COLLECT ETL [2009.04.30]


효과가 클때는 대량처리를 사용하라


EXEC runstats_pkg.rs_start;

/* 1행씩 처리 */
BEGIN
    FOR i IN 1 .. 5000
    LOOP
        FOR x IN (SELECT ename, empno, hiredate FROM emp)
        LOOP
            NULL;
        END LOOP;
    END LOOP;
END;
/

EXEC runstats_pkg.rs_middle;

/* 대량 처리 */
DECLARE
    l_ename dbms_sql.varchar2_table;
    l_empno dbms_sql.number_table;
    l_hiredate dbms_sql.date_table;
BEGIN
    FOR i IN 1 .. 500
        SELECT ename, empno, hiredate
          BULK COLLECT INTO l_ename, l_empno, l_hiredate
          FROM emp;
    END LOOP;
END;
/

EXEC runstats_pkg.rs_stop(10000);

Run1 ran in 274 hsecs
Run2 ran in 132 hsecs
run 1 ran in 207.58% of the time

Name                                 Run1      Run2        Diff
STAT...session logical reads       80,522    15,525     -64,997
STAT...consistent gets             80,003    15,004     -64,999
STAT...buffer is not pinned co     70,000     5,000     -65,000
STAT...no work - consistent re     70,000     5,000     -65,000
STAT...table scan blocks gotte     70,000     5,000     -65,000
STAT...recursive calls             75,003     5,003     -70,000
LATCH.cache buffers chains        162,601    32,582    -130,019

Run1 latches total versus runs -- difference and pct
Run1       Run2      Diff        Pct
188,736    58,658    -130,078    321.76%

  • 데이터 양이 많으면 많을수록 상대적으로 우수하다.
  • 데이터 양이 적을때는 효과가 크지 않다.
  • 극적인 효과가 기대되는 곳에서만 대량처리를 사용


ETL작업에 대량처리를 사용하라

  • ETL : 추출(Extract), 변환(Transform), 적재(Load)를 의미하는 데이터웨어하우징 용어로 다음은 전형적인 ETL 구조이다.

FOR x IN (SELECT * FROM ...)
LOOP
    Process data
    INSERT INTO table VALUES(...);
END LOOP;

  • INSERT AS SELECT 구문으로 튜닝할수 있다.
  • BULK COPY 를 이용하여 최적화할 수 있다.

CREATE TABLE t1
AS
SELECT *
  FROM dba_objects
 WHERE 1 = 0;

CREATE TABLE t2
AS
SELECT *
  FROM dba_objects
 WHERE 1 = 0;



CREATE PROCEDURE row_at_a_time
AS
BEGIN
    FOR x IN (SELECT * FROM dba_objects)
    LOOP
        INSERT INTO t1 VALUES x;
    END LOOP;
END;
/



CREATE PROCEDURE row_at_a_time
AS
BEGIN
    FOR x IN (SELECT * FROM dba_objects)
    LOOP
        INSERT INTO t1 VALUES x;
    END LOOP;
END;
/



CREATE PROCEDURE nrows_at_a_time(p_array_size NUMBER)
AS
TYPE array IS TABLE OF dba_objects%ROWTYPE;
l_data array;
CURSOR c IS SELECT * FROM dba_objects;
BEGIN
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
        FORALL i IN 1 .. l_data.COUNT
            INSERT INTO t2 VALUES l_data(i);
        EXIT WHEN c%NOTFOUND;
    END LOOP;
END;
/

"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3576

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입