07. PL/SQL 함수의 특징과 성능 부하

(1)PL/SQL 함수의 특징

  • 이식성을 고려 오라클서버가 아닌 제품에서도 수행되도록 설계
  • PL/SQL 컴파일하면 JAVA 언어처럼 바이트코드가 생성==> 해석하고 실행할 수 있는 PL/SQL 엔진(가상머신)만 있다면 어디서든 실행 가능
  • 바이트코드는 데이터 딕셔너리에 저장, 런타임 시 해석
  • PL/SQL도 JAVA처럼 인터프리터 언어이기 때문에 Native 코드로 완전 컴파일된 내장(Built-In) 함수에 비해 많이 느림
  • 이 문제를 극복하려고 오라클 9i부터 해당 플랫폼 Native 코드로 컴파일 할 수 있는 기능을 제공하기 시작했지만 사용상 복잡

show parameter PLSQL

NAMETYPEVALUE
plsql_v2_compatibility1FALSE
plsql_warnings2DISABLE:ALL
plsql_code_type2INTERPRETED
plsql_debug1FALSE
plsql_optimize_level32
plsql_ccflags2
  • PL/SQL은 인터프리터 언어로 실행시 매번 SQL 실행엔진과 PL/SQL 가상멋신 사이에 컨텍스트 스위칭(Context Switching) 발생
  • SQL에서 함수를 호출할 때마다 SQL 실행엔진이 사용하던 레지스터 정보들을 백업했다가 PL/SQL 엔진이 실행을 마치면 다시 복원하는 작업을 반복 ==> 느려짐
  • PL/SQL 을 일반 프로그래밍 언어처럼 잘게 모듈화, 공용화 지양( 잘못 사용하면 심각한 성능 부하)

(2) Recursive Call를 포함하지 않는 함수의 성능 부하

사용자 정의 함수 남용(날짜형을 문자열 포멧으로 변환)

  • Recursive Call 없이 컨텍스트 스위칭 효과만으로 5~10배 속도 저하

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) ) ;  
insert into t 
select rownum no 
     , to_char(sysdate + rownum, 'yyyy/mm/dd hh24:mi:ss') char_time 
from   dual 
connect by level <= 1000000 ; 
  
경   과: 00:00:03.88 
 
insert into t 
select rownum no 
     , date_to_char(sysdate + rownum) char_time 
from   dual 
connect by level <= 1000000 ;  
 
경   과: 00:00:12.24

(3) 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  ;
 
 
경   과: 00:00:35.79

  • I/O 가 발생하지 않는 가벼운 쿼리를 삽입했을 뿐인데 느려짐

SELECT 1  
FROM DUAL 
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          0          0           0 
Execute 1000000      15.18       14.21          0          0          0           0 
Fetch   1000000      15.35       13.79          0          0          0     1000000 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total   2000001     30.54      28.00          0          0          0     1000000 
 
Misses in library cache during parse: 1 
Optimizer mode: ALL_ROWS 
Parsing user id: 61     (recursive depth: 1)

Rows      Row Source Operation
-------- ----------------------------
1000000   FAST DUAL (cr=0 pr=0 pw=0 time=10983723 us)

  • 쿼리하나 수행하는데 데이타베이스 Call 이 200만 번 추가 발생(캐싱돼 있어서 Parse Call 한번)
  • 실제로 Recursive Call 는 I/O 를 수반하므로 훨씬더 큰 성능저하

함수사용의 제한

  • 사용자 정의 함수는 소량의 데이터 조회시만 사용
  • 대용량조회시는 부분범위처리 가능한 상황에서만 제한적으로 사용
  • 성능을 위해서라면 가급적 조인이나 스칼라 서브쿼리 형태로 변환

(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 (전체 건수만큼 함수 호출)


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 
 

< 케이스 2 > : sal 컬럼을 선두로 갖는 인덱스를 이용 , 단 한번만 함수 호출(함수 먼저 실행하고 리턴된 값으로 EMP_X01 인덱스 엑세스 하는 상수 조건으로 사용)


select /*+ index(emp (sal)) */ * from emp   
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 

< 케이스 3 > : 조건절에 deptno 컬럼 하나로 구성된 emp_x20 인덱스 이용

  
select /*+ index(emp, emp_x20) */ * from emp  
where sal >= emp_avg_sal  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) 
 

  • dept_no = 20 은 인덱스 조건(5건), sal >= emp_avg_sal 필터 조건(3건)으로 사용
  • 테이블 엑세스 횟수 (5회) 만큼 함수 호출

< 케이스 4 > : deptno +sal 순으로 구성된 emp_x30 인덱스 이용 (함수 호출이 한번 )

  
select /* index(emp (deptno, sal)) */ * from emp 
where sal >= emp_avg_sal 
 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 인덱스 사용 (함수호출 6번)


select /*+ index(emp (deptno, ename, sal)) */ *   from emp 
where sal >= emp_avg_sal 
  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 >

  • '=' 조건이 아닌경우
  • 인덱스 컬럼 구성상 선행 컬럼이 조건절에 누락되거나 = 조건이 아닌 경우 그 컬럼은 필터 조건으로 사용된다.

select /*+ index(emp (deptno, sal)) */ * from emp 
where sal >= emp_avg_sal 
 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' );  
 
 
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 쿼리는 메인 쿼리의 시작 시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을 읽기 때문 발생

프로시저, 패키지, 트리거를 사용할 때도 공통적으로 나타나는 현상으로 주의

(6) 함수의 올바른 사용 기준

  • 함수를 사용했을 때 성능이 느려지는 원리를 이해하고 활용 ==> 채번함수(lock 경합 최소화)
  • 트리거를 통해 처리하려 했던 그 목적을 트리거보다 더 빠르게 달성할 수 있는 없다면 트리거를 사용
  • 가급적 조인으로 처리
  • 결국 User Call을 발생시키도록 구현해야 한다면, 함수/프로시저를 사용
  • 애플리케이션 단에서 구현하더라도 읽기 일관성 문제가 발생, 데이터 일관성을 유지 설계, 개발
  • PL/SQL 함수와 프로시저로만 구현하면 라이브러리 캐시에서 관리할 오브젝트 개수와 크기가 증가, 히트율 및 경합이 증가로 효율성이 저하
  • 오브젝트 정의를 변경하면 오브젝트간 Dependency 체인을 따라 순간적으로 동시 컴파일을 유발해 시스템 장애 발생(가급적 패키지를 사용 :http://www.gurubee.net/lecture/1075 )
  • 정해진 Shared Pool 크기내에서 적정 SQL 과 PL/SQL 단위 프로그램 유지
    ==> 역할을 분담해 연산 위주의 작업은 애플리케이션 처리, SQL수행을 많이 요하는 작업은 오라클 함수/프로시저를 이용