ORACLE/SQL

BULK COLLECT

argoLee 2017. 8. 17. 13:47

BULK COLLECT

LIMIT을 (를) 알면서 % NOTFOUND을 걷어차는 모범 사례

많은 양의 데이터를 가져와야 할 때마다 BULK COLLECT를 사용하기 시작했습니다. 이로 인해 내 DBA에 문제가 발생했습니다. 그는 나의 프로그램이 훨씬 더 빨리 달릴지라도 너무 많은 메모리를 소비하고 있다고 불평하고있다. 그는 생산 롤아웃을 승인하지 않습니다. 프로그래머는 무엇을 할 수 있습니까?

BULK COLLECT와 같은 기능을 배우고 사용할 때 기억해야 할 가장 중요한 점은 무료 점심이 없다는 것입니다. 거의 언제나 어쩔 수없는 트레이드 오프가 있습니다. BULK COLLECT와 다른 많은 성능 향상 기능과 마찬가지로 성능은 향상되지만 더 많은 메모리를 소비합니다.

특히 콜렉션의 메모리는 SGA (System Global Area)가 아닌 PGA (Program Global Area)에 저장됩니다. SGA 메모리는 Oracle Database에 연결된 모든 세션에서 공유되지만 PGA 메모리는 각 세션에 할당됩니다 . 따라서 프로그램에 콜렉션을 채우기 위해 5MB의 메모리가 필요하고 100 개의 동시 연결이있는 경우 해당 프로그램은 SGA에 할당 된 메모리 외에도 500MB의 PGA 메모리를 소비합니다.

다행스럽게도 PL / SQL을 사용하면 개발자가 LIMIT 절을 사용하여 BULK COLLECT 연산에서 사용되는 메모리 양을 쉽게 제어 할 수 있습니다.

직원 테이블에서 모든 행을 검색 한 다음 각 행에 대한 보상 분석을 수행해야한다고 가정합니다. BULK COLLECT는 다음과 같이 사용할 수 있습니다.

PROCEDURE process_all_rows
IS
   TYPE employees_aat 
   IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   l_employees employees_aat;
BEGIN
   SELECT *
   BULK COLLECT INTO l_employees
      FROM employees;
     
   FOR indx IN 1 .. l_employees.COUNT 
   LOOP
       analyze_compensation 
      (l_employees(indx));
   END LOOP;
END process_all_rows;

매우 간결하고 우아하며 효율적인 코드. 그러나 직원 테이블에 수십만 개의 행이 포함되어 있고 각 테이블에 수백 개의 열이 포함되어 있으면이 프로그램이 PGA 메모리를 과도하게 소비 할 수 있습니다.

결과적으로 BULK COLLECT의 "무제한"사용을 피해야합니다. 대신 SELECT 문을 명시 적 커서 선언으로 이동시킨 다음 간단한 루프를 사용하여 루프 본문을 실행할 때마다 테이블의 모든 행이 아닌 전체 행을 가져옵니다 (목록 1 참조).

Code Listing 1: Using BULK COLLECT with LIMIT clause

PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
    CURSOR employees_cur 
    IS 
        SELECT * FROM employees;

    TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    l_employees employees_aat;
BEGIN   
    OPEN employees_cur;
    LOOP
        FETCH employees_cur 
            BULK COLLECT INTO l_employees LIMIT limit_in;

        FOR indx IN 1 .. l_employees.COUNT 
        LOOP
            analyze_compensation (l_employees(indx));
        END LOOP;

        EXIT WHEN l_employees.COUNT < limit_in;

   END LOOP;

   CLOSE employees_cur;
END process_all_rows;

Listing 1의 process_all_rows 프로시 저는 한 번에 limit_in 행의 값까지 페치하도록 요청한다. PL / SQL은 데이터를 가져올 때마다 컬렉션의 동일한 limit_in 요소를 다시 사용하므로 동일한 메모리를 다시 사용합니다. 테이블 크기가 커지더라도 PGA 소비량은 안정적입니다.

LIMIT 절에서 사용할 숫자를 어떻게 결정합니까? 이론적으로 PGA에서 소비 할 수있는 메모리 양을 파악한 다음 가능한 한 그 양에 최대한 근접하도록 조정해야합니다.

