03 데이터베이스 Call이 성능에 미치는 영향

03 데이터베이스 Call이 성능에 미치는 영향

# 예시상황
세금납부에 대한 서비스중 하기의 테이블이 있다.
'월요금납부_실적' 테이블은 고객별로 하나의 례코드로 입력
'납입방법별_월요금집계' - 닙입방법코드별로 하나의 례코드로 입력

                                        

기간계(OLTP)와 정보계(DW)서비스에 서는 각각 아래와 같은 방법으로 서비스를 운영한다.
기간계 시스템에서는 주로 고객별로 조회   >> 월요금납부_실적 테이블을 주로 모델링하여 사용
정보계 시스템에서는 주로 납입방법별로 조회 >> 납입방법별_월요금집계 테이블을 주로 모델링하여 사용

각서비스의 데이터를 공유하거나, 기간계에 입력된 데이터를 정보계에 데이터 Migration을 할 때
ETL( 추출, 변환, 적제 ) 배치프로그램을 사용하여 서로 다른 테이블 모델링에 데이터를 넘긴다.
그리고 이 ETL 배치프로그램을 작성할때 Database Call이 덜 발생하게 작성해야 한다.

1) 기간계에서 정보계로의 ETL 
[1] 기존 작성된 ETL 프로시저 설명
*1 - 월요금납부실적 테이블에서 select로 월별로 데이터를 읽는 커서를 생성
*2 - 2009년 3월을 입력값으로 하여 1.에서 만든 커서를 열고,
*3 - 열린 커서에 대해 Loop문을 돌리면서
      각각 하나의 row를 순차적으로 Fetch시키면서 
      Fetch된 2009년 3월 데이터를 매건(하나의 로우) 마다 납입방법이
      지로인지, 자동이체인지, 신용카드인지, 핸드폰인지, 인터넷인지 판별하여
      입력값을 선택해 정보계 납입방법별_월요금집계 테이블에 입력

2) 하기 프로시저 수행시 발생할수 있는 Call수 ( 월요금납부실적 테이블의 건수가 100만건이라 하는 가정 )
[1] Loop문에서 월요금납부실적 테이블의 하나의 로우당 1개의 Fetch Call이 발생
     >> 100만번의 Fetch call이 발생
[2] IF문을 통한 insert 작업 수행시 Call이 한번 발생.
    만약 월요금납부실적 테이블의 하나의 로우가, 납입방법을 지로, 자동이체, 신용카드, 핸드폰, 인터넷 모든 방법을
    사용해 납입했다면, 1번의 loop를 돌때 5번의 if문에 부합하고, 5번의 insert 가 일어나며,
    5번의 execute call이 발생한다.   
[3] 최대 발생할수 있는 call수는
    (   
        Loop 문 안에서 일어나는 1개의 Row당 발생하는 1개의 Fetch Call
    +  Loop 문 안에서 발생할수 있는 1개의 Row당 발생하는 5개의 Execute Call
    ) X 100만 = 600만

즉 하기 프로시져를 수행시 600만 Call이 발생할수 있음
어플리케이션 상에서 Fetch size를 설정할수 있는데, 기본이 1이면 1개의 Row씩 fetch를 하나,
Fetch size가 10이라면 1번의 Fetch시 10개의 Row씩 Fetch를 수행하며,
Fetch size가 1때 보다 Fetch Call수가 1/10로 줄어들수 있다.

PL/SQL로 작성하여 DBMS내에 저장하면 Network 트래픽이 없는 Recursive Call이므로 그나마 빠르게 수행되지만
C, JAVA, VB와 같이 외부 어플리케이션에서 네트워크를 경유해 수행한다면, 아래사항에 의해 부하가 더 일어날수 있다.
즉 User Call이 Recursive Call에 비해 더 심각한 부하를 일으킬수 있는 이유이다.
- Parse Call이 발생하는 상황
- 네트워크 구간에서 소비한 시간
- Database Call이 발생할 때 마다, 매번 OS로부터 CPU와 메모리 리소스를 할당 받으려고 소비한 시간.

---------------------------------------------------------------------------------------------------------------------------------
DECLARE 
   CURSOR C (1NPUT M) NTH VARC HAR2 ) IS 
      SELECT 고객변호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷
      FROM 월요금납부실적 
      WHERE 납입월 = INPUT_MONTH;
      REC C% ROWTYPE;
      LTYPE VARCHAR2 (l);
BEGIN 
   OPEN C('200903');
      LOOP 
         FETCH C INTO REC;
         EXIT WHEN C%NOTFOUND;
         IF REC.지로 > 0 THEN
            LTYPE := 'A';
            INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 닙입빙법코드, 납입금액)
            VALUES ( REC.고객번호, REC.납입월, LTYPE, REC.지로);
         END IF ;
         IF REC.자동이체 > 0 THEN
            LTYPE := 'B';
            INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 닙입빙법코드, 납입금액)
            VALUES ( REC.고객번호, REC.납입월, LTYPE, REC.자동이체);
         END IF ;      
         IF REC.신용카드 > 0 THEN
            LTYPE := 'C';
            INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 닙입빙법코드, 납입금액)
            VALUES ( REC.고객번호, REC.납입월, LTYPE, REC.신용카드);
         END IF ;      
         IF REC.핸드폰 > 0 THEN
            LTYPE := 'D';
            INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 닙입빙법코드, 납입금액)
            VALUES ( REC.고객번호, REC.납입월, LTYPE, REC.핸드폰);
         END IF ;      
         IF REC.인터넷 > 0 THEN
            LTYPE := 'E';
            INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 닙입빙법코드, 납입금액)
            VALUES ( REC.고객번호, REC.납입월, LTYPE, REC.인터넷);
         END IF ;
      END LOOP; 
   COMMIT;   
   CLOSE C;   
