본문 바로가기
엑셈 기업문화/엑셈 사람들

[이은경]Function을 사용할 때 이것을 꼭 고려하세요!

by EXEM 2008. 11. 7.


프로그램을 개발할 때, 다음과 같이 Function을 만들어서 많이 사용한다. 이렇게 Function을 만들어서 사용하면 개발의 편의성을 얻을 수 있고 프로그램 해석이 쉽다는 장점을 가지고 있다.

Function get_dept – 부서명, 위치, 부서번호를 가져오는 함수

FUNCTION  get_dept
           ( dept_kind  comcod.code_kind%TYPE , dept_gubun varchar2 )
return varchar2 as

 v_code_dname    comcod.code_kname%TYPE;
 v_code_loc        comcod.code_skname%TYPE;
 v_code_deptno    comcod.code_ename%TYPE;

begin ------------------------------------------------SQL문(1)
    SELECT  dname , loc , deptno
    INTO    v_code_dname, v_code_loc, v_code_deptno
    FROM   dept
    WHERE  deptno=dept_kind;

 --3개의 값을 가져 온후 구분 코드에 따라 1개의 값만 return함
   if dept_gubun = '1' then
       return  v_code_dname;
   elsif dept_gubun = '2' then
       return  v_code_loc;
   elsif dept_gubun = '3' then
       return  v_code_deptno;
   else
       return ' ';
   end if;    
exception
    when    others  then
        v_code_dname :=  ' ';
        return  v_code_dname ;
end ;



그런데, Function을 사용할 때 우리가 미처 생각하지 못하고 쉽게 저지를 수 있는 실수가 있다.
바로 다음과 같이 SELECT 절에 사용하는 경우, 경합이 발생할 수 있음을 생각하지 못하는 것이다.

-------------------------------------------------------SQL문(2)
SELECT emp.empno            ,
 emp.ename             ,
emp.hiredate           ,
        emp.comm              ,
        emp.sal                 ,
       emp.depno ,
get_dept(&DEPT , ‘1’)
FROM  emp, salgrade
WHERE  ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT )

SQL문(2)와 같이 SELECT절에 Function을 사용하는 SQL문을 실행하면, Library Cache 래치 경합이자주 목격되고, SQL문의 실행이 지연된다. 많은 파싱에 의해서 Execute Count가 증가하기 때문이다.

SQL문(2)를 실행해서 emp테이블과 salgrade테이블에 WHERE절 조건에 대한 결과 건수가 다음과같이 많은 경우,

[ SQL문(2)의 실행 시 Trace결과]

call     count       cpu    elapsed       disk      query    current       rows        mis   Wait Ela
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0          0          0       0.00
Exec         1      0.01       0.00          0          0          0          0          0       0.00
Fetch      261      2.01       2.03          0      43923          0       6500          0       0.81
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total      263      2.02       2.04          0      43923          0       6500          0       0.81

결과 Rows가 6500이다.

SELECT절에 있는 Function인 get_dept를 구성하는 SQL문(1)은 그 결과 건수마다 실행된다.

[ SQL문(2)의 실행 시 SQL문(1)의 Trace결과]

call     count       cpu    elapsed       disk      query    current       rows        mis   Wait Ela
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0          0          0       0.00
Exec      6500      1.15       1.17          0          0          0          0          0       0.00
Fetch     6500      0.12       0.13          0      19326          0       6442          0       0.00
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total    13001      1.27       1.30          0      19326          0       6442          0       0.00

SQL문(2)의 결과건수인 6500만큼 Exec에 나타난다.

사용자는 SQL문(2)를 한번만 수행했지만, 결과 건수에 따라 Function이 실행되고, SQL문(1)이 호출되어 Execute Count가 증가하게 되는 것이다.

결국, 결과건수만큼 반복적으로 수행되는 SQL문(1)로 인하여 그만큼 Library Cache를 탐색하는 횟수가 늘어나고 Library Cache 래치를 보유하는 시간과 횟수가 늘어나 Library Cache 래치 경합이 발생하게 된다.

이를 해결하기 위해서는 SQL문(2)를 다음과 같이 조금 수정할 필요가 있다. Function의 내용을 스칼라 서브쿼리나 Outer 조인쿼리로 변경하여 SQL문(1)의 반복 수행을 줄이는 것이다.

--------------------------------------------------------------SQL문(3) 스칼라 서브쿼리
SELECT  emp.empno            ,
        emp.ename             ,
emp.hiredate           ,
        emp.comm              ,
        emp.sal                 ,
        emp.deptno ,
--      get_dept(&DEPT , '1')
        (SELECT decode(&dept_gubun,'1',dname , '2',loc,'3',deptno)
         FROM dept
WHERE deptno=&DEPT)
FROM emp, salgrade
WHERE  ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT );

[ SQL문(3)의 실행 시 Trace결과]

call     count       cpu    elapsed       disk      query    current       rows        mis   Wait Ela
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.03       0.03          0          0          0          0          1       0.00
Exec         1      0.00       0.00          0          0          0          0          0       0.00
Fetch      261      0.19       0.20          0      25239          0       6500          0       0.47
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total      263      0.22       0.23          0      25239          0       6500          1       0.47

1회만 수행되고, 결과건수 6500건 동일
------------------------------------------ SQL문(4) Outer 조인쿼리
SELECT emp.empno            ,
       emp.ename             ,
emp.hiredate            ,
       emp.comm              ,
       emp.sal                 ,
       emp.deptno ,
--     get_dept(&DEPT , '1')
       decode(&dept_gubun,'1',c.dname,'2',c.loc,'3',c.deptno)
FROM emp, salgrade,
      (SELECT * FROM dept ) c
WHERE ( salgrade.grade = 2 )
AND   ( emp.sal between salgrade.losal and salgrade.hisal)
AND   ( emp.deptno = &DEPT )
AND   (c.deptno(+)=&DEPT);

[ SQL문(4)의 실행 시 Trace결과]

call     count       cpu    elapsed       disk      query    current       rows        mis   Wait Ela
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.04       0.03          0          0          0          0          1       0.00
Exec         1      0.00       0.00          0          0          0          0          0       0.00
Fetch      261      0.22       0.18          0      24757          0       6500          0       0.46
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total      263      0.26       0.22          0      24757          0       6500          1       0.46

1회만 수행되고, 결과건수 6500건 동일

SQL문(3)과 SQL(4)의 경우는 WHERE절의 결과 건수에 상관없이 사용자가 1회 수행하면, 1회만 수행되어 파싱횟수(수행횟수)가 증가하지 않는다. 따라서 Library Cache 래치 경합을 피할 수 있다.

그런데, SQL문(3)의 스칼라 서브쿼리와 SQL(4)의 Outer 조인쿼리 중 어떤 형태로 변경하는 것이 좋을까? 일반적으로 대량의 데이터를 처리 할 경우에는 스칼라 서브쿼리는 Nested loop방식으로 처리되어 Cache Buffer Chains 래치의 발생 우려가 있다. 그래서 Outer 조인쿼리방식을 사용하는 것이 유리하다. 하지만, OLTP성 작업으로 건수가 적은 경우는 스칼라 서브쿼리 방식을 사용해도 별다른 차이가 없다.

지금까지 사용자가 만든 Function을 SQL문에서 사용할 때 고려해야 할 내용을 소개하였다. 이는조금만 신경쓰면 해결할 수 있는 팁이라고 생각하며, 많은 도움이 되었으면 좋겠다.

댓글