PL/SQL Programming 에센스 과정

• 2023년 5월 11일부터 12일까지 KITRI 에서 진행하는 PL/SQL Programming 에센스 과정을 수강하였습니다.
• 2일동안 진행되는 과정에서 PL/SQL 기본구조, 변수와 컬렉션, CURSOR, Function, Procedure, Trigger, Package 등 실제 업무에 활용되는 다양한 기술들을 배울 수 있었습니다.
• 학습 내용중 BULK COLLECT 와 FORALL 를 활용한 성능개선 부분을 배울 수 있었고, 대용량을 처리하는 실무 업무에서 활용하기 좋을 거라 생각하였습니다.
• 동시에 실무 코드에서 보지 못한 기능이어서 소개하는 목적도 있습니다.
• 이번에 소개할 기능은 BULK COLLECT INTO 와 FORALL 입니다.
• BULK COLLECT INTO 와 FORALL 는 SELECT 와 INSERT/UPDATE/MERGE 과정에서 데이터를 BULK 로 처리하는 기능으로 계산속도를 높일수 있습니다.

1. BULK COLLECT INTO

Oracle에서 BULK COLLECT INTO는 대량의 데이터를 한 번에 메모리에 로드하여 처리할 수 있는 방법을 제공합니다.
일반적으로 ROW BY ROW 방식으로 데이터를 처리하는 것보다 BULK COLLECT INTO 방식을 사용하는 것이 성능이 좋습니다.
그 이유는 다음과 같습니다.

1. 네트워크 부하 감소 BULK COLLECT INTO를 사용하면 데이터베이스와 클라이언트 간의 네트워크 부하가 적어지기 때문에 성능이 향상됩니다. ROW BY ROW 방식은 데이터를 한 줄씩 읽어오기 때문에 많은 양의 데이터가 있을 경우 네트워크 부하가 커집니다.

2. 메모리 사용 최적화 BULK COLLECT INTO를 사용하면 대량의 데이터를 한 번에 메모리에 로드하기 때문에 메모리 사용이 최적화됩니다. ROW BY ROW 방식은 매번 한 줄씩 메모리를 로드하기 때문에 메모리 사용이 많아질 수 있습니다.

3. 수행 시간 감소 BULK COLLECT INTO를 사용하면 SQL문을 한 번 실행하여 결과를 한꺼번에 가져올 수 있기 때문에, ROW BY ROW 방식보다 수행 시간이 감소합니다.

하지만 BULK COLLECT INTO 방식은 메모리 사용량이 많아질 수 있으므로, 대량의 데이터를 처리할 때 메모리 사용량을 고려하여 적절한 크기로 나누어 처리해야 합니다.
이를 잘 제어하지 못하면 메모리를 과다하게 사용하여 오히려 성능이 나빠질 수도 있습니다.

BULK COLLECT INTO 를 SELECT 에 활용하면 성능이 개선될까?

• 결론부터 말하면 개선되지 않습니다.
• 다음 코드를 확인해보자.

SELECT COUNT(*) FROM KPI_FCSTACCURACY_AP1;

• 실제 실무에서 사용되는 4143112 건의 ROW 를 가진 테이블을 가지고 왔습니다.

DECLARE 
    V_COUNT NUMBER := 1;
    V_ITEM VARCHAR(50);
    START_TIME NUMBER;
    END_TIME NUMBER;
    V_RESULT NUMBER;
BEGIN
    ---------- 1. 시작 시간 -----------------------
    START_TIME := DBMS_UTILITY.GET_TIME;
    ----------------------------------------------
    
    ---------- 2. 로직 시작 -----------------------
    
    FOR R IN (SELECT * FROM KPI_FCSTACCURACY_AP1) 
    LOOP
        V_COUNT := V_COUNT+1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(V_COUNT);
    DBMS_OUTPUT.PUT_LINE(V_ITEM);
    
    ---------- 3. 종료 시간 및 소요시간 계산 ---------
    END_TIME := DBMS_UTILITY.GET_TIME;
    SELECT (END_TIME - START_TIME)/100 DIFF_SEC INTO V_RESULT FROM DUAL;
    DBMS_OUTPUT.PUT_LINE(V_RESULT);
    ----------------------------------------------
