8. PL/SQL 함수 호출 부하 해소 방안

2010.07.11 09:16

토시리 조회 수:14021

사용자 정의 함수사용에 대한 유의점

소량의 데이터 조회 시에만 사용
대용량의 경우, 부분범위처리가 가능한 상황에서 제한적으로 사용
가능한, 조인이나 스칼라 서브쿼리로 전환하는 노력이 필요
사용을 피할수 없는 경우라면, 호출횟수를 최소화하도록 노력한다.

본절에서는 부하를 최소화하기 위해, 다음의 방법들을 알아본다.

페이지 처리 또는 부분범위처리를 활용
Decode 혹은 Case문으로 변환
View Merge를 통한 함수호출 최소화
스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
Deterministic 함수의 캐싱효과 활용
복잡한 함수로직을 풀어 SQL로 구현

(1) 페이지 처리 또는 부분범위처리를 활용

다음의 SQL문을 보자.

memb_nm()과 code_nm()함수가 데이터가 추려지지 않은, 가장 많은 범위에서 그대로 사용되고 있다.

select * 
from ( 
   select rownum no, a.*
   from (
       select memb_nm(매수회원번호) 매도 회원명  
             ,memb_nm(매수회원번호)  매수회원명 
             ,code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명 
             ,code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명 
             ,code_nm('418' , 체결 유형코드) 체결 유형명 
             ,매도계좌번호
             ,매수계좌번호
       from 체결 
       where 종목코드 = : 종목코드 
       and   체결일자 = : 체결일자  
       and   체결시간 between sysdate-10/21/60 and sysdate 
       order by 체결시각 desc 
   ) a 
   where rownum <= 30  
) 
where no between 21 and 30

다음과 같이, 데이터의 범위를 최소화한 후 가장 바깥쪽에서 함수를 적용하는 호출횟수를 줄일수 있다.

select memb_nm(매수회원번호) 매도 회원명  
       ,memb_nm(매수회원번호)  매수회원명 
       ,code_nm('446' , 매도투자자구분코드) 매도투자자구분명 
       ,code_nm('446' , 매수투자자구분코드) 매수투자자구분명 
       ,매도계좌번호
       ,매수계좌번호
from ( 
   select rownum no, a.*
   from (
       select 매수회원번호  
             ,매수회원번호 
             ,매도투자자구분코드
             ,매수투자자구분코드 
             ,매도계좌번호
             ,매수계좌번호
       from 체결 
       where 종목코드 = : 종목코드 
       and   체결일자 = : 체결일자  
       and   체결시간 between sysdate-10/21/60 and sysdate 
       order by 체결시각 desc 
   ) a 
   where rownum <= 30  
) 
where no between 21 and 30

(2) Decode 또는 Case문으로 변환

다음과 같은 경우는 (1)을 적용할수 없다.

전체결과집합을 출력해야하는 경우
안쪽의 인라인뷰의 Order by절에 함수가 사용되는 경우
insert....select문

이런경우는, 함수로직을 풀어 Decode, Case문으로 전환하던가, 스칼라 서브쿼리를 이용하여 캐싱 효과를 줄 수 있다.

여기서는, Decode, Case문으로의 전환에 대해 살펴본다.

다음의 "체결"테이블을 생성, 데이터 100만건을 준비한다.

CREATE TABLE 체결(체결일자, 체결번호, 시장코드, 증권그룹코드, 체결수량, 체결금액) 
NOLOGGING AS 
SELECT '20090315' 
     , ROWNUM  
     , DECODE(SIGN(ROWNUM-100000), 1, 'ST', 'KQ')        -- 유가증권, 코스닥  
     , DECODE(MOD(ROWNUM, 8), 0, 'SS', 1, 'EF', 2, 'EW'  -- 주식, ETF, ELW 
                            , 3, 'DR', 4, 'SW', 5, 'RT'  -- DR, 신주인수권, 리츠 
                            , 6, 'BC', 7, 'MF')          -- 수익증권, 투자회사 
     , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)  
     , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)  