END;
---------------------------------------------------------------------------------------------------------------------------------

3) 개선된 쿼리[1] : DECODE문을 사용하여, Fetch Call과 Execute Call을 줄였다.
---------------------------------------------------------------------------------------------------------------------------------
INSERT INTO 납입방법별 월요금집계 (납입월, 고객변호, 납입방법코드, 납입금액)
SELECT 
      x.납입월, 
      x.고객번호, 
      CHR(64 + Y.NO) 납입방법코드,
      DECODE( Y.NO, 1, 지로, 2, 자동이체, 3, 신용키드, 4, 핸드폰, 5, 인터넷) 
FROM 월요금납부실적 x, ( 
               SELECT LEVEL NO 
               FROM DUAL CONNECT BY LEVEL <= 5
               ) y 
WHERE x.납입월 = '200903'
AND y.NO IN ( 
              DECODE(지로, 0, NULL, 1)
            , DECODE(지동이체, 0 , NULL , 2)
            , DECODE(신용카드, 0 , NULL, 3 )
            , DECODE(핸드폰, 0 , NULL, 4 )
            , DECODE(인터넷, 0 , NULL, 5 )
)

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

4) 개선된 쿼리[2] : 위 쿼리도 소트머지 조인 또는 해시조인으로 유도하기 위한 약간의 튜닝이 필요하다.
---------------------------------------------------------------------------------------------------------------------------------
INSERT INTO 납입방법별 월요금집계 (납입월, 고객변호, 납입방법코드, 납입금액)
SELECT /*+ USE_MERGE(X Y) NO_EXPAND NO_MERGE(X) */ 
      x.납입월, 
      x.고객번호, 
      CHR(64 + Y.NO) 납입방법코드,
      DECODE( Y.NO, 1, 지로, 2, 자동이체, 3, 신용키드, 4, 핸드폰, 5, 인터넷) 
FROM (
      SELECT 1 DUMMY, 납입월, 고객번호, 지로, 자동이체, 신용카드, 핸드폰, 인터넷
      FROM 월요금납부실적
      WHERE 납입월 = '200903'
    ) x,
    (
      SELECT 1 DUMMY, LEVEL NO
      FROM DUAL
      CONNECT BY LEVEL <= 5 
    ) y
WHERE x.DUMMY=y.DUMMY
AND y.NO IN ( 
              DECODE(지로, 0, NULL, 1)
            , DECODE(지동이체, 0 , NULL , 2)
            , DECODE(신용카드, 0 , NULL, 3 )
            , DECODE(핸드폰, 0 , NULL, 4 )
            , DECODE(인터넷, 0 , NULL, 5 )
)
---------------------------------------------------------------------------------------------------------------------------------

5) 반대의 ETL 쿼리
---------------------------------------------------------------------------------------------------------------------------------
INSERT INTO 월요금납부실적 
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)
SELECT 고객변호, 
      납입월,
      NVL(SUM(DECODE(납입방법코드, 'A', 납입금액)), 0) 지로,
      NVL(SUM(DECODE(납입방법코드, 'B', 납입금액)), 0) 자동이체,
      NVL(SUM(DECODE(납입방법코드, 'C', 납입금액)), 0) 신용카드,
      NVL(SUM(DECODE(납입방법코드, 'D', 납입금액)), 0) 핸드폰,
      NVL(SUM(DECODE(납입방법코드, 'E', 납입금액)), 0) 인터넷,
FROM 납입방법별_월요금집계
WHERE 납입월 = '200903'
GROUP BY 고객번호, 납입월;
---------------------------------------------------------------------------------------------------------------------------------

문제1) 5종류의 상품이 cart 테이블에 있을때 Fetch Call과 Execute Call이 각각 몇번 발생하겠는가? ( 정답 : 드래그 )
정답 : 
>> goods_no 가 '=' 조건이고, 쿼리 한번당 cart테이블에서 한 종류의 상품만 select되어 insert되게 됨으로
    아래 쿼리를 총 5번 수행해야 5종류의 상품을 cart테이블에 담을수 있다.
    따라서 다섯번의 Parse Call과 다섯번의 Execute Call이 발생하게 된다.
문제2) 아래의 쿼리를 튜닝

insert into wishlist 
select custid , goods_no 
from cart 
where custid = :custid 
and goods_no = :goods_no
정답 : 
>> goods_no 가 'in' 조건이고, 쿼리 한번당 cart테이블에서 다섯 종류의 상품이 모두 select되 insert됨으로써
     아래 쿼리를 총 1번 수행하면 5종류의 상품을 cart테이블에 담을수 있다.
     따라서 한번의 Parse Call과 한번의 Execute Call이 발생하게 된다.

insert into wishlist 
select custid , goods_no 
from cart 
where custid = :custid 
and goods_no in ( :goods_no1, :goods_no2, :goods_no3, :goods_no4, :goods_no5 )