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

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

# 함수 사용 적정시기
- 소량의 데이터 조회시 사용
- 대량의 데이서 조회시 부분범위 처리가 가능한 상황에서 제한적으로 사용
- 조인 또는 스칼라 서브쿼리 형태로 변환
- 최후의 방법으로 함수를 쓰되 호출횟수를 최소화 할수 있는 방법 강구

# 함수 호출 부하방안
- 페이지처리(소량의 데이터), 부분범위 처리 활용
- DECODE함수 Case문으로 변환
- 뷰 머지 방지를 통한 함수 호출 최소화
- 스칼라 서브쿼리 캐싱효과를 이용한 함수 호출 최소화
- Deterministic 함수의 캐싱효과 활용
- 복잡한 함수로직을 풀어 SQL로 구현

1) 페이지처리(소량의 데이터), 부분범위 처리 활용
필요한 데이터를 다 만든뒤에 함수를 마지막에 사용.
만약 부분범위처리가 불가능해 전제범위 처리를 해야하는 상황이라면, 전혀 소용이 없는 기법이다. 
이때는 함수사용시 dual 테이블을 이용해 스칼라서브쿼리로 변환하여.
캐싱효과를 이용해, 성능을 높일수 있다.

# 원본 쿼리
---------------------------------------------------------------------------------------------------------------------------------
select *
from ( 
   select rownum no, a.*
   from ( 
      select manb_nm(매도회원번호) 매도회원명
              ,manb_nm(매수회원변호) 매수회원명
              ,code_nm('446',매도투자지구분코드) 매도투자지구분명
              ,code_nm('446',매수투자자구분코드) 매수투자지구분명
              ,code_nm('418',체결유형코드) 체결유형명
              ,매도계좌번호
              ,매수계좌번호
              ,체결시각
              ,체결수량
              ,체결가
              ,체결수량 * 체결가 체결급액 
      from 체결
      where 종목코드 = :종목코드
      and   체결일자 = :체결일자
      and   체결시각 between sysdate-10/24/60 and sysdate
      order by 체결시각 desc 
   ) a 
   where rownum <= 30
where no between 21 and 30
---------------------------------------------------------------------------------------------------------------------------------

# 개선된 쿼리
---------------------------------------------------------------------------------------------------------------------------------
select manb_nm(a.매도회원번호) 매도회원명,
        manb_nm(a.매수회원변호) 매수회원명,
        code_nm('446',a.매도투자지구분코드) 매도투자지구분명,
        code_nm('446',a.매수투자자구분코드) 매수투자지구분명,
        code_nm('418',a.체결유형코드) 체결유형명,
        a.체결유형명,
        a.매도계좌번호,
        a.매수계좌번호,
        a.체결시각,
        a.체결수량,
        a.체결가,
        a.체결급액,
        a.체결수량 * a.체결가 체결급액 
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
---------------------------------------------------------------------------------------------------------------------------------

(2) DECODE함수 Case문으로 변환
# 참고 Data 
- 시장코드 : ST, KQ, SD, GD
- 증권그룹코드 : 'SS','EF','EW','DR','SW','RT','BC','MF','FU','OP'

# 원본 쿼리
---------------------------------------------------------------------------------------------------------------------------------
SET TIMING ON 
SELECT SF_상품분류 ( 시장코드 , 증권그룹코드 ) 상품분류 
      ,COUNT(*) 체결건수
     ,SUM(체결수량) 체결수량
     ,SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY SF_상품분류(시장코드,증권그룹코드)
ORDER BY 1
---------------------------------------------------------------------------------------------------------------------------------

# 쿼리안에서 쓰이는 함수
---------------------------------------------------------------------------------------------------------------------------------
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
         L_분류 := '파생';
      ELSE
         L_분류 := '주식외 현물';
      END IF;
   ELSE
      L_분류 := '파생';
   END IF;
   -- SELECT 순서 ||'· '|| L_분류 INTO L_분류 
   -- FROM 분류순서 
   -- WHERE 분류명 = L_분류;
   RETURN L_분류 ;
END;
/
---------------------------------------------------------------------------------------------------------------------------------

# 참고 Data 
- 시장코드 set1 : ST, KQ
- 시장코드 set2 : SD, GD
- 증권그룹코드 set1 : 'SS','EF','EW','DR','SW','RT','BC','MF'
- 증권그룹코드 set2 : 'FU','OP'

# 쿼리안에서 쓰이는 SQL 개선 - CASE
---------------------------------------------------------------------------------------------------------------------------------
SET T1M1NG ON 
SELECT CASE WHEN 시장코드 IN ( 'ST', 'KQ' ) AND 증권그룹코드 = ’ SS ’ THEN '주식 현물'
          WHEN 시장코드 IN ( 'ST', 'KQ' ) AND 증권그룹코드 NOT IN ( 'EF','EW','SS' ) 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 증권그룹코드 IN ( 'EF','EW' ) THEN '파생'
       WHEN 시장코드 IN ( 'ST', 'KQ' ) AND 증권그룹코드 NOT IN ( 'EF','EW','SS' ) THEN '주식외 현물'
       ELSE '파생' END
ORDER BY 1
---------------------------------------------------------------------------------------------------------------------------------

# 참고 Data 
- 시장코드 : ST, KQ, SD, GD
- 증권그룹코드 : 'SS','EF','EW','DR','SW','RT','BC','MF','FU','OP'

# 쿼리안에서 쓰이는 SQL 개선 - DECODE
---------------------------------------------------------------------------------------------------------------------------------
SET TIMING ON 
SELECT 
      DECODE( 시장코드||증권그룹코드,'STSS','주식 현물'
                      ,'KQSS','주식 현물'
                      ,'STEF','파생'
                      ,'KQEF','파생'
                      ,'STEW','파생'
                      ,'KQEW','파생'
                      ,'SDFU','파생'
                      ,'GDOP','파생'
                      ,'SDFU','파생'
                      ,'GDOP','파생'
                      ,'주식외 현물'
      ) 상품분류,
      ,COUNT(*) 체결건수
      ,SUM(체결수량) 체결수량
      ,SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY DECODE( 시장코드||증권그룹코드,'STSS','주식 현물'
                      ,'KQSS','주식 현물'
                      ,'STEF','파생'
                      ,'KQEF','파생'
                      ,'STEW','파생'
                      ,'KQEW','파생'
                      ,'SDFU','파생'
                      ,'GDOP','파생'
                      ,'SDFU','파생'
                      ,'GDOP','파생'
                      ,'주식외 현물'
      )
ORDER BY 1;
---------------------------------------------------------------------------------------------------------------------------------

(3) 뷰 머지 방지를 통한 함수 호출 최소화 
# 함수를 고치지 않고, 함수호출횟수를 줄이는 기법

[1] 기존 쿼리형태
---------------------------------------------------------------------------------------------------------------------------------
select FUNC(),FUNC(),FUNC(), t1.*
from t1
---------------------------------------------------------------------------------------------------------------------------------

[2] 개선된 쿼리형태1
---------------------------------------------------------------------------------------------------------------------------------
select t1.func, t1.func, t1.func, t1.*
from (select /*+ NO_MERGE */ FUNC() func, t1.* from t1 ) t1
---------------------------------------------------------------------------------------------------------------------------------

[3] 개선된 쿼리형태2
---------------------------------------------------------------------------------------------------------------------------------
select t1.func, t1.func, t1.func, t1.*
from (select rownum, FUNC() func, t1.* from t1 ) t1
---------------------------------------------------------------------------------------------------------------------------------

[4] 개선된 쿼리형태3
---------------------------------------------------------------------------------------------------------------------------------
select t1.func, t1.func, t1.func, t1.*
from (select FUNC() func, t1.* from t1 where rownum > 0) t1
---------------------------------------------------------------------------------------------------------------------------------

반드시 NO_MERGE 힌트가 들어가거나, rownum 컬럼이 인라인 뷰안에 있어야한다. 그렇지 않다면 
실행계획 수립시, Query Transformation 단계에서 [1] 쿼리처럼 변형될 가능성이 있다.

(4) 스칼라 서브쿼리 캐싱효과를 이용한 함수 호출 최소화
 # 스칼라서브쿼리 사용시 입력값과 출력값 쌍을 내부 캐시(UGA)에 저장한다.
    스칼라서브쿼리가 수행될 때마다 입력값을 캐시에서 찾아보고 거기 있으면 저장된 출력값을 리턴하고
    없으면 쿼리를수행한 후 입력값과 출력값을 캐시에 저장해두는 원리다.
    >> 이 기능으로 함수 호출 횟수를 줄이는데 시용할 수 있다.
    >> 함수를 Dual 테이블을 이용해 스칼라 서브쿼리로 한번 감싸는 방법
    >> 함수 입력 값의 종류가 많다면 HASH충돌 이라는것이 일어나기 때문에 CPU사용율이 높아져 비효율적이다. 
        입력값 : 메인쿼리의 컬럼값
        출력값 : 메인쿼리의 컬럼값에 대응되어 일치되는 스칼라서브쿼리의 컬럼값
---------------------------------------------------------------------------------------------------------------------------------
--메인쿼리
select ( 
   --스칼라서브쿼리 : 메인쿼리의 select절에 포함된 서브쿼리   
   select d.dname           ---> 출력 값 : d.dname 
   from dept d 
   where d.deptno = e.empno ---> 입력 값 : e.empno
)
from emp e
---------------------------------------------------------------------------------------------------------------------------------

(3)-[2] 개선된 쿼리형태
---------------------------------------------------------------------------------------------------------------------------------
select t1.func, t1.func, t1.func, t1.*
from (select /*+ NO_MERGE */ FUNC() func, t1.* from t1 ) t1
---------------------------------------------------------------------------------------------------------------------------------

(3)-[2] 스칼라서브쿼리로 개선된 쿼리형태1
---------------------------------------------------------------------------------------------------------------------------------
select t1.func, t1.func, t1.func, t1.*
from (select /*+ NO_MERGE */ (select FUNC() from dual) func, t1.* from t1 ) t1
---------------------------------------------------------------------------------------------------------------------------------

(5) Deterministic 함수의 캐싱 효과 활용
# 10gR2 부터 함수를 선언할 때 Deterministic 키워드를 넣어 주면
  스칼라 서브쿼리를 덧입히지 않아도 캐싱 효과가 나타난다.  

# 함수의 입력값과 출력값은 CGA(Call Global Area)에 캐싱된다.
  CGA에 힐딩된 값은 데이터베이스 Call 내에서만 유효히므로 Fetch Call 이 완료되면 그 값들은 모두 해제된다.

# 함수내부에 SQL이 들어가 있다면, 일관성을 유지하지 못하므로, 함부로 DETERMINISTIC를 남용해서는 안된다.

# 선언 방법
---------------------------------------------------------------------------------------------------------------------------------
create or replace function ACCUM (p input number) return number
DETERMINISTIC
as
   ...
   ...
begin 
   ...
end;
/
---------------------------------------------------------------------------------------------------------------------------------

(6) 복잡한 함수 로직을 풀어 SQL로 구현
ex) 수정주가
어떤 주식이 주식시장 시간이외에 주말이나, 휴일에 아래와 같은 이벤트가 발생이 되면, 주가가 변경이 된다.
- 유,무상 증자에 의한 권리락, 
- 배당에 의한 배당락, 
- 액면변경