FROM   DUAL 
CONNECT BY LEVEL <= 500000 
UNION ALL 
SELECT '20090315' 
     , ROWNUM + 300000  
     ,(CASE WHEN MOD(ROWNUM, 4) < 2 THEN 'SD' ELSE 'GD' END) 
     ,(CASE WHEN MOD(ROWNUM, 4) IN (0, 2) THEN 'FU' ELSE 'OP' END) 
     , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)  
     , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)  
FROM   DUAL 
CONNECT BY LEVEL <= 500000;

각 증권그룹코드에 때른 분류를 표현하고자 할 때, 코드값을 보고 분류명을 취하고자 한다.

다음과 같은 방법을 생각해볼 수 있다.

코드값을 넘기면 분류명을 반환하는 함수를 작성, 사용.
위의 함수를 DECODE나 CASE문으로 직접기재.
상품분류마스터 테이블과의 조인.

함수작성하여 사용한 예.

CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2)  
RETURN VARCHAR2 
IS 
  L_분류 VARCHAR2(20); 
BEGIN 
  IF 시장코드 IN ('ST', 'KQ') THEN  -- 유가증권, 코스닥 
    IF 증권그룹코드 = 'SS' THEN  
      L_분류 := '주식 현물'; 
    ELSIF 증권그룹코드 IN ('EF', 'EW') THEN  -- ETF, ELW 
      L_분류 := '파생'; 
    ELSE  
      L_분류 := '주식외 현물'; 
    END IF; 
  ELSE   
     L_분류 := '파생'; 
  END IF;

  --SELECT 순서 || '. ' || L_분류 INTO L_분류  
  --FROM   분류순서 
  --WHERE  분류명 = L_분류; 
   
  RETURN L_분류; 
END;

SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY SF_상품분류(시장코드, 증권그룹코드) ※상품분류로 그룹핑
ORDER BY 1 ;

Case문을 사용한 예.

SELECT CASE 
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드  = 'SS' THEN '주식 현물' 
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물' 
       WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생' 
       END 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY ...생략... ※상품분류로 그룹핑
ORDER BY 1 ;

Decode문을 이용한 예.

SELECT DECODE( 시장코드||증권그룹코드 
             , 'STSS', '주식 현물' 
             , 'KQSS', '주식 현물' 
             , 'SDFU', '파생' 
             , 'SDOP', '파생' 
             , 'GDFU', '파생' 
             , 'GDOP', '파생' 
             , 'STEF', '파생' 
             , 'STEW', '파생' 
             , 'KQEF', '파생' 
             , 'KQEW', '파생' 
             , '주식외 현물' ) 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY ...생략... ※상품분류로 그룹핑
ORDER BY 1 ;

각 Select문을 실행시켜보면,

함수를 사용한 쪽이 약 7초가 걸린데 비해, Case/Decode문을 사용한 쪽은 0.6～0.8초정도가 소요되었다.

더욱이, 함수내에 주석처리된 부분인, 분류순서테이블에서 분류순서를 취득하게 한다면

즉, 함수 내에서 Recursive Call을 발생시키면 더욱더 비극적인 결과를 갖고 오게 된다.

CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2)  
RETURN VARCHAR2 
IS 
  L_분류 VARCHAR2(20); 
BEGIN 
  IF 시장코드 IN ('ST', 'KQ') THEN  -- 유가증권, 코스닥 
    IF 증권그룹코드 = 'SS' THEN  
      L_분류 := '주식 현물'; 
    ELSIF 증권그룹코드 IN ('EF', 'EW') THEN  -- ETF, ELW 
      L_분류 := '파생'; 
    ELSE  
      L_분류 := '주식외 현물'; 
    END IF; 
  ELSE   
     L_분류 := '파생'; 
  END IF;

  SELECT 순서 || '. ' || L_분류 INTO L_분류  
  FROM   분류순서 
  WHERE  분류명 = L_분류; 
   
  RETURN L_분류; 
