프로그램을 개발할 때, 다음과 같이 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문에서 사용할 때 고려해야 할 내용을 소개하였다. 이는조금만 신경쓰면 해결할 수 있는 팁이라고 생각하며, 많은 도움이 되었으면 좋겠다.
'엑셈 기업문화 > 엑셈 사람들' 카테고리의 다른 글
| [김시연]프로젝트 5박 10일 (4) | 2008.11.18 |
|---|---|
| [오수영]덧셈뺄셈도 제일 처음엔 어려웠었다 (2) | 2008.11.14 |
| [박준연]“인덱스에 대한 구라” 누가 그랬을까~? (1) | 2008.10.31 |
| [원종철]TStringList를 사용할 때 당신이 알아야할 두가지 (0) | 2008.10.24 |
| [오경렬]진정한 지식에 대한 단상 (3) | 2008.10.10 |
댓글