따라서, 주식시장에서 마지막으로 기록되었던 주가와 새로 주식시장이 개장하는 날에 시작하는 주가사이에 
주식시장에서 발생하지 않은 차이값 생겨, 주가의 연속성이 없어진다.

이러한 이벤트가 발생한 다음 주식시장에서 발생된 마지막 시장의종가를
그대로 당일 주식시장의 시작가로 시용하면, 거래가 시작되자마자 주식이 바로 상한가 또는 하한가를 기록해
주가등락 정보를 왜곡하게 되므로 당일 시작기준가를 조정하는 시장조치를 실시한다.
따라서 현재와 과거의 주가를 비교할 때 수정주가라는 값을 사용해 시작가를 수정해준다.
이러한 수정주가는 아래와 같이 구한다.

1) 아래의 테이블이 있다.
[1] 기준가변경이력 : 이벤트에 의해 변경된 주식의 가격과 변동비율이 기록된 테이블
[2] 일별종목주가 : 주식시장에서의 주식의 마지막 종가와, 종가가 기록된 테이블
      

2) 기준가변경이 테이블을 "종목코드" 별 오름차순 정렬, 
   "이벤트가 발생하여 주가가 변경된 날짜"를 기준으로 내림차순 정렬한다.


3) 정렬된 데이터에서 수정비율을 테이블을 "종목코드" 별로 누적곱을 구한다.