END;

SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY SF_상품분류(시장코드, 증권그룹코드)
ORDER BY 1 ;

이 경우, Recursive Call이 발생하지 않았을 때의 약 6배가 넘는 약45초가 소요된다.

여기까지보면, 직접표기(Decode/Case문을 이용, 데이터의 직접표기)가 절대적으로 좋아 보이지만,

실제 실무에서 이런 코드를 쓰는 경우는 드물다.

유지보수성이 상당히 떨어지기 때문이다.

◆여담한가지
일본의 경우를 볼 때,
프로젝트의 전체 생명주기를 봤을 때 코스트는 초기릴리즈까지가 20～30%, 유지보수에 70～80% 의 비용이 소요된다.
기업대상의 B2B프로젝트의 경우, OLTP환경처럼 동시접속의 폭주가 드물기때문에 병목현상(DB락,쓰레드락 등등)등에 의한 성능저하를 가장 경제적인 레벨까지만 대응한다.
결국, 초고성능의 코딩보다는 누구나 알기쉬운 코딩을 추구하여 유지보수성을 높이려는데 힘을 쏟는다.
물론, 컨슈머계는 유지보수성 보다는 성능을 중요시한다.
유지보수에 드는 코스트를 실감할 수 있는 일화로,
일본에서 꽤 큰 SI업체중 히○치제○소라는 회사는 입찰시에 가격후려치기를 하여 진입하고,
실제이익은 유지보수에서 충당하는 식의 전략을 많이 쓴다고 한다. (경쟁입찰을 이 회사와 붙으면 이기기 힘들다고 한다.)

상품분류마스터 테이블을 설치하여 데이터를 관리하는 것이 실무에 가까운 코드라 볼 수 있다.

즉, 상품분류마스터 테이블과의 조인으로 해결한다.

여기서, 100만건이라는 데이터를 조인해 버리면 오히려 성능저하가 우려될 수 도 있으나, 이는 앞에서 언급한 (1)부분범위처리와 같은 맥락에서 생각해보면 금방 답이 나올 것이다.

SELECT B.분류명 상품분류  
     , SUM(체결건수) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM (SELECT 시장코드, 증권그룹코드 
           , COUNT(*) 체결건수 
           , SUM(체결수량) 체결수량 
           , SUM(체결금액) 체결금액 
      FROM   체결 
      WHERE  체결일자 = '20090315' 
      GROUP BY 시장코드, 증권그룹코드) A, 상품분류 B
WHERE A.시장코드 = B.시장코드 
AND   A.증권그룹코드 = B.증권그룹코드 
GROUP BY B.분류명 
ORDER BY 1 ;

범위를 줄인 상태에서 조인을 걸어 의미없는 데이타와의 결합을 피한다.

(3) View Merge 방지를 통해 함수호출 최소화

지금까지 언급한 방법들이 이런저런 이유로 불가능할 경우도 있을 수 있다.

즉, 함수사용을 피할 수 없는 상황이라면 그 호출 횟수를 줄이는 노력을 해야 할 것이다.

다음은, 100만건 데이터에 대해 3번씩 함수호출이 일어나는 예이다. (총 300만번)

SELECT SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량)) "주식현물_체결수량"    
     , SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '3. 파생', 체결수량)) "파생_체결수량"        
FROM   체결 
WHERE  체결일자 = '20090315' ;

이 쿼리를 실행시켜보면, 약 2분14초가 소요되어, 앞선 예제에서 약45초보다 3배가량 소요되는것을 알 수 있다.

그럼, 다음과 같이 함수호출을 1번으로 줄인다면 어떻게 될까. 1/3로 단축될거라는 기대가 생긴다.

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ;

막상 테스트를 해보면, 위의 결과와 크게 다르지 않다. (약 2분1초 소요)

그 이유는 내부적으로 뷰머지(View Merge)가 발생하기 때문이다.

