오라클 성능 고도화 원리와 해법 I (2012년)
PL/SQL 함수 호출 부하 해소 방안 0 0 56,916

by 구루비스터디 PLSQL 함수 [2018.03.20]


  1. PL/SQL 함수 호출 부하 해소
    1. 사용자정의 함수의 사용범위
    2. 함수 호출 부하 해소 방안
      1. 1) 페이저 처리 또는 부분범위 처리활용
      2. 2) Decode, Case 함수 문으로 변환
      3. 3) 뷰 머지 방지를 통한 함수 호출 최소화
      4. 4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
      5. 5) Deterministic 함수의 캐싱 효과 활용
      6. 6) 복잡한 함수 로직을 풀어 SQL로 구현


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을 사용하여 좀더 가독성 및 객체지향? , 쉬운 유지보수가 젤 어떻게보면 맞을것이다.
  • 때와 상황에 맞쳐서 사용하는게 좋을듯하다.
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3115

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입