4) 정렬된 데이터에서 변경된일자를 기준으로 "이벤트가 발생하지 않은 기간"의 시작일과, 종료일을 구한다.


5) 기준가변경이 테이블의 "이벤트가 발생하지 않은 기간"의 시작일과, 종료일을 기준으로
   일별종목주가 테이블에서 종목코드가 같은 데이터끼리 종가가 기록된 날짜가, 시작일과 종료일 사이에 해당하면
   데이터를 매치시킨다 ( 조인 )
                                      

6) 매치된 데이터릐 누적수정비율과, 종가를 곱하여 수정주가를 구해준다.
   만약 매치되는 데이터가 없다면 매치되지 않는 데이터의 누적수정비율은 1로 치환한다.

# 기존에는 위의 내용을 함수로 작성하여 아래와 같이 사용해왔다.
---------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION 수정주가
(
P_종목코드 in varchar2
,P_조회일자 in varchar2
,P_거래일자 in varchar2
,P_수정전주가 in number
) return number
IS
V_수정주가 number;
V_수정비율 number;
CURSOR C IS
SELECT 수정비율
FROM 기준가변경이력
WHERE 종목코드 = P_종목코드
AND 변경일자 <= P_조회일자
AND 변경일자 > P_거래일자
ORDER BY 변경일자;
BEGIN
V_수정주가 := P_수정전주가;
OPEN C;
LOOP
FETCH C INTO V_수정비율 ;
EX1T WHEN C%NOTFOUND;
V_수정주가 := V_수정주가 * V_수정비율 ;
END LOOP;
RETURN V_수정주가;
CLOSE C;
END;
/