즉, 뷰머지로 인해 인라인으로 묶기기 전 상태로 돌아가 실행계획이 수립되어버린다.

뷰머지를 무효화 시키는 방법으로 다음을 들 수 있다.

힌트절을 준다.
ROWNUM을 사용한다.

힌트절을 준 예.

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT /*+ NO_MERGE */ SF_상품분류(시장코드, 증권그룹코드) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ;

ROWNUM을 사용한 예.

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT ROWNUM, SF_상품분류(시장코드, 증권그룹코드) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ;

실행시켜보면, 최초의 의도와 같은 1/3의 수행속도를 보인다. (약 45초 소요)

(4) 스칼라서브쿼리의 캐싱효과를 이용, 함수호출 최소화

뷰머지를 무효화시킴으로 상당히 수행속도를 줄이긴 했어도 여전히 45초정도의 시간이 소요된다.

이를, 스칼라 서브쿼리의 캐시효과를 이용하여 더욱더 개선시킬 수 있다.

캐시의 원리는, 서브쿼리의 실행후 입력값과 출력값을 캐시에 저장하는 것이다.

즉, 입력된 값에 대해 캐시에 존재하면 서브쿼리의 실행없이 그 출력값을 그대로 사용한다.

이 방법은 입력값의 종류가 적을 때 효과적이고, 입력값의 종류가 많아지면 CPU사용률이 증가해 오히려 성능저하를 초래한다.

입력값의 종류가 20개인, 다음의 쿼리를 함수를 서브쿼리화 하여 캐싱효과 노려본다.

