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

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;
/