SELECT 거래일자, 종가, 수정주가( 종목코드, '20181231', 거래일자, 종가 )
FROM 일별종목주가
WHERE 거래일자 BETWEEN '20180101' AND '20181231'
AND 종목코드 = :종목코드
ORDER BY 거래일자;
---------------------------------------------------------------------------------------------------------------------------------

# 사용자 정의 함수를 사용함으로써 Database Call이 증가하면 부하가 발생함으로 
  위의 내용을 아래와 같이 쿼리로 변환하여 Database Call을 감소시킬수 있다.
---------------------------------------------------------------------------------------------------------------------------------
SELECT a.거래일자, a.종가, 종가*(NVL(b.누적수정비율,1)) 수정주가
FROM 일별종목주가 a, (
SELECT 종목코드
 , NVL(LAG(변경일자) OVER(ORDER BY 변경일자),'000000')시작일
 , TOCHAR(TO_DATE(변경일자,'yyyymmdd')-1,'yyyymmdd') 종료일
 , EMP(SUM(LN(수정))) OVER(ORDER BY 변경일자 DESC) 누적수정비율
FROM 기준가변경이력
WHERE 종목코드 = :종목코드
AND 변경일자 BETWEEN '20180101' AND '20181231'
) b
WHERE a.종목코드 = :종목코드
AND a.거래일자 BETWEEN '20180101' AND '20181231'
AND a.거래일자 BETWEEN b.시작일(+) AND b.종료일(+)
AND b.종목코드(+) = a.종목코드
ORDER BY a.거래일자;
---------------------------------------------------------------------------------------------------------------------------------