SELECT SUM(DECODE(상품분류, '1. 주식 현물', 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생', 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT /*+ NO_MERGE */  
        (SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ;

SQL트레이스를 걸어보면, 입력값이 20종류이기에 20번의 함수호출만 일어날 거라는 예상이 빗나갔음을 알 수 있다.

SELECT 순서||'.'||:B1  
FROM 
 분류순서 WHERE 분류명 = :B1  
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          0          0           0 
Execute 725010    11.37      10.39          0          0          0           0 
Fetch   725010     17.57      17.63          0    2175030          0      725010 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total   1450021     28.95      28.03          0    2175030          0      725010

이는 해시충돌에 의한 현상으로 다음과 같다.

서브쿼리의 입출력값이 캐쉬에 존재.
해시값이 충돌.
기존 캐쉬엔트리를 유지. (새 값으로 엔트리가 갱신되지 않는다.)
해당 서브쿼리를 다시 수행.

결국, 캐싱효과를 보지 못하게 되는것이다.

이는 다음과 같이 캐시사이즈를 조정함으로 해결할 수 있다. (10g)

ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;

※8i, 9i는 256개의 엔트리를 캐싱한다.

다시 실행해보면, 예상했던 호출횟수를 확인할 수 있다.

SELECT SUM(DECODE(상품분류, '1. 주식 현물', 체결수량)) "주식현물_체결수량"  
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량" 
     , SUM(DECODE(상품분류, '3. 파생', 체결수량)) "파생_체결수량" 
FROM ( 
  SELECT /*+ NO_MERGE */  
        (SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류 
       , 체결수량  
  FROM   체결  
  WHERE  체결일자 = '20090315' 
) ; 

경   과: 00:00:00.79 
 
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          0          0           0 
Execute     20     0.00       0.00          0          0          0           0 
Fetch       20      0.00       0.00          6         60          0          20 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total       41      0.00       0.00          6         60          0          20

(5) Deterministic 함수의 캐싱효과활용

10gR2에서는 함수선언시 Deterministic 키워드를 넣어주면 스칼라 서브쿼리를 덧입히지 않아도 캐싱효과를 나타낸다.

일반 스칼라 서브쿼리의 캐싱과 Deterministic 함수의 캐싱의 차이점.
일반스칼라서브쿼리 : 입출력값이 CGA에 캐싱되어 데이터베이스 Call 내에서만 유효.
Deterministic 함수 : 입출력값이 UGA에 캐싱되어 세션내에서 유효.
※CGA는 Call관련 정보를 저장하는 공간으로 Call이 발생시 생성, 종료시 소멸된다.
※UGA는 유져관련 정보를 저장하는 공간으로 세션접속되어 끊기는 동안 유지된다.

Deterministic 함수를 작성.

create or replace function ACCUM (p_input number) return number 
DETERMINISTIC
as 
  rValue number := 0 ; 
  call_cnt number := 0; 
begin 
  dbms_application_info.read_client_info(call_cnt); 
  if call_cnt is null then 
    call_cnt := 0; 
  end if; 
 
  *dbms_application_info.set_client_info(call_cnt + 1);* 
 
  for i in 1..p_input loop 
    rValue := rValue + i ; 
  end loop; 
  return rValue ; 
end;

1000000번의 함수호출을 발생시켜보자.

select sum(accum_num) 
from ( 
  select accum(mod(rownum, 50)) accum_num 
  from dual 
  connect by level <= 1000000 
) ; 

경   과: 00:00:01.54

함수 호출 횟수를 확인한다.

select sys_context('userenv', 'client_info') from dual; 
 
SYS_CONTEXT('USERENV','CLIENT_INFO') 
----------------------------------------------------------------- 
50

실제적으로는 50번의 함수 호출이 발생되었다.

이는 입력값이 mod(rownum, 50)으로 50개이기 때문이다.

이번에는 Deterministic 키워드를 제거한 함수를 작성하여, 같은 테스트를 해보자.

exec dbms_application_info.set_client_info( NULL ); 
select sum(accum_num) 
from ( 
  select accum(mod(rownum, 50)) accum_num 
  from dual 
  connect by level <= 1000000 
) ; 

경   과: 00:00:13.56

함수 호출 횟수를 확인한다.

select client_info 
from   v$session 
where  sid = sys_context('userenv', 'sid'); 
 
CLIENT_INFO 
------------------------------------------------------- 
1000000

1000000번의 함수호출이 발생했음을 알 수 있다.

이 게시물을

번호	제목	글쓴이	날짜	조회 수
27	8. 애플리케이션 커서 캐싱	토시리	2010.06.29	6608
26	9. Static vs. Dynamic SQL [1]	balto	2010.07.04	18342
25	10. Dynamic SQL 사용 기준	balto	2010.07.04	8643
24	11. Static SQL 구현을 위한 기법들	실천하자	2010.07.05	12084
23	4. Array Processing 활용	휘휘	2010.07.05	18238
22	1. Call 통계	실천하자	2010.07.05	10441
21	5. Fetch Call 최소화	휘휘	2010.07.05	16839
20	5장. 데이터베이스 Call 최소화 원리	휘휘	2010.07.05	6102
19	2. User Call vs. Recursive Call	토시리	2010.07.07	9050
18	3. 데이터베이스 Call이 성능에 미치는 영향	토시리	2010.07.07	11664
17	6장. I/O 효율화 원리	휘휘	2010.07.08	6404
16	4. Prefetch	balto	2010.07.10	28432
15	5. Direct Path I/O	balto	2010.07.10	12189
»	8. PL/SQL 함수 호출 부하 해소 방안	토시리	2010.07.11	14021
13	6. 페이지 처리의 중요성	실천하자	2010.07.11	6819
12	2. Memory vs. Disk I/O	휘휘	2010.07.12	7439
11	3. Single Block vs. Multiblock I/O	휘휘	2010.07.12	9167
10	7. PL/SQL 함수의 특징과 성능 부하	실천하자	2010.07.12	12599
9	1. 블록 단위 I/O	토시리	2010.07.12	9380
8	1. Library Cache Lock	balto	2010.07.17	12800

첫 페이지 1 2 3 4 끝 페이지

쓰기

태그

bysql.net

로그인

8. PL/SQL 함수 호출 부하 해소 방안

댓글 0