PLSQL 함수의 특징과 성능 부하 (by jongmali) [2010.01.09]
07. PL/SQL 함수의 특징과 성능 부하
(1) PL/SQL 함수의 특징
- PL/SQL로 작성한 함수와 프로시저를 컴파일하면 JAVA 언어처럼 바이트코드가 생성.
- 이를 해석하고 실행할 수 있는 PL/SQL 엔진(가상머신)만 있다면 어디서든 실행될 수 있다.
- 바이트코드는 데이터 딕셔너리에 저장되었다가 런타임 시 해석된다.
- PL/SQL도 JAVA처럼 인터프리터 언어이기 때문에 Native 코드로 완전 컴파일된 내장(Built-In) 함수에 비해 많이 느리다.
- 이 문제를 극복하려고 오라클 9i부터 해당 플랫폼 Native 코드로 컴파일 할 수 있는 기능을 제공하기 시작했지만 사용상 복잡성 때문에 잘 사용되지 않고 있다.
SQL > show parameter plsql
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags string
*plsql_code_type string INTERPRETED*
plsql_compiler_flags string INTERPRETED, NON_DEBUG
- PL/SQL은 인터프리터 언어이므로 그것으로 작성한 함수 실행시 매번 SQL 실행엔진과 PL/SQL 가상멋신 사이에 컨텍스트 스위칭(Context Switching)이 일어난다.
(SQL에서 함수를 호출할 때마다 SQL 실행엔진이 사용하던 레지스터 정보들을 백업했다가 PL/SQL 엔진이 실행을 마치면 다시 복원하는 작업을 반복한다) - PL/SQL 함수와 프로시저를 잘게 모듈화, 공용화하는 것이 안 되는 이유가 여기에 있다.
- 원리를 잘 알고 사용하면 성능을 높일 수 있는 요소가 많지만, 잘못 사용하면 심각한 성능 부하를 일으킬 수 있다.
(2) Recursive Call를 포함하지 않는 함수의 성능 부하
- 날짜형 데이터를 공용 문자열 포맷으로 변환하려고 함수를 정의하는 경우, 오라클 내장함수 to_char와 사용자 정의 함수를 사용할 때의 수행시간 비교
create or replace function date_to_char(p_dt date) return varchar2
as
begin
return to_char(p_dt, 'yyyy/mm/dd hh24:mi:ss');
end;
/
create table t ( no number, char_time varchar2(21) ) ;
set timing on
insert into t
select rownum no
, to_char(sysdate + rownum, 'yyyy/mm/dd hh24:mi:ss') char_time
from dual
connect by level <= 1000000 ;
1000000 개의 행이 만들어졌습니다.
경 과: *00:00:04.76*
insert into t
select rownum no
, date_to_char(sysdate + rownum) char_time
from dual
connect by level <= 1000000 ;
1000000 개의 행이 만들어졌습니다.
경 과: *00:00:25.60*
- Recursive Call 없이 컨텍스트 스위칭 효과만으로 보통 5 ~ 10배 정도 느려졌다.
(3) Recursive Call를 포함하는 함수의 성능 부하
- 대개의 사용자 정의 함수에는 Recursive Call을 포함한다.
- Recursive Call도 매번 Execute Call과 Fetch Call을 발생시키기 때문에 대량의 데이터를 조회하면서 레코드 단위로 함수를 호출하도록 쿼리를 작성하면 극도로 나빠진다.
create or replace function date_to_char(p_dt date) return varchar2
as
n 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
/
1000000 개의 행이 만들어졌습니다.
경 과: *00:01:01.87*
- I/O가 전현 발생하지 않는 가벼운 쿼리를 삽입했을 뿐인데, Recursive Call없는 함수와 비교하면 2.3배(책에서는 4배), 함수를 사용하지 않았을 때와 비교하면 14배(책에서는 23배)가량 더 느려졌다.
SELECT 1
FROM DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000000 9.12 8.39 0 0 0 0
Fetch 1000000 3.43 3.10 0 0 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
*total 2000001* 12.56 11.50 0 0 0 1000000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62 (recursive depth: 1)
- 데이터베이스 Call이 200만 번이나 추가로 더 발생했다.
- Parse Call 한번 (함수가 커서를 캐싱한 채로 라이브러리 캐시에 캐싱됨)
- 대용량의 조회쿼리에서 함수 남용은 레코드 수만큼 건건이 함수를 호출함
- 사용자 정의 함수 사용 고려 사항
- 소량의 데이터 조회
- 대용량 조회시 부분범위처리가 가능한 상황
- 조인 또는 스칼라 서브쿼리 형태로 변환
(4) 함수를 필터 조건으로 사용할 때 주의 사항
- 함수를 where절에서 필터 조건을 사용할 때도 각별한 주의가 필요
- 조건절과 인덱스 상황에 따라 함수 호출 횟수가 달라지기 때문
-- emp 테이블을 읽어 평균 급여를 리턴하는 함수 생성
SQL> create or replace function emp_avg_sal return number
2 is
3 l_avg_sal number;
4 begin
5 select avg(sal) into l_avg_sal from emp;
6 return l_avg_sal;
7 end;
8 /
--컬럼 구성을 달리하는 index 4개 생성
SQL> create index emp_x10 on emp(sal);
SQL> create index emp_x20 on emp(deptno);
SQL> create index emp_x30 on emp(deptno, sal);
SQL> create index emp_x40 on emp(deptno, ename, sal);
*< 케이스 1 >*
- 인덱스를 사용하지 않고 Full Scan
SQL> select /*+ full(emp) */ * from emp
# where sal >= emp_avg_sal;
SELECT AVG(SAL) FROM EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 14 0.00 0.00 0 0 0 0
Fetch 14 0.00 0.00 0 42 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 0.00 0.00 0 42 0 14
? Full Scan 할때 emp 테이블의 전체의 건수만큼 함수 호출이 일어났다
*< 케이스 2 >*
- sal 컬럼을 선두로 갖는 인덱스를 이용
SQL> select /*+ index(emp (sal)) */ * from emp
2 where sal > = emp_avg_sal;
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 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
? 함수 호출이 단 한번만 일어남
? 함수를 먼저 실행하고, 거기서 리턴된 값으로 emp_x10 인덱스를 액세스 하는 상수 조건으로 사용
*< 케이스 3 >*
- 조건절에 deptno 추가 deptno 컬럼 하나로 구성된 index 이용
SQL> select /*+ index(emp, emp_x20) */ * from emp
2 where sal >= emp_avg_sal
3 and deptno = 20;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 1 15 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 1 15 0 5
Predicate Information (identified by operation id):
---------------------------------------------------
# - filter("SAL">="EMP_AVG_SAL"())
# - access("DEPTNO"=20)
? deptno = 20 을 만족하는 레코드 5번
? sal >= emp_avg_sal 조건은 테이블 필터 조건으로 사용
*< 케이스 4 >*
- deptno +sal 순으로 구성된 emp_x30 인덱스 이용
SQL> select /* index(emp (deptno, sal)) */ * from emp
2 where sal >= emp_avg_sal
3 and deptno = 20 ;
Predicate Information (identified by operation id):
---------------------------------------------------
# - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"() AND "SAL" IS NOT NULL)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1 0.000 0.000 0 7 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3 0.000 0.000 0 7 0 1
- 함수 호출이 한번만 일어났다.
*< 케이스 5 >*
- deptno와 sal 중간에 ename 컴럼이 낀 emp_x40 인덱스 사용
SQL> select /*+ index(emp (deptno, ename, sal)) */ *
2 from emp
3 where sal >= emp_avg_sal
4 and deptno = 20;
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"())
filter("SAL">="EMP_AVG_SAL"())
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 6 0.000 0.000 0 0 0 0
Fetch 6 0.000 0.000 0 42 0 6
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.000 0.002 0 42 0 6
- 인덱스 스캔할 첫 번째 레코드 액세스 단게에서 1번
- 필터 조건에서 4건을 찾는 동안 4번 (ename 정보가 없어서 필터 조건)
- One-plue (deptno = 20 범위를 넘어 더 이상 조건을 만족하는 레코드가 없음을 확인)
*< 케이스 6 >*
- '=' 조건이 아닌경우
- 인덱스 컬럼 구성상 선행 컬럼이 조건절에 누락되거나 = 조건이 아닌 경우 그 컬럼은 필터 조건으로 사용된다.
SQL> select /*+ index(emp (deptno, sal)) */ * from emp
2 where sal >= emp_avg_sal
3 and deptno >= 10 ;
2 - access("DEPTNO">=10 AND "SAL">="EMP_AVG_SAL"() AND "DEPTNO" IS NOT NULL)
filter("SAL">="EMP_AVG_SAL"()
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 14 0.000 0.001 0 0 0 0
Fetch 14 0.000 0.001 0 98 0 14
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 29 0.000 0.002 0 98 0 14
- 인덱스 스캔할 첫번째 레코드를 엑세스하는 단계 1번
- Deptno >= 10 조건을 만족하는 나머지 13건 스켄 13번
- One-plus 스캔은 없음(맨 마지막 레코드에 도달했기 때문)
- 조건절과 인덱스 구성에 따라 성능 차이가 매우 크게 나타날 수 있음을 이해할 것.
(5) 함수와 읽기 일관성
create table LookupTable ( key number, value varchar2(100) ) ;
insert into LookupTable values( 1, 'YAMAHA' );
commit;
create or replace function lookup(l_input number) return varchar2
as
l_output LookupTable.value%TYPE;
begin
select value into l_output from LookupTable where key = l_input;
return l_output;
end;
/
- 생성한 lookup 함수를 참조하는 쿼리를 수행하고 결과집합을 Fetch 하는 동안 다른 세션에서 LookupTable로부터 value값을 변경한다면?
- 레코드를 Fetch 하면서 lookup 함수가 반복 호출되는데, 중간부터 다른 결과 값을 리턴하게 된다.
- 문장수준 읽기일관성(Statement-level consistency)이 보장 되지 않는다.
- 함수 내에서 수행되는 Recursive 쿼리는 메인 쿼리의 시작 시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을 읽기 때문.
- Deterministic 함수로 선언하거나, 함수에 스칼라 서브쿼리를 덧씌우더라도 이 문제를 완전히 해소할 수는 없다. (캐시의 제한)
- 일반 조인문 또는 스칼라 서브쿼리를 사용할 때만 완벽한 문장수준 읽기 일관성이 보장된다.
- 프로시저, 패키지, 트리거를 사용할 때도 공통적으로 나타나는 현상.
(6) 함수의 올바른 사용 기준
- 함수를 사용했을 때 성능이 느려지는 원리를 이해하고 잘 활용하라
- 트리거를 통해 처리하려 했던 그 목적을 트리거보다 더 빠르게 달성할 수 있는 방법은 없다. 없다면 트리거를 사용해라.
- 조인으로 처리 할 수 있다면 조인으로 처리하라.
- 함수/프로시저를 사용하지 않았을 때 결국 User Call을 발생시키도록 구현해야 한다면, 함수/프로시저를 사용하라.
- 오라클 함수를 사용하지 않고 애플리케이션 단에서 구현하더라도 읽기 일관성 문제가 발생하므로 데이터 일관성이 깨지지 않도록 설계하고 개발하라.
- 모든 프로그램을 PL/SQL 함수와 프로시저로 구현하면 라이브러리 캐시에서 관리할 오브젝트 개수와 크기가 증가하여 히트율 및 경합이 증가해 효율성이 저하된다.
- 오브젝트 정의를 변경하면 라이브러리 캐시 오브젝트간 Dependency 체인을 따라 순간적으로 동시 컴파일을 유발해 시스템 장애로 연결될 가능성도 있다.
(Dependency 체인에 의한 라이브러리 캐시 부하를 최소화하려면 가급적 패키지를 사용하라) - 역할을 분담해 연산 위주의 작업은 애플리케이션 서버 단에서 주로 처리하고, SQL수행을 많이 요하는 작업은 오라클 함수/프로시저를 이용하도록 설계할 필요가 있다.
문서에 대하여
- 최초작성자 : 김종원
- 최초작성일 : 2010년 01월 09일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*