그러나 I (및 다른 사람들)가 수행 한 테스트에서 적어도 25 이상이면 어떤 값을 선택하든 거의 동일한 성능을 보입니다. test_diff_limits.sql 스크립트는 이 열의 샘플 코드 는 Oracle Database 11g 인스턴스 에서 ALL_SOURCE 데이터 사전보기를 사용하여 이러한 동작을 보여줍니다 . 다음은 모든 행 (총 47 만)을 가져올 때 보았던 결과 (백분의 1 초)입니다.

Elapsed CPU time for limit of 1 = 1839
Elapsed CPU time for limit of 5 = 716
Elapsed CPU time for limit of 25 = 539
Elapsed CPU time for limit of 50 = 545
Elapsed CPU time for limit of 75 = 489
Elapsed CPU time for limit of 100 = 490
Elapsed CPU time for limit of 1000 = 501
Elapsed CPU time for limit of 10000 = 478
Elapsed CPU time for limit of 100000 = 527

%NOTFOUND 잊지 마세요

오라클 데이터베이스 10g 가 BULK COLLECT에 필적하는 속도로 수행 할 수 있도록 커서 FOR 루프를 자동으로 최적화 한다는 사실을 알고 매우 기뻤습니다 . 불행하게도, 우리 회사는 여전히 Oracle9의에서 실행되고 난 내가 대량 수집에 루프에 대한 나의 커서를 변환하기 시작했습니다 있도록 데이터베이스. 나는 문제가 생겼다 : 나는 100의 한계를 사용하고있다. 그리고 나의 질문은 총 227의 줄을 가져온다. 그러나 나의 프로그램은 단지 그들 중의 200 개를 처리한다. [쿼리는 Listing 2에 나와있다.] 내가 뭘 잘못하고 있니?

Code Listing 2: BULK COLLECT, %NOTFOUND, and missing rows

PROCEDURE process_all_rows
IS
   CURSOR table_with_227_rows_cur 
   IS 
      SELECT * FROM table_with_227_rows;

   TYPE table_with_227_rows_aat IS 
      TABLE OF table_with_227_rows_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   l_table_with_227_rows table_with_227_rows_aat;
BEGIN   
   OPEN table_with_227_rows_cur;
   LOOP
      FETCH table_with_227_rows_cur 
         BULK COLLECT INTO l_table_with_227_rows LIMIT 100;

         EXIT WHEN table_with_227_rows_cur%NOTFOUND;     /* cause of missing rows */

      FOR indx IN 1 .. l_table_with_227_rows.COUNT 
      LOOP
         analyze_compensation (l_table_with_227_rows(indx));
      END LOOP;
   END LOOP;

   CLOSE table_with_227_rows_cur;
END process_all_rows;

당신이 와서 너무 BULK 수집하는 루프에 대한 커서에서 완전히 올바른 전환 가까이! 유일한 실수는 EXIT WHEN 절에서 % NOTFOUND 커서 속성을 사용하는 습관을 포기하지 않았다는 것입니다.

EXIT WHEN 
table_with_227_rows_cur%NOTFOUND;

한 번에 한 행씩 데이터를 가져올 때 완벽한 의미를가집니다. 그러나 BULK COLLECT를 사용하면 해당 코드 행은 설명 된대로 정확하게 불완전한 데이터 처리를 초래할 수 있습니다.

프로그램을 실행할 때 무슨 일이 일어나고 있는지, 왜 마지막 27 행이 빠져 있는지 살펴 보겠습니다. 커서를 열고 루프에 들어가면 다음과 같이됩니다.

1. fetch 문은 1에서 100까지의 행을 검색합니다.
2. table_with_227_rows_cur % NOTFOUND는 FALSE로 평가되고 행이 처리됩니다.
3. fetch.은 101에서 200까지의 행을 검색합니다
4. table_with_227_rows_cur % NOTFOUND는 FALSE로 평가되고 행이 처리됩니다.
5. fetch 문은 201에서 227까지의 행을 검색합니다.
6. table_with_227_rows_cur % NOTFOUND는 TRUE로 평가되고 루프는 처리가 끝난 227 행으로 종료됩니다!

따라서 쿼리가 227 개의 행을 모두 처리하도록하려면 다음 문을 바꿉니다.

BULK COLLECT 및 컬렉션을 사용하여 커서에서 데이터를 가져 오는 경우 커서 특성을 사용하여 루프 및 데이터 처리를 종료할지 여부를 결정 해서는 안됩니다 .

EXIT WHEN 
table_with_227_rows_cur%NOTFOUND; 

with

EXIT WHEN 
l_table_with_227_rows.COUNT = 0;

