07 PL/SQL 함수의 특징과 성능부하

07 PL/SQL 함수의 특징과 성능부하

1) PL/SQL 함수의 특징
 - PL/SQL도 JAVA처럼 인터프리터 언어이기 때문에 Native 코드로 완전 컴파일된 내장함수에 비해 느리다.
 - 작성한 PL/SQL 함수 실행시 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.
   CPU가 여러개 작업을 수행할때 하나의 작업을 마치고 다음작업을 수행하는것이 아니라 여러개의 작업을 동시에
   돌아가며 수행하고, 다른 작업을 수행할 때 기존 CPU가 처리하던 프로세스의 수행내용을 백업하기도하고,
   다시 CPU가 다른 프로세스를 처리할 때 해당프로세스에서 백업했던 수행내용을 불러오는 작업을 수행하는데 이를 
   컨텍스트 스위칭이라 한다.   

2) Recursive Call을 포함하지 않는 함수의 성능부하
 - PL/SQL 사용자정의 함수를 넣어 놓은 SQL문장을 실행시키면,
  >> SQL엔진과 PL/SQL엔진간, 컨텍스트 스위칭이 발생한다.
  >> 컨텍스트 스위칭이 성능에 부하를 준다.  

3) Recursive Call을 포함하는 함수의 성능부하
  >> 대량의 데이터를 조회하며, 레코드 단위로 사용자정의 함수(PL/SQL)을 사용하면,
       각 레코드 수만큼 함수가 실행되 Recursive Call인 Execute Call과 Fetch Call을 발생시키고,
       성능이 극도로 나빠진다.  
  >> 따라서 사용자 정의 함수는 소량의 데이터 조회시만 사용하거나, 대량조회시에는 부분범위 처리가 가능한
       상황에서 제한적으로 사용해야한다.
       또는 가급적, 조인 또는 스칼라 서브쿼리 형태로 변환하려 해야한다.
  >> 아래 통계를 보았을 때 select 1 into n from dual; 쿼리로 인한 1000000개의 Fetch Call과 Execute Call이
       발생하였다. 간단한 쿼리임에도 불구하고 DB Call수가 엄청나게 늘어났다.

---------------------------------------------------------------------------------------------------------------------------------
create or replace function date_to_char( p_dt date ) return varchar2
as 
   l_empno number;
begin
   select 1 into n from dual;
   return to char ( p_dt , 'yyyy/mm/dd hh24:mi:ss' ) ;
end;
/

insert into t 
select rownum no, date_to_char (sysdate + rownum) char_time
from dual
connect by level <= 1000000

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

(4) 함수를 필터조건으로 사용할 때 주의사항
 >> 조건절과 인텍스 상황에 따라 함수 호출 횟수가 달라지기 때문에
      함수를 where절 에서 필터 조건으로 사용할 때도 주의가 필요.
 >> 인덱스 엑세스 조건이면 where절에 있는 함수가 1번 수행
      인덱스 필터조건이거나, 테이블 필터조건이면, 함수가 매번 수행된다.

(5) 함수와 읽기 일관성
 >> 테이블을 읽는 함수를 사용해 테이블을 읽는도중에 다른 세션에서 해당 테이블의 값을 바꿔버리면
    테이블을 읽는도중 바뀐값이 적용되어 보이게 된다. 테이블을 볼때 읽기 시작한 시점의 값을 보여줘야하는데
    읽는 도중에 바뀌어버린 값을 보여줌으로써 읽기 일관성이 보장되지 않는다.
    프로시져, 패키지, 트리거를 사용할때도 공통적으로 나타나는 현상이다.
 >> 아래 쿼리를 수행할 때
      여러개의 지수업종에 동일 종목코드가 존재하는 데이터구조를 갖고 있다.
      A 지수업종의 C종목에 대한 현재가를 적용해 하나의 로우를 출력했는데
      B 지수업종의 C종목에 대한 현재가를 적용해 하나의 로우를 출력할때
      C종목의 현재가가 특정세션에 의해 바뀌었다면, 위 쿼리를 시작할 시점에 C종목의 현재가가
      일정하지 않고 시간에 따라 다른값이 출력될수 있다. >> 일관성을 만족하지 못하는 경우이다.
      따라서 일관성을 만족할수 있도록 조인문이나, 스칼라서브쿼리를 이용해 쿼리를 작성하는것이 좋다.

