by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

PLSQL 함수 호출 부하 해소 방안




PL/SQL 함수 호출 부하 해소

  • 사용자정의 함수의 사용범위
    1) 소량의 데이터 조회시
    2) 대용량 데이터를 조회 할때는 부분범위 처리가 가능한 상황에서 제한적으로
    3) 조인 또는 스칼라 서브쿼리 형태로 변환하려는 노력이 필요
    4) 어쩔 수 없을 때는 사용하지만, 호출 횟수를 최소화 할수 있는방법을 강구
  • 함수 호출 부하 해소 방안
    1) 페이저 처리 또는 부분범위 처리활용
     
    SQL> select *
      2  from (
      3  select rownum no , a.*
      4  from (
      5  select lookup(1)
      6  from all_objects
      7  order by 1
      8  ) a
      9  where rownum <= 300
     10  ) where no between 21 and 300;
    
    280 개의 행이 선택되었습니다.
    경   과: 00:00:08.04
    
    select lookup(1),no
    from (
    select rownum no , a.*
    from (
    select rownum 
    from all_objects
    order by 1
    ) a 
    where rownum <= 300 
    ) where no between 21 and 300
    
    280 개의 행이 선택되었습니다.
    경   과: 00:00:02.17
    
    맨마지막에서 함수가 일어나게 처리함으로 속도개선향상
    
    
    2) Decode, Case 함수 문으로 변환
    
    -- 사용자정의함수
    두개의 테이블을 만들고
    create table t2 (no varchar2(2));
    create table t3 (no varchar2(2));
    
    t2 테이블에 1~5의 값이 들어간 데이터 백만건입력
    
    SQL> insert into t2
      2  select ceil(dbms_random.value(1,5)) no  from dual
      3  connect by level<= 1000000;
    
    1000000 개의 행이 만들어졌습니다.
    
    경   과: 00:00:07.78
    SQL> commit;
    
    커밋이 완료되었습니다.
    
    
    함수 생성
    CREATE OR REPLACE function trans
    (
      Str varchar2
    )
       RETURN VARCHAR2
    IS
       RET_VAL   VARCHAR2 (4000);
    BEGIN
      if str = '1' then 
        RET_VAL := '일';
      elsif str = '2' then
        RET_VAL := '이';
      elsif str = '3' then
        RET_VAL := '삼';
      elsif str = '4' then
        RET_VAL := '사';
      elsif str = '5' then
        RET_VAL := '오';
      end if;
      RETURN RET_VAL;   
    end;
    
    -사용자정의 insert
    
    SQL> insert into t3
      2  select trans(no) from t2;
    
    1000000 개의 행이 만들어졌습니다.
    경   과: 00:00:50.76
    
    - 내장함수사용 insert
    SQL> insert into t3
      2  select decode(no,'1','일','2','이','3','삼','4','사','5','오') no from t2;
    
    1000000 개의 행이 만들어졌습니다.
    경   과: 00:01:06.89
    
    -- decode사용
    SQL> delete from t3;
    
    2000000 행이 삭제되었습니다.
    
    경   과: 00:01:24.54
    SQL> insert into t3
      2  select decode(no,'1','일','2','이','3','삼','4','사','5','오') no from t2;
    
    1000000 개의 행이 만들어졌습니다.
    
    경   과: 00:00:10.31
    
    -- case사용
    SQL> delete from t3;
    
    1000000 행이 삭제되었습니다.
    
    경   과: 00:00:29.32
    SQL> insert into t3
      2  select case when no = '1' then '일'
      3              when no = '2' then '이'
      4              when no = '3' then '삼'
      5              when no = '4' then '사'
      6              when no = '5' then '오' end no from t2;
    
    1000000 개의 행이 만들어졌습니다.
    
    경   과: 00:00:02.96
    
    -- 테이블조인
    SQL> create table t_code(no,korea_no)
      2  as
      3  select'1','일' from dual union all
      4  select'2','이' from dual union all
      5  select'3','삼' from dual union all
      6  select'4','사' from dual union all
      7  select'5','오' from dual;
    
    SQL> alter table t_code add constraint t_code_pk primary key(no);
    
    테이블이 변경되었습니다.
    
    SQL> delete from t3;
    
    1000000 행이 삭제되었습니다.
    
    경   과: 00:00:24.07
    
    SQL> insert into t3
      2  select korea_no
      3  from t2 a , t_code b
      4  where a.no = b.no;
    
    1000000 개의 행이 만들어졌습니다.
    
    경   과: 00:00:01.85
    
    
    3) 뷰 머지 방지를 통한 함수 호출 최소화
    SQL> select sum(decode(trans(no),'일',1,0)) "1의총계",
      2         sum(decode(trans(no),'이',1,0)) "2의총계",
      3         sum(decode(trans(no),'삼',1,0)) "3의총계",
      4         sum(decode(trans(no),'사',1,0)) "4의총계",
      5         sum(decode(trans(no),'오',1,0)) "5의총계"
      6  from t2
      7  ;
    
       1의총계    2의총계    3의총계    4의총계    5의총계
    ---------- ---------- ---------- ---------- ----------
             0     249557     250515     249514     250414
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2     22.65      22.70          0       1571          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4     22.65      22.71          0       1572          0           1
    
    경   과: 00:00:23.45
    
    SQL> select sum(decode(no,'일',1,0)) "1의총계",
      2         sum(decode(no,'이',1,0)) "2의총계",
      3         sum(decode(no,'삼',1,0)) "3의총계",
      4         sum(decode(no,'사',1,0)) "4의총계",
      5         sum(decode(no,'오',1,0)) "5의총계"
      6  from (
      7  select trans(no) no from t2
      8  ) a;
    
       1의총계    2의총계    3의총계    4의총계    5의총계
    ---------- ---------- ---------- ---------- ----------
             0     249557     250515     249514     250414
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2     26.64      26.83          0       1571          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4     26.64      26.83          0       1572          0           1
    
    
    경   과: 00:00:24.09
    
    -- NO_MERGE 흰트를 주다...
    SQL> select sum(decode(no,'일',1,0)) "1의총계",
      2         sum(decode(no,'이',1,0)) "2의총계",
      3         sum(decode(no,'삼',1,0)) "3의총계",
      4         sum(decode(no,'사',1,0)) "4의총계",
      5         sum(decode(no,'오',1,0)) "5의총계"
      6  from (
      7  select /*+ NO_MERGE */trans(no) no from t2
      8  ) a ;
    
       1의총계    2의총계    3의총계    4의총계    5의총계
    ---------- ---------- ---------- ---------- ----------
             0     249557     250515     249514     250414
    
    경   과: 00:00:06.10
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      5.46       5.48          0       1571          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      5.46       5.48          0       1572          0           1
    
    
    SQL> select sum(decode(no,'일',1,0)) "1의총계",
      2         sum(decode(no,'이',1,0)) "2의총계",
      3         sum(decode(no,'삼',1,0)) "3의총계",
      4         sum(decode(no,'사',1,0)) "4의총계",
      5         sum(decode(no,'오',1,0)) "5의총계"
      6  from (
      7  select rownum,trans(no) no from t2
      8  ) a ;
    
       1의총계    2의총계    3의총계    4의총계    5의총계
    ---------- ---------- ---------- ---------- ----------
             0     249557     250515     249514     250414
    
    
    경   과: 00:00:05.39
    
    SQL> select sum(decode(no,'일',1,0)) "1의총계",
      2         sum(decode(no,'이',1,0)) "2의총계",
      3         sum(decode(no,'삼',1,0)) "3의총계",
      4         sum(decode(no,'사',1,0)) "4의총계",
      5         sum(decode(no,'오',1,0)) "5의총계"
      6  from (
      7  select trans(no) no from t2
      8  where rownum > 0
      9  ) a ;
    
       1의총계    2의총계    3의총계    4의총계    5의총계
    ---------- ---------- ---------- ---------- ----------
             0     249557     250515     249514     250414
    
    경   과: 00:00:06.96
    
    
    4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
    SQL> select sum(decode(no,'일',1,0)) "1의총계",
      2         sum(decode(no,'이',1,0)) "2의총계",
      3         sum(decode(no,'삼',1,0)) "3의총계",
      4         sum(decode(no,'사',1,0)) "4의총계",
      5         sum(decode(no,'오',1,0)) "5의총계"
      6  from (
      7  select (select trans(no) from dual) no from t2
      8  ) a ;
    
       1의총계    2의총계    3의총계    4의총계    5의총계
    ---------- ---------- ---------- ---------- ----------
             0     249557     250515     249514     250414
    
    경   과: 00:00:00.89
    
    select sum(decode(no,'일',1,0)) "1의총계",
           sum(decode(no,'이',1,0)) "2의총계",
           sum(decode(no,'삼',1,0)) "3의총계",
           sum(decode(no,'사',1,0)) "4의총계",
           sum(decode(no,'오',1,0)) "5의총계"
    from (
    select (select trans(no) from dual) no from t2
    ) a
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.76       0.76          0       1571          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.76       0.76          0       1572          0           1
    
    
    -- _query_execution_cache_max_size 파라미터에 의해 캐시 사이즈를 증가후 테스트
    SQL> alter session set "_query_execution_cache_max_size" =2097152;
    
    세션이 변경되었습니다.
    
    
    SQL> select sum(decode(no,'일',1,0)) "1의총계",
      2         sum(decode(no,'이',1,0)) "2의총계",
      3         sum(decode(no,'삼',1,0)) "3의총계",
      4         sum(decode(no,'사',1,0)) "4의총계",
      5         sum(decode(no,'오',1,0)) "5의총계"
      6  from (
      7  select (select trans(no) from dual) no from t2
      8  ) a ;
    
       1의총계    2의총계    3의총계    4의총계    5의총계
    ---------- ---------- ---------- ---------- ----------
             0     249557     250515     249514     250414
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.82       0.82          0       1571          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.82       0.82          0       1571          0           1
    
    경   과: 00:00:00.78
    
    별로 변화가 없어서 줄이고 해보았다.
    
    SQL> alter session set "_query_execution_cache_max_size" =100;
    세션이 변경되었습니다.
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        4      2.00       1.98          0       3142          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        8      2.00       1.98          0       3142          0           2
    
    
    
    5) Deterministic 함수의 캐싱 효과 활용
    
    SQL>     create or replace function accum(p_input number) return number
      2  deterministic
      3  as
      4    rvalue number := 0;
      5    call_cnt number := 0;
      6    call_cnt2 number := 0;
      7
      8  begin
      9    dbms_application_info.read_client_info(call_cnt);
     10    if call_cnt is null then
     11      call_cnt := 0;
     12    end if;
     13    call_cnt := call_cnt+1;
     14    dbms_application_info.read_client_info(call_cnt2);
     15
     16    for i in 1..p_input loop
     17     rvalue := rValue + i;
     18    end loop;
     19    return rvalue;
     20
     21  end;
     22  /
    
    SQL> select sum(accum_num)
      2  from (
      3    select accum(mod(rownum,50)) accum_num
      4    from dual
      5    connect by level <= 1000000
      6  );
    
    SUM(ACCUM_NUM)
    --------------
         416500000
    
    경   과: 00:00:01.67
    
    SQL> create or replace function accum(p_input number) return numb
      2  deterministic
      3  as
      4    rvalue number := 0;
      5    call_cnt number := 0;
      6    call_cnt2 number := 0;
      7
      8  begin
      9    dbms_application_info.read_client_info(call_cnt);
     10    if call_cnt is null then
     11      call_cnt := 0;
     12    end if;
     13    --call_cnt := call_cnt+1;
     14    dbms_application_info.set_client_info(call_cnt+1);
     15
     16    for i in 1..p_input loop
     17     rvalue := rValue + i;
     18    end loop;
     19    return rvalue;
     20
     21  end;
     22  /
    
    함수가 생성되었습니다.
    
    경   과: 00:00:00.01
    SQL> select sum(accum_num)
      2  from (
      3    select accum(mod(rownum,50)) accum_num
      4    from dual
      5    connect by level <= 1000000
      6  );
    
    SUM(ACCUM_NUM)
    --------------
         416500000
    
    경   과: 00:00:01.89
    
    
    - 쿼리는 100000번 호출햇지만 하단의 쿼리로 호출횟수가 50번 일어난걸 확인할수있따.(client_info값)
    
    SQL> select sys_context('userenv', 'client_info') from dual
      2  ;
    
    SYS_CONTEXT('USERENV','CLIENT_INFO')
         
               50
    
    -- deterministic 제거후테스트
    SQL> create or replace function accum(p_input number) return number
      2  --deterministic
      3  as
      4    rvalue number := 0;
      5    call_cnt number := 0;
      6    call_cnt2 number := 0;
      7
      8  begin
      9    dbms_application_info.read_client_info(call_cnt);
     10    if call_cnt is null then
     11      call_cnt := 0;
     12    end if;
     13    --call_cnt := call_cnt+1;
     14    dbms_application_info.set_client_info(call_cnt+1);
     15
     16    for i in 1..p_input loop
     17     rvalue := rValue + i;
     18    end loop;
     19    return rvalue;
     20
     21  end;
     22  /
    
    함수가 생성되었습니다.
    
    경   과: 00:00:00.06
    SQL> exec dbms_application_info.set_client_info(null);
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    경   과: 00:00:00.00
    SQL> select sum(accum_num)
      2  from (
      3    select accum(mod(rownum,50)) accum_num
      4    from dual
      5    connect by level <= 1000000
      6  );
    
    SUM(ACCUM_NUM)
    --------------
         416500000
    
    경   과: 00:00:10.07
    
    
    함수를 1000000 호출한걸 확인할수있다.
    SQL> select client_info
      2  from v$session
      3  where sid= sys_context('userenv','sid');
    
    CLIENT_INFO
    ----------------------------------------------------------------
    1000000
    
    deterministic 요함수를 사용을 하므로써, 성능은 조아지지만 일괄성을 보장해주지않는다.
    
    
    6) 복잡한 함수 로직을 풀어 SQL로 구현
    사용자(개발자) 입장에서는 난감한 경우다.
    Function을 사용하여 좀더 가독성 및 객체지향? , 쉬운 유지보수가 젤 어떻게보면 맞을것이다.
    때와 상황에 맞쳐서 사용하는게 좋을듯하다.
    
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.