일반적으로 BULK COLLECT로 작업 할 때는 다음 사항을 염두에 두어야합니다.

컬렉션은 인덱스 값 1부터 항상 순차적으로 채워집니다.
BULK COLLECT로 채워지면 컬렉션 1에서 컬렉션. COUNT 까지 반복 할 수 있도록 항상 안전합니다 (즉, NO_DATA_FOUND 예외를 발생시키지 않습니다) .
가져온 행이 없으면 fetch 비어 있습니다.
처리 할 행이 더 있는지 보려면 COUNT 메소드를 사용하여 콜렉션의 내용을 항상 확인하십시오.
커서 속성에 의해 리턴 된 값, 특히 % NOTFOUND는 무시하십시오.

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

대량의 데이터를 load 하거나 update(delete) 할 때 일반적으로 사용하는 LOOP 문보다 성능이 수십배 이상 좋은 방법이 BULK SQL 을 사용하는 방법이다.

일반적인 LOOP 문 안에 있는 SQL 은 매 LOOP 마다 1회씩 수행된다. LOOP 가 100만개이면 100만번의 SQL 수행이 발생되는 것이다. 반면에 BULK SQL 을 사용하면 LOOP 없이 1번의 SQL 수행으로 처리할 수 있다.

BULK SQL 처리를 위해 오라클 PL/SQL 에서는 FORALL 과 BULK COLLECT INTO 라는 이라는 예약어를 사용한다.

FORALL 은 PL/SQL 이 DML 문장을 SQL 엔진에게 보내는데 사용되고,

BULK COLLECT INTO 는 SQL 수행결과 데이터셋을 PL/SQL 엔진에게 보낸다.

BULK SQL 은 MULTI ROW 집합을 한번에 처리하기 때문에 ARRAY 처리가 쉬운 TABLE DATATYPE 같은 collection type 이 주로 사용된다.

BULK SQL 로 수백만건을 한번에 처리할 수도 있지만, 이렇게 하면 PGA 소모가 크기 때문에 1000 ~ 100000 건 단위로 나누어서 작업하는 것이 좋다. 이를 위해 LIMIT 이라는 예약어를 사용한다.

<참고> BULK SQL 에 대해서 자세히 공부하려면 아래의 링크를 보세요.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#BABFHGHI

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

BULK SQL 사용예

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

CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST IS

CURSOR sales_cur IS

SELECT PROD_ID,

CUST_ID,

TIME_ID,

CHANNEL_ID,

PROMO_ID,

QUANTITY_SOLD,

AMOUNT_SOLD

FROM SH.SALES;

TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER;

SALES_TBL SALES_TBL_TYPE;

BEGIN

OPEN sales_cur;

LOOP

FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000; -- PGA 소모를 작게 유지하기 위해 1000건씩만 처리

--각종 계산은 여기에서

FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP

sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5;

END LOOP;

--<방법1> FETCH 된 1000건을 SQL 1회 수행으로 처리

FORALL i IN sales_tbl.FIRST..sales_tbl.LAST

INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,

sales_tbl(i).CUST_ID,

sales_tbl(i).TIME_ID,

sales_tbl(i).CHANNEL_ID,

sales_tbl(i).PROMO_ID,

sales_tbl(i).QUANTITY_SOLD,

sales_tbl(i).AMOUNT_SOLD

);

-- <방법2> LOOP를 사용하는 방법; INSERT SQL 1000 번 수행 (속도는 방법1 보다 30배 이상 느림)

FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP

INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,

sales_tbl(i).CUST_ID,

sales_tbl(i).TIME_ID,

sales_tbl(i).CHANNEL_ID,

sales_tbl(i).PROMO_ID,

sales_tbl(i).QUANTITY_SOLD,

sales_tbl(i).AMOUNT_SOLD

);

END LOOP;

-- UPDATE / DELETE 도 가능

FORALL i IN sales_tbl.FIRST..sales_tbl.LAST

UPDATE SALES SET AMOUNT_SOLD = sales_tbl(i).AMOUNT_SOLD

WHERE PROD_ID = sales_tbl(i).PROD_ID

AND CUST_ID = sales_tbl(i).CUST_ID

AND TIME_ID = sales_tbl(i).TIME_ID

AND CHANNEL_ID = sales_tbl(i).CHANNEL_ID

AND PROMO_ID = sales_tbl(i).PROMO_ID;

EXIT WHEN sales_cur%NOTFOUND;

END LOOP;

CLOSE sales_cur;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200));

END;