External Table을 이용 물리적 파일을 처리하는 DML작업 성능개선.

일반적으로, OLTP 프로그램(온라인)에 대한 튜닝을 진행하는데 중요한 성능개선방법은 국내에 나와있는 많은 책들에서 이미 소개된 것과 같이

 

-      인덱스에 대한 재조정

-      SQL 재 작성

-      힌트 조정

-      페이징 기법

-      Memory Keep

-      Parameter 조정

-      기타

 

등을 활용하여 프로그램의 일량을 최소화 할 수 있도록 효율적인 Access Path로 유도하는 것이 중요한 성능개선 방법이 될 것이다. 온라인 프로그램과 마찬가지로 배치 프로그램에서도 물론 위의 개선방법들을 많이 사용을 하게 된다.

하지만, 배치 프로그램을 계속 진행하면서 익히 알고 있는 다양한 성능개선방법으로만 튜닝을 진행 하는 것은 뭔가 알 수 없는 벽을 만난듯한 그런 느낌을 많이 받았다. 그런 벽의 존재가 기존에 일반적으로 널리 알려진 성능개선방법과 오라클에서 제안하는 다양한 신기술들이 접목이 되었을 때 배치 프로그램에 대한 성능개선이 좀더 잘 이뤄진다라는 그런 사실이 날 가로막고 있었던 거다.

 

오라클 제품이 버전 업그레이드 되면서 새롭게 등장하는 많은 신기술, 신기능들에 대한 지식이 필요하다라는 생각이확~ 들게 했던 계기가 있었는데

물리 파일을 읽어 조회작업과 트랜잭션 처리(UPDATE)를 수행하는 프로그램 중 성능문제가 심각한 프로그램이 있어 이를 해결하기 위해 조치를 취한 방법으로 배치 프로그램(or DW) 튜닝을 지속적으로 해 오셨던 분들은 모두들 알고 있는 기능이겠지만 실제 적용을 해보지 않았던 분들에게는 생소한 기능들 중(나 역시도 한번도 접하지 않고 기능이 나온 지만 알고 있었던 ) 오라클이 9i 에서 첨 소개한 External Table이 있다.  External Table에 대한 정보를 찾고 적용하게 된 것은 아래의 그림과 같은 로직으로 수행이 되는 배치 프로그램이 있었고, 해당 프로그램은 수납관리 프로그램 중 아주 중요한 역할을 수행하고 수행시간이 아주 중요한 배치 프로그램으로 성능개선이 꼭 되어야 하는 핵심 프로그램 중 하나의 내용이다.

위의 배치 프로그램의 로직을 보면, 물리 파일에서 최대 300만 건을 한 건씩 읽어, 300만 건에 대한 한 건씩

UPDATE를 수행하는 프로그램으로 최대 300만 건 수행하는데 3~4시간이 소요되어 수행시간이 길었으며, 고객의 요구는 최대 30분 이내에 처리를 해달라는 것 이여서 상당히 고민에 빠지게 하는 배치 프로그램 이었다.

 

오라클 9i 이전에는 물리 파일에 있는 내용을 읽어 처리하는 조회작업이나 DML 작업(트랜잭션 처리)의

경우는 Temp(임시 처리용) Table을 생성하고 SQL*Loader를 이용하여 DB에 Insert 한 이후 배치 처리하는

방법이 있을 것이고, 물리 파일(업무적인 병렬처리)을 읽어 배치 프로그램 로직을 수행(조회,DML작업)하는 방법이 일반적인 방법일 것이라 생각이 들었다.

