[펌] 대용량 데이터 처리(Bulk SQL)

요약1. 커서에 한번에 넣고2. Bulk Collect into XX Limit 40000
4만건단위로 XX data SET 처리 (배열??)3. FORALL
4만건 단위로 20번에 INSERT 처리

------------------------------------------------------------------------------------------------------------------------
특정 프로시져에서 CPU사용량이 높다고 이것을 튜닝해 달라고 요청이 왔는데
프로시져를 까보니 데이타를 80만건정도를 계속 INSERT시키는 그런 프로그램이었다.

80만건을 계속 INSERT한다는것 자체가 의심이 갔지만 일단 그렇게 해야한다고 하니 이것에 대해 분산처리할 수 있는 방법이 필요했다. 80만건을 INSERT SELECT로 한꺼번에 처리하면 CPU사용량도 늘어나지만 메모리사용량도 늘어난다.

여기서 Bulk Sql을 사용하면 CPU사용량은 비슷하지만 메모리 사용량은 분산처리할 수 있다.(구글링으로 알아낸 결과임.^^)

 

분산처리는 커서를 이용해 루프를 돌면서 특정건수(LIMIT) 만큼을 INSERT처리하는 방식이다.

 

예문을 보면서 알아보자.

 

[사용예]

 

CREATE OR REPLACE PROCEDURE SP_TEST AS

BEGIN

  INSERT INTO SALES

  SELECT *

    FROM TT_DATA;

END;

• TT_DATA 테이블의 데이타가 80만건이라고 가정하자.
• SP_TEST 프로시져를 호출하면 SALES 테이블은 80만건이 다 들어갈때까지 LOCK이 걸리게 된다.

 

이것을 분산처리 방식으로 변경해 보자.

필자는 커서를 다이나믹쿼리로 구성했다.

 

[Bulk SQL 사용예]

 

CREATE OR REPLACE PROCEDURE SP_TEST AS

  TYPE Gv_DATA_LIST IS TABLE OF SALES%ROWTYPE;  -- TYPE 선언

  Gv_DATA      Gv_DATA_LIST;                    -- TYPE변수 할당

  CUR1         SYS_REFCURSOR;                   -- 커서변수 선언

  Gv_SelectSQL LONG := ' ';

  --

BEGIN  

  Gv_SelectSQL := ' SELECT * FROM TT_DATA ';

  -- 

  OPEN CUR1 FOR 

    Gv_SELECTSQL;  

  LOOP

    --

    FETCH CUR1 BULK COLLECT INTO Gv_DATA LIMIT 40000;

    --

    FORALL I IN Gv_DATA.FIRST..Gv_DATA.LAST

      INSERT INTO SALES VALUES Gv_DATA(I) ;

    --

    EXIT WHEN CUR1%NOTFOUND;

    --

  END LOOP;

END;

• 선언부에는 SALES 테이블을 TYPE변수로 선언한다.
• 커서변수를 선언한다.
  (일반적으로 이곳에 쿼리를 넣을수도 있지만 필자는 다이나믹쿼리로 하기때문에 변수만 선언했다.)
• 커서를 오픈후 루프를 돌면서 FETCH를 한다.(핵심1)
  FETCH CUR1 BULK COLLECT INTO Gv_DATA LIMIT 40000;
  
• FORALL문을 이용해서 데이타를 넣는다.(핵심2)
  - FORALL I IN Gv_DATA.FIRST..Gv_DATA.LAST
  - 40000건을 SQL1회로 처리하게 된다. 
• FOR문을 사용할 경우 SQL 40000회가 실행된다.
• 20번 루프를 돌면서 처리가 끝나게 된다.
• 40000만건씩 INSERT처리되기 때문에 다음 처리할때 여유있는 메모리를 사용할 수 있게 된다.
• 여러사람이 동시에 호출을 한다면 40000건이 INSERT된 후 대기하고 있던 사용자가 사용할 수 있게 된다.

 

https://m.blog.naver.com/PostView.nhn?blogId=inhim&logNo=100154127152&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2F

 

BULK SQL 사용예

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

m.blog.naver.com

 

 

http://wiki.gurubee.net/pages/viewpage.action?pageId=3901299

대량 처리 - 21.[종료]대용량 데이터베이스 스터디 - 개발자, DBA가 함께 만들어가는...

Browse Log In Dashboard 21.[종료]대용량 데이터베이스 스터디 … 2009년 하반기 대용량데이터베이스 스터디 대량 처리 대량 처리 Added by ...

wiki.gurubee.net

 

대량 처리 - [종료]대용량 데이터베이스 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고

BULK SQL 사용예