END;

• FOR 문을 활용하여 테이블의 전체 데이터를 가지고 오는 쿼리입니다.
• ROW BY ROW 방식을 사용하기 때문에 느릴 것이라 예측됩니다.
• 실제 소모 시간은 10.25초입니다.

DECLARE
    TYPE TABLE_REC IS TABLE OF KPI_FCSTACCURACY_AP1%ROWTYPE INDEX BY PLS_INTEGER;
    CURSOR C_TABLE IS (SELECT * FROM KPI_FCSTACCURACY_AP1); 
    V_TABLE_ARR TABLE_REC;
    V_COUNT NUMBER := 1;
    V_ITEM VARCHAR(50);
    START_TIME NUMBER;
    END_TIME NUMBER;
    V_RESULT NUMBER;
BEGIN
    ---------- 1. 시작 시간 -----------------------
    START_TIME := DBMS_UTILITY.GET_TIME;
    ----------------------------------------------
    
    ---------- 2. 로직 시작 -----------------------
    OPEN C_TABLE;
    LOOP
        FETCH C_TABLE BULK COLLECT INTO V_TABLE_ARR LIMIT 500;
        EXIT WHEN V_TABLE_ARR.COUNT = 0;
        
        FOR R IN V_TABLE_ARR.FIRST .. V_TABLE_ARR.LAST 
        LOOP
            V_COUNT := V_COUNT+1;
        END LOOP;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(V_COUNT);
    CLOSE C_TABLE;
    
    ---------- 3. 종료 시간 및 소요시간 계산 ---------
    END_TIME := DBMS_UTILITY.GET_TIME;
    SELECT (END_TIME - START_TIME)/100 DIFF_SEC INTO V_RESULT FROM DUAL;
    DBMS_OUTPUT.PUT_LINE(V_RESULT);
    ----------------------------------------------
END;

• 이번에는 BULK COLLECT INTO 를 활용하여 SELECT 문을 개선하였습니다.
• 메모리 문제를 고려하여 BULK COLLECT INTO 활용시 LIMIT 500 을 설정하여 메모리 부하를 줄였습니다.
• LIMIT 숫자를 500, 1000, 1500 으로 수정하며 소요시간을 계산하였습니다.
• LIMIT 500 : 10.79초
• LIMIT 1000 : 10.1초
• LIMIT 1500 : 10.18초

FOR	BULK COLLECT INTO LIMIT 500	BULK COLLECT INTO LIMIT 1000	BULK COLLECT INTO LIMIT 1500
10.25초	10.79초	10.1초	10.18초

• 소요시간에 큰차이가 없는 것을 알 수 있습니다.
• 그렇다면 SELECT 에서 BULK COLLECT INTO 는 성능개선이 안되는 것인가?

왜 SELECT 문에서 BULK COLLECT INTO 가 성능개선을 이루지 못하는가

• ORACLE 버전이 올라가면서 기본 SELECT 전략이 변경되었기 때문입니다.
• ORACLE 19C 버전에서는 기본 SELECT 문 활용시 더이상 ROW BY ROW 방식이 아닌 100 ROWS 씩 BULK 로 데이터를 가지고 오는 것이 DEFAULT 설정입니다.
• 메모리를 고려하여 BULK COLLECT INTO 활용 시 LIMIT 을 500 에서 1000을 거는것을 고려했을때 더이상 BULK COLLECT INTO 로 SELECT 문의 극적인 성능 개선을 가지고 오는 것은 어려워졌습니다.

2.FORALL

Oracle의 FORALL 문은 PL/SQL에서 대량의 데이터를 한 번에 처리할 수 있는 방법을 제공합니다. 이를 사용하면 성능이 향상됩니다.
그 이유는 다음과 같습니다.

1. 한 번의 SQL문으로 대량의 데이터 처리 FORALL 문을 사용하면 한 번의 SQL 문으로 대량의 데이터 처리가 가능합니다. 이는 SQL 문이 실행될 때마다 데이터베이스와의 연결 및 트랜잭션 처리 시간이 줄어들어 성능이 향상됩니다.