그렇지만, 이 방법들은 식빵에 쨈을 발라서 먹지 않을 때 혹은 저녁을 먹을 때 반찬 중에 김치가 빠져 있을 때 느끼는… 뭔가 허전하고 뭔가 중요한 것이 빠져있는 느낌이 상당히 많이 들게 되었고 또한, 30분 이내에 배치 프로그램의 수행이 완벽히 끝낼 수 있을지 의문이 들었고 기존에 사용되던 방식들을 이용하여 30분 이내에 처리가 되더라도 업무적인 병렬 처리 등등의 처리를 활용하게 되면 프로세스간의 경합과 비효율적인 서버 자원을 사용하게 될 가능성이 많게 되고, 프로그램 수행을 하기 위한 관리적인 요소가 별도로 들어가야 할 것으로 판단되어, 오라클 DW 성능개선 방법들을 오라클 매뉴얼등등을 활용하여 문제의 배치 프로그램의 성능개선을 위한 좋은 방법이 있는지도 꼼꼼히 훑어보기 시작했다.

 

그러던 와중 “앗~ 이것이다.” 라는 기능이 있는 것이었다.
그것도 Oracle 9i에서 나온 기능 중에.

그것이 바로, External Table 이었다.

External Table은 물리 파일의 처리에 대한 문제점을 해결하기 위해서 Oracle 9i에서 새로 나온 기능으로 문제의 배치 프로그램을 External Table + Merge Into ~ 구문을 활용하면 아주 괜찮은 성능개선이 될 것이라는 확신이 생겨났다. 또한, 이곳 ORACLE 버전이 10.2.0.3으로 10g부터는 Merge Into ~ 구문도 Upsert (UPDATE+INSERT)으로 작성하지 않고 UPDATE문만 처리하는 것도 가능하기에 문제의 배치 프로그램의 성능 개선방법으로 걸림돌이 되지는 않았다. External Table과 Merge Into~구문을 활용하여 배치 프로그램을 개선방법을 적용하여 아래와 같이 개선이 되었다.

물리 파일의 300만 건의 읽는 작업은 External Table을 활용하여 한번 읽는 작업으로 처리를 하고, 300만 건의 UPDATE 처리는 Merge Into ~ 구문에서 PARALLEL DML 처리로 하여 적용하였고, 개선 전에 300만 건 처리 시 3~4시간 소요되는 것이 적용 후 최대 15분 이내에는 처리가 완료되는 것을 확인할 수 있었다.

 

적용했던 사례는 아래와 같다.

 

먼저, 물리 파일을 읽기 위하여 UTL DIRECTORY에 물리 파일이 있는 DIRECTORY를 UTL_FILE_DIR 파라미터에 설정을 한다.

 

STEP1. utl directory에 위치시키기

 

SQL> show parameter utl

     

NAME                  TYPE        VALUE

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

create_stored_outlines   string

utl_file_dir              string     /usr/tmp, /usr/tmp, /app/oracle/product/10.
                                 2.0/db/appsutil/outbound/NDB2_cdb4, /usr/tmp

 

: /home1/var/lll/projs/if/work 폴더(물리적 파일위치) UTL_FILE_DIR 파라미터에 추가하여야 함.

 

그리고, 물리 파일을 읽어 들일 External Table을 생성한다.

STEP2. External Table 생성하기

 

- Directory 지정

    CREATE OR REPLACE DIRECTORY dat_dir AS '/home1/var/lll/projs/if/work ';

  CREATE OR REPLACE DIRECTORY log_dir AS '/home1/var/lll/projs/if/work ';

    CREATE OR REPLACE DIRECTORY bad_dir AS '/home1/var/lll/projs/if/work ';

    CREATE OR REPLACE DIRECTORY dat_dir AS '/usr/tmp';

    CREATE OR REPLACE DIRECTORY log_dir AS '/usr/tmp';

    CREATE OR REPLACE DIRECTORY bad_dir AS '/usr/tmp';

 

    GRANT READ ON DIRECTORY dat_dir TO 배치유저; à 프로그램 수행 유저

    GRANT WRITE ON DIRECTORY log_dir TO 배치유저;

    GRANT WRITE ON DIRECTORY bad_dir TO 배치유저;

 