---------------------------------------------------------------------------------------------------------------------------------
create or replace SF_현재가 ( p_종목코드 varchar2 ) return number 
as 
   rValue number;
begin 
   select 현재가 into rValue from 종목별시세 where 종목묘드 = p_종목묘드;
   return rValue ;
end;
/

create or replace SF_시가총액 ( p_좋목코드 varchar2 ) return number
as 
   rValue number ;
begin 
   select 현재가 * 발행주식수 into rValue from 종목별시세 where 종목코드 = p 종목묘드;
   return rValue;
end ;
/

select a . 지수업종코드 
      ,min(a.지수업종명) 지수업 종명 
      ,avg(SF_현재가(b.종목코드)) 평균주식가격 
      ,sum(SF_시가총액(b .종목코드)) 시가총액
from 지수업종 a , 지수업종구성 종목 b 
where a.지수업종유형묘드 = '001' 
and b.지수업종코드 = a.지수업종코드
group by a.지수업종코드;
---------------------------------------------------------------------------------------------------------------------------------

# 일반 조인문으로 개선한 쿼리
---------------------------------------------------------------------------------------------------------------------------------
select a.지수엽종코드
        ,min(a.지수업종멍) 지수업종명
        ,avg (c.현재가) 평균주식 가격
        , sum (c.현재가 * c.발행주식수) 시가총액
from 지수업종 a , 지수업종구성종목 b , 종목별시세 C
where a.지수업종유형묘드 = '001' 
and b.지수업종코드 = a.지수업종코드
and c.종목코드 = b.종목코드
group by a.지수업종코드 ;
---------------------------------------------------------------------------------------------------------------------------------

# 스칼라 서브쿼리문으로 개선한 쿼리
---------------------------------------------------------------------------------------------------------------------------------
select a.지수업종코드
        , min( a.지수업종명) 지수업종명
        , avg (se1ect 현재가 from 종목별시세 where 종목코드 = b.종목코드) 평균주식가격
        , sum (se1ect c.현재가 * c.발행주식수 from 종목별시세 where 종목코드 = b.종목코드) 시가총액
from 지수업종 a , 지수업종구성종목 b 
where a.지수업종유형코드 = '001'
and b.지수업종코드 = a.지수업종코드
group by a.지수업종코드;
---------------------------------------------------------------------------------------------------------------------------------

(6) 함수의 올바른 사용기준
 >> 쓰지 말라는 얘기가 아니고, 함수를 사용했을 때 성능이 느려지는 원리를 이해하고 활용해야한다.
 >> 채번함수 - 함수사용이 무조건 나쁜것이 아니라는 예시
      채번을 위해 PL/SQL 함수 (seq_nextval)을 사용하지 않고, select, insert, update를 사용하면
      User Call이 발생하여, Recursive Call보다 느리다. PL/SQL 함수 사용.
 >> Recursive Call에 대한 부하를 완전하게 없애는것 보다. 무분별한 사용을 하지 않고,
      조인이나 스칼라 서브쿼리로 바꿀수 있다면 바꾸는것이 좋다.
 >> 읽기 일관성 문제도 마찬가지다. 오라클 함수를 시용하지 않고 애플리케이선 단에서 구현하더라도 같은
      문제가 발생하므로 함수 사용에 따른 폐단으로 규정하는 것은 문제가 있다. 
      대부분 개발지들이 모르고 있는 사실이므로 주의를 당부한 것이고， 
      애플리케이션 단에서 구현할 때와 마찬가지로 데이터 일관성이 깨지지 않도록 설계하고 개발해야 한다
 >> Shared pool 크기 내에서 소화할수 있는 적정개수의 SQL과 PL/SQL 단위 프로그램을 유지할수 있도록
      노력해야한다. 연산위주의 작업은 어플리케이션 서버에서, SQL수행을 요구하는 작업은 오라클
      함수/프로시저를 이용하도록 설계해야한다.