08.PL/SQL 함수 호출 부하 해소

사용자 정의 함수 사용 방법

• 소량의 데이터 조회시만 사용
• 대용량 처리시 부분범위처리 제한적 사용
• 조인 또는 스칼라 함수로 변환
• 호출 횟수 최소화

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

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' , 매수 투자자 구분코드) 매수투자자구분명 ,
       code_nm('418' , 체결 유형코드) 체결 유형명 ,
       매도계좌번호,
       매수계좌번호 ,
       체결시각 ,
       체결수량 ,
       체결가 ,
       체결수량 * 체결가 체결금액
  from (select rownum no, a.*
        from (select 매도회원번호 ,
                       매수회원번호 ,
                       매도투자자구분코드,
                       매수투자자구분묘드 ,
                       매도계화변호,
                       매수계좌번호 ,
                       체결유형코드 ,
                       체결시각 ,
                       체결수량 ,
                       체결가
                  from 체결
                 where 종목코드 = :종목코드
                   and 체결일자 = :체결일자
                   and 체결시각 between sysdate - 10/24/60 and sysdate
                 order by 체결시각 desc ) a
         where rownum <= 30
       )
  where no between 21 and 30 
)

• 페이지 처리를 하지 않더라고 부분범위처리가 가능한 상황이라면, 데이터 전송 마지막 단계에서 함수 호출

h5.(2)Decode 함수 또는 Case 문으로 변환

• 함수 로직을 풀어서 decode, case 문으로 전환 또는 조인문으로 구현 가능여부 확인
• 함수에 입력되는 값의 종류가 많지 않다면, 스칼라 서브쿼리의 캐싱효과를 이용

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 

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; 
    
  RETURN L_분류; 
END; 

• 함수 사용

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

경   과: 00:00:07.31 

• 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  
       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 
ORDER BY 1 

경   과: 00:00:00.68

• Decode 문으로 변경

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

경   과: 00:00:00.87

• Recursive call 발생을 위한 테이블생성, 함수 수정

CREATE TABLE 분류순서(분류명, 순서) 
AS 
 SELECT '주식 현물', 1 FROM DUAL 
 UNION ALL 
 SELECT '주식외 현물', 2 FROM DUAL 
 UNION ALL 
 SELECT '파생', 3 FROM DUAL ; 
 

CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 IN VARCHAR2, 증권그룹코드 IN VARCHAR2)  
RETURN VARCHAR2 
IS 
  L_분류 VARCHAR2(14); 
BEGIN  
   
  SELECT 순서 || '. ' || L_분류 INTO L_분류  
  FROM   분류순서 
  WHERE  분류명 = L_분류; 
   
  RETURN L_분류; 
END; 

• Recursive call 이 100만번 수행

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

경   과: 00:00:45.42

• 분류 및 메타정보로써 관리하는 테이블 생성 후 조인

CREATE TABLE 상품분류(시장코드, 증권그룹코드, 분류명) 
AS 
    SELECT 'ST', 'SS', '주식 현물'    FROM DUAL UNION ALL 
    SELECT 'ST', 'EF', '파생'         FROM DUAL UNION ALL 
    SELECT 'ST', 'EW', '파생'         FROM DUAL UNION ALL 
    SELECT 'ST', 'DR', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'ST', 'SW', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'ST', 'RT', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'ST', 'BC', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'ST', 'MF', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'KQ', 'SS', '주식 현물'    FROM DUAL UNION ALL 
    SELECT 'KQ', 'EF', '파생'         FROM DUAL UNION ALL 
    SELECT 'KQ', 'EW', '파생'         FROM DUAL UNION ALL 
    SELECT 'KQ', 'DR', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'KQ', 'SW', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'KQ', 'RT', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'KQ', 'BC', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'KQ', 'MF', '주식외 현물'  FROM DUAL UNION ALL 
    SELECT 'SD', 'FU', '파생'         FROM DUAL UNION ALL 
    SELECT 'SD', 'OP', '파생'         FROM DUAL UNION ALL 
    SELECT 'GD', 'FU', '파생'         FROM DUAL UNION ALL 
    SELECT 'GD', 'OP', '파생'         FROM DUAL 
; 

ALTER TABLE 상품분류 
ADD  
CONSTRAINT 상품분류_PK PRIMARY KEY(시장코드, 증권그룹코드)
; 

• 100건을 그대로 조인하는 것이 아니고(hash join 처리시 빠를수 있음), group by 선행 후 20건에 대해서만 조인하므로 성능이 느리지 않음

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

경   과: 00:00:00.68

h5.(3)뷰머지(View Merge)방지를 통한 함수 호출 최소화

• 100만건 스캔하면서 건당 SF_상품분류 함수 3번 수행하여 총 300만번 호출

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

경   과: 00:02:13.51

• 함수 호출 1/3 로 감소

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

경   과: 00:02:13.64

• 뷰머지 방지
  (1) no_merge 힌트

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

경   과: 00:00:45.34 

(2) rownum 사용

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

경   과: 00:00:45.29 

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

경   과: 00:00:46.50

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

• 스칼라 서브쿼리를 사용하면 오라클은 그 수행횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장
• 스칼라 서브쿼리에 있어 입력값은 거기서 참조하는 메인 쿼리의 컬럼 값

select  (
  select  d.dname              /*출력값 d.dname */
  from  dept d
  where d.deptno = e.empnno    /*입력값 e.empno */  
 )
from  emp e

• 서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력값을 리턴, 없으면 쿼리 수행 후 캐시에 저장
• 함수 호출 횟수를 줄이는데 사용, 함수를 Dual 을 이용해 스칼라 서브쿼리로 감싸서 캐시

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

경   과: 00:00:34.42

• 함수 호출이 725,010 번 발생

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

• 해시충돌이 발생하여 기존 캐시 엔트리를 그대로 둔채 스칼라 서브쿼리만 한번 더 수행
• 해시 충돌이 발생한 입력값이 반복적으로 입력되면, 쿼리가 반복 수행

• _query_execution_cache_max_size : 캐시사이즈 조정 (8i, 9i 는 기본 256 개, 해시 충돌 없이 20번만 수행 )

ALTER SESSION SET "_query_execution_cache_max_size" = 2097152; 
 
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

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

• Deterministic 키워드 함께 함수 선언하면 , 서브쿼리 없어도 캐싱효과
• 함수 입력값과 출력값은 CGA(Call Global Area) 에 캐싱 : 데이터베이스 Call 내에서만 유효, Fetch Call 이 완료되면 해제
• 스칼라 서브쿼리에서 입력, 출력은 UGA 에 저장되므로 Fetch Call 상관없이 끝까지 유효

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);    /* 함수내 호출 횟수 확인 , client_info 변경 */
 
  for i in 1..p_input loop 
    rValue := rValue + i ; 
  end loop; 
  return rValue ; 
end;  

• 1,000,000 번 함수 호출 ( 실제 함수 호출은 50번 (한번의 Fetch Call 내에서 캐시 상태를 유지하며 처리)

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

• Deterministic 제거 (함수 호출 1,000,000 회)

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

• Deterministic 가 있더라고 함수안에 쿼리 문장이 포함하고 있다면 일관성 보장되지 않음( 같은 입력값에 언제라도 다른 출력 값을 낼수 있음)
• 그 함수가 일관성 있는 결과를 리턴함을 선언할 뿐
• 함수가 쿼리문을 포함할 때는 캐싱효과를 위해 함부로 Deterministic 으로 선언해선 안됨

h5.(6)복잡한 함수 로직을 풀어 SQL 구현