- External Table 생성하기 

    CREATE TABLE TB_PAYMENT_UPDATE (

                            payment_data varchar2(180)

    ) ORGANIZATION EXTERNAL

    (

       TYPE ORACLE_LOADER

       DEFAULT DIRECTORY dat_dir

       ACCESS PARAMETERS

       (

         records delimited by newline

         badfile bad_dir:'ext_test%ar_%p.bad

         logfile log_dir:'ext_test%ar_%p.log'

         fields terminated by ','

         missing field values are null

         (

              payment_data

         )

       )

       LOCATION ('payment_update.dat')

    )

    PARALLEL 4

    REJECT LIMIT UNLIMITED ;

    ALTER TABLE 배치유저. TB_PAYMENT_UPDATE NOPARALLEL

 

 

/home1/var/lll/projs/if/work/payment_update.dat(물리 파일)을 읽을 준비는 다 되었다.

 

위에서 물리파일의 하나의 Row가 컬럼 구분자 없이 구성이 되어 있어, 물리 파일을 TB_PAYMENT_UPDATE라는

External Table로 불러들이게 되는데, 물리 파일의 한 Row(한줄)을 Payment_data 라는 하나의 Column으로 매칭을

시켜서 데이터를 가져와서 원하는 데이터를 자리수로 계산하여 잘라 필요한 데이터를 사용하게 된다. 물리 파일을 읽고,

물리 파일의 데이터를 업데이트하는 로직을 Merge Into ~ 구문을 활용하여 처리를 변경하여 최종 반영하게 된다.

 

STEP3. SQL 변경하기

 

개선 전 :

- FILE READ : MAX 300만건 à /home1/var/lll/projs/if/work/payment_update.dat (물리 파일)

     - UPDATE STATEMENT 수행 : MAX 300만번 수행됨.

      UPDATE TB_PAYMENT SET

             APPLICATION_ID         = :sql_application_id,

             WDRW_IMPSB_RSN_CD      = :sql_wdrw_impsb_rsn_cd,

             ATST_RETN_AMT          = to_number(:atst_retn_amt2),

             SYS_UPDATE_DATE        = SYSDATE

       WHERE WORK_YYMM  BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')

         AND TO_CHAR(SYSDATE,'YYYYMM')

         AND BILL_ACNT_GRP_NO       = MOD(:sql_bill_acnt_no,20) + 1

         AND BILL_ACNT_NO           = :sql_bill_acnt_no

         AND ATST_BILL_SEQNO        = to_number(:atst_bill_seqno2)

         AND FILE_PRSS_NO           = :sql_file_prss_no

 

개선 후 :

- FILE READ : 테이블 두번 FTS로 해석 à Merge Into ~

- UPDATE   : Merge Into

~

 

