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;


 

'ORACLE > SQL' 카테고리의 다른 글

PLSQL - 다차원 콜렉션  (0) 2018.01.24
피벗 (행->열)  (0) 2017.12.22
오라클 정규식 사용 팁  (0) 2017.07.07
잡동사니 퀴리  (0) 2017.07.05
DML 수행 시 내부 절차  (0) 2017.06.20

+ Recent posts