by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

PLSQL 함수의 특징과 성능 부하




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'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.