# 위의 쿼리를 해석했을 때 의미는 아래와 같다.
1) 아래의 테이블이 있다.
[1] 기준가변경이력 : 이벤트에 의해 변경된 주식의 가격과 변동비율이 기록된 테이블
[2] 일별종목주가 : 주식시장에서의 주식의 마지막 종가와, 종가가 기록된 테이블

# 서브쿼리 b
#######################################################################################
2) 기준가변경이 테이블을 "종목코드" 별로 데이터를 구분정리하고,
WHERE 종목코드 = :종목코드

3) 구분정리된 데이터를 "이벤트가 발생하여 주가가 변경된 날짜"를 기준으로 내림차순 정렬한다.
   (최신부터 과거순으로 정렬)
OVER(ORDER BY 변경일자 DESC)

4) 정렬된 데이터에서 수정비율을 테이블을 "종목코드" 별로 누적곱을 구한다.
EMP(SUM(LN(수정)))

# 누적곱 구하는법 : 자연로그 사용
---------------------------------------------------------------------------------------------------------------------------------
# 자연로그 ln의 기본 공식
- log 10 = 1
- log^e 의 약자 = ln 
- log^e e = 1
- ln e = 1

# 10 * 10 곱 구하기 
log 10 + log 10 = 1 + 1 = 2 = log 100 
>> log 10 + log 10 = log 100 
>> log^log 100 = 100
>> 100 = 10 * 10

# 10 * 100 곱 구하기 
log 10 + log 100 = 1 + 2 = 3 = log 1000
>> log 10 + log 100 = log 1000
>> log^log 1000 = 1000
>> 1000 = 10 * 100

# log를 사용해 a * b 곱 구하기 
log a + log b = log c
>> log^log c = c
>> c = a * b

# log가 아닌 자연로그 ln을 사용해 a * b 곱 구하기 
ln a + ln b = ln c
>> ln^ln c = EXP(ln c) = c
>> c = a * b

# a와 b의 곱을 구하려면
- a와 b에 자연로그를 씌우고 더한다.
>> ln a + ln b = ln c

- 더한값을 자연지수연산을 사용해 자연로그를 제거해준다.
>> ln^ln c = EXP(ln c) = c = a * b
---------------------------------------------------------------------------------------------------------------------------------

5. 정렬된 데이터에서 변경된일자를 기준으로 "이벤트가 발생하지 않은 기간"의 시작일과, 종료일을 구한다.
, NVL(LAG(변경일자) OVER(ORDER BY 변경일자),'000000')시작일
, TO_CHAR(TO_DATE(변경일자,'yyyymmdd')-1,'yyyymmdd') 종료일
#######################################################################################

메인쿼리에서 일별종목주가 a 테이블과의 조인
#######################################################################################
6. 기준가변경이 테이블의 "이벤트가 발생하지 않은 기간"의 시작일과, 종료일을 기준으로
   일별종목주가 테이블에서 종목코드가 같은 데이터끼리 종가가 기록된 날짜가, 시작일과 종료일 사이에 해당하면
   데이터를 매치시킨다 ( 조인 )

- AND 거래일자 BETWEEN b.시작일(+) AND b.종료일(+)
- AND b.목종드(+) = a.종목코드 

7. 매치된 데이터릐 누적수정비율과, 종가를 곱하여 수정주가를 구해준다.
   만약 매치되는 데이터가 없다면 매치되지 않는 데이터의 누적수정비율은 1로 치환한다.
- a.종가 * NVL(b.누적수정비율,1)
#######################################################################################