[SAS tip] ORACLE insert 속도 개선 : BULKLOAD, INSERTBUFF

 

SAS에서 oracle로 데이터를 insert할 경우, 보통은 1row를 넣고 commit 하고, 1row를 넣고 commit을 합니다.

그렇기 때문에 대용량 데이터를 올릴 경우 속도가 매우 느립니다. (때로는 참을 수 없을 만큼!! -_-)

 

속도를 개선할 수 있는 방법은 두 가지가 있습니다.

 

 

< 수행속도 비교 : 2천만건 insert 시. >

    * 해당 속도는 시스템 환경과 대상 데이터셋의 규모에 따라 변경될 수 있습니다.

옵션구분	수행시간
옵션없음	1시간 이상
BULKLOAD = YES	6분
INSERTBUFF = 1000	5분

 

 

 

1. BULKLOAD 옵션 : SQL*Loader를 통해 수행

 

ex.

        proc sql;

           create table oracle.prodscore_click_temp (BULKLOAD=YES) as

           select prodid, keyword, keyword_value, change

              from &lib1..keyword_value;

        quit;

 

BULKLOAD 옵션을 쓰면, 해당 작업이 SAS를 통해서 일어나지 않고 SQL*Loader를 통해 일어납니다.

관련하여 .dat .ctl .log  세 파일이 생성됩니다.

    - BL_테이블명_0.dat :  오라클에 로딩하기 위해서 데이터를 변환한 파일입니다.

    - BL_테이블명_0.ctl :  로딩에 필요한 BULKLOAD설정관련 오라클 statement가 들어가고,

    - BL_테이블명_0.log : 은 SQL*Loader log 입니다.

 

작업 완료 후에도 위 세 파일은 삭제되지 않는다고 하는데, 제가 보기에는 삭제되는 것 같습니다 -_-;

다른 파일은 큰 문제가 아닌데, 만약 .dat 파일이 삭제되지 않는다면 원본 데이터 그대로이기 때문에 용량 차지가 클 것입니다.

.dat 파일을 삭제하길 원한다면 아래와 같은 옵션을 추가하면 됩니다.

 

ex.

        proc sql;

           create table oracle.prodscore_click_temp (BULKLOAD=YES BL_DELETE_DATAFILE=YES) as

           select prodid, keyword, keyword_value, change

              from &lib1..keyword_value;

        quit;

 

 

2. INSERTBUFF 옵션 : SAS 내부 수행

 

반면에 INSERTBUFF 옵션을 사용하면 지정된 숫자만큼의 용량에 해당하는 row를 한 번에 insert 함으로써 속도 개선이 가능합니다.

default 값은 1이고, 양수만 가능하며 max는 32767입니다.  해당 수치는 사용할 buffsize를 의미합니다.

따라서 max를 사용할 경우 시스템 속도가 느려질 수 있으므로, 서버의 메모리와 네트웍 상황을 보아가며 튜닝 최적값을 찾아서 사용해야 합니다.

 

ex. data step 단계에서 지정.

        proc sql;

           create table oracle.prodscore_click_temp (INSERTBUFF=1000) as

           select prodid, keyword, keyword_value, change

              from &lib1..keyword_value;

        quit;

 

ex. libname 문장에서 지정.

        libname  oracle     oracle user=&user     orapw=&pw     path="&path"  INSERTBUFF=1000;

 

 

3. 기타.

 

a. proc dbload 프로시져의 commit 은 숫자를 늘려보았자 속도 개선 효과는 거의 없습니다.

1row씩 insert를 한 뒤, 지정된 숫자가 지나간 뒤에 commit을 하는 것이기 때문입니다.

 

ex.

        proc dbload dbms=oracle data=&lib1..keyword_value;

            path = &path;

            user = &user;

            pw   = &pw;

            table= prodscore_click_temp;

            commit = 100000;

            load;

       run;

 

b. DBCOMMIT옵션도 있는데. 이것도 a와 비슷한 것 같습니다. (부정확;)

ex.

        data oracle.prodscore_click_temp(dbcommit = 10000;);

            set &lib1..keyword_value

       run;