MERGE /*+ USE_HASH(T B) */ INTO  TB_PAYMENT T

            USING ( SELECT /*+ LEADING(H P U) USE_HASH(H P U) FULL(H) FULL(P) FULL(U) */ à File 읽기

                                P.WORK_YYMM       ,  

                                P.BILL_ACNT_GRP_NO,   

                                P.BILL_ACNT_NO    ,                        

                                H.FILE_PRSS_NO    ,

                                P.ATST_BILL_SEQNO ,

                                P.WORK_DV_CD      ,

                                U.WDRW_IMPSB_RSN_CD,

                                TO_NUMBER(U.ATST_RETN_AMT) ATST_RETN_AMT

                          FROM  TB_PAYMENT P,

                              (

                                SELECT SUBSTR(PAYMENT_DATA,36,9) BILL_ACNT_NO, à 파일에서 해당 데이터 위치

                                       SUBSTR(PAYMENT _DATA,45,10) ATST_BILL_SEQNO,

                                       SUBSTR(PAYMENT _DATA,9,11) ATST_RETN_AMT,

                                       SUBSTR(PAYMENT _DATA,3,6) WDRW_IMPSB_RSN_CD

                                FROM  TB_PAYMENT_UPDATE

                                WHERE PYM_DATA LIKE 'RD%'  ) U,

                             (  SELECT SUBSTR(PYM_DATA,24,5) FILE_PRSS_NO

                                FROM  TB_PAYMENT_UPDATE

                                WHERE PYM_DATA LIKE 'RH%'

                              ) H

                         WHERE P.WORK_YYMM  BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),‘

                                    YYYYMM') AND TO_CHAR(SYSDATE,'YYYYMM')

                           AND P.BILL_ACNT_GRP_NO   = MOD(U.BILL_ACNT_NO,20) + 1

                           AND P.BILL_ACNT_NO       = U.BILL_ACNT_NO

                           AND P.ATST_BILL_SEQNO    = U.ATST_BILL_SEQNO

                           AND P.FILE_PRSS_NO       = H.FILE_PRSS_NO

                       ) B

                ON ( -- PK로 해당건만 UPDATE -> ROWID 비교도 가능함.

                          T.WORK_YYMM        = B.WORK_YYMM

                      AND T.BILL_ACNT_GRP_NO = B.BILL_ACNT_GRP_NO

                      AND T.BILL_ACNT_NO     = B.BILL_ACNT_NO

                      AND T.FILE_PRSS_NO     = B.FILE_PRSS_NO

                      AND T.ATST_BILL_SEQNO  = B.ATST_BILL_SEQNO

                      AND T.WORK_DV_CD       = B.WORK_DV_CD

                )

                WHEN     MATCHED THEN

                         UPDATE SET  T.APPLICATION_ID         = :sql_application_id,

                                        T.WDRW_IMPSB_RSN_CD  = B.WDRW_IMPSB_RSN_CD ,

                                        T.ATST_RETN_AMT        = B.ATST_RETN_AMT,

                                        T.SYS_UPDATE_DATE      = SYSDATE

 

개선내용확인하기 - SELECT 처리 TEST 결과 (FILE READ -> UPDATE 대상 선별)

 

call     count       cpu    elapsed       disk      query    current        rows

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

      Parse        1      0.06       0.05          0        663          0           0

      Execute      1      0.00       0.00          0          0          0           0

      Fetch    68321     38.02      44.25          0      95293          0     1024792

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

      total    68323     38.08      44.31          0      95956          0     1024792

     

      Misses in library cache during parse: 1

      Optimizer mode: FIRST_ROWS

      Parsing user id: 427

     

      Rows     Row Source Operation

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

      1024792  FILTER  (cr=96701 pr=0 pw=0 time=40230686 us)

      1024792   HASH JOIN  (cr=96701 pr=0 pw=0 time=40230278 us)

      1024792    EXTERNAL TABLE ACCESS FULL TB_PAYMENT_UPDATE (cr=704 pr=0 pw=0 time=7209313 us)

      1194024    HASH JOIN  (cr=95997 pr=0 pw=0 time=18037752 us)

            1     EXTERNAL TABLE ACCESS FULL TB_PAYMENT_UPDATE (cr=704 pr=0 pw=0 time=7288229 us)

      1194024     PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=95293 pr=0 pw=0 time=8359986 us)

      1194024      PARTITION LIST ALL PARTITION: 1 20 (cr=95293 pr=0 pw=0 time=7165527 us)

      1194024       TABLE ACCESS FULL TB_PAYMENT PARTITION: KEY KEY (cr=95293 pr=0 pw=0 time=7168107 us)

 

 

 

외부 성능개선 컨설팅 중 발생했던 배치 프로그램의 문제를 접근하는 방향이 익히 알고 있는 성능개선 방법뿐만 아니고 잘 사용되지 않는 오라클 기술들을 잘 활용하면 문제 접근이 의외로 쉬울 수 있고, 성능개선 효과 또한 아주 큰 경우가 많이 있을 것으로 각된다. 오라클 버전 업그레이드 되고, 오라클이 신기술(New Feature)을 발표하면 이러한 기술들을 최소한 그 기능이 어떻게 활용이 되면 좋을지 그리고, 향후 적용할 수 있는 영역이 어딘지 등등에 대해 고민하는 것도 아주 좋을 것같다.