2. 바인드 변수를 사용하여 메모리 사용 최적화 FORALL 문은 바인드 변수를 사용하여 메모리 사용을 최적화합니다. 바인드 변수를 사용하면 SQL 문이 실행될 때마다 새로운 메모리 공간을 할당하지 않고, 이미 할당된 메모리 공간에 값을 할당합니다. 따라서 메모리 사용량도 줄어들어 성능이 향상됩니다.

하지만 FORALL 방식은 메모리 사용량이 많아질 수 있으므로, 대량의 데이터를 처리할 때 메모리 사용량을 고려하여 적절한 크기로 나누어 처리해야 합니다.
이를 잘 제어하지 못하면 메모리를 과다하게 사용하여 오히려 성능이 나빠질 수도 있습니다.

FORALL 를 INSERT 에 활용하면 성능이 개선될까?

• 결론부터 말하면 굉장히 개선됩니다.
• 다음 코드를 확인해보자.

CREATE TABLE MOCK_TABLE
(
   C1    NUMBER,
   C2    NUMBER
);

• MOCK_TABLE 을 CREATE 하였습니다.

DECLARE
   START_TIME   NUMBER;
   END_TIME     NUMBER;
   V_RESULT     NUMBER;
BEGIN
   ---------- 1. 시작 시간 -----------------------
   START_TIME := DBMS_UTILITY.GET_TIME;

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

   ---------- 2. 로직 시작 -----------------------
   FOR i IN 1 .. 100000
   LOOP
      INSERT INTO MOCK_TABLE
           VALUES (i, i);
   END LOOP;

   COMMIT;
   ---------- 3. 종료 시간 및 소요시간 계산 ---------
   END_TIME := DBMS_UTILITY.GET_TIME;

   SELECT (END_TIME - START_TIME) / 100 DIFF_SEC
     INTO V_RESULT
     FROM DUAL;

   DBMS_OUTPUT.PUT_LINE (V_RESULT);
----------------------------------------------
END;
/

• 기본 FOR 문을 활용한 예시입니다.
• 1부터 100000 까지 MOCK_TABLE 에 데이터를 INSERT 합니다.
• 소요시간은 2.41초입니다.

DECLARE
   TYPE MOCK_TABLE_INS IS TABLE OF MOCK_TABLE%ROWTYPE
      INDEX BY BINARY_INTEGER;

   V_MOCK      MOCK_TABLE_INS;
   START_TIME   NUMBER;
   END_TIME     NUMBER;
   V_RESULT     NUMBER;
BEGIN
   ---------- 1. 시작 시간 -----------------------
   START_TIME := DBMS_UTILITY.GET_TIME;

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

   ---------- 2. 로직 시작 -----------------------
   FOR i IN 1 .. 100000
   LOOP
      V_MOCK (i).C1 := i;
      V_MOCK (i).C2 := i;
   END LOOP;

   FORALL i IN 1 .. 100000
      INSERT INTO MOCK_TABLE
      VALUES V_MOCK (i);

   COMMIT;
   ---------- 3. 종료 시간 및 소요시간 계산 ---------
   END_TIME := DBMS_UTILITY.GET_TIME;

   SELECT (END_TIME - START_TIME) / 100 DIFF_SEC
     INTO V_RESULT
     FROM DUAL;

   DBMS_OUTPUT.PUT_LINE (V_RESULT);
----------------------------------------------
END;
/

• 매개변수에 데이터를 집어넣고 FORALL 을 통해 1부터 100000 까지 데이터를 INSERT 하는 쿼리입니다.
• 소요시간은 0.14초입니다.
• 15배 이상의 성능 개선을 이루었습니다.

FOR WITH 100000 DATA	FORALL WITH 100000 DATA
2.41초	0.14초

3. 결론

• BULK 를 활용한 SELECT 문은 활용하기 어렵습니다. 인덱스를 활용한 튜닝 작업이나 중간 테이블을 통해 미리 데이터를 만들어두는 방식으로 성능을 높여야 합니다.
• FORALL 를 활용한 INSERT/MERGE/UPDATE 는 활용할 수 있는 부분이 많습니다.
• 만약 만약 데이터를 INSERT/MERGE/UPDATE 하는 부분이 있고 해당 부분의 시간 개선이 필요하다면 FORALL 를 적극적으로 활용하면 좋을거 같습니다.