메뉴 건너뛰기

bysql.net

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

2010.07.12 09:00

실천하자 조회 수:12594

(1) PL/SQL 함수의 특징


  • 오라클 서버가 아닌 Oracle Forms, Oracle Reports 같은 제품에서도 수행 가능 설계

→ PL/SQL 작성 함수와 프로시저 컴파일 시 JAVA언어처럼 바이트 코드 생성(데이터 딕셔너리 저장, 런타임 시 해석)

→ PL/SQL 엔진만 있을 시 어디서든 실행 가능

  • JAVA와 같은 인터프리터 언어

→ Native 코드로 완전 컴파일된 내장 함수(Built-in)에 비해 많이 느림

    (9i부터 Native 코드로 컴파일 가능 기능을 제공했지만 사용상 복잡으로 잘 사용되지 않음)

→ 작성된 함수 실행 시 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭 발생


    ☞ 원리를 잘 알고 사용시 PL/SQL 함수와 프로시저 이용으로 성능을 높일 수 있지만 잘못 사용 시 심각한 성능 부하 발생 가능



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

     <사용자 정의 함수 남용>    Ex) 날짜형 데이터를 공통 문자열 포맷으로 변환하기위해 함수 정의

  • 오라클 내장 함수 to_char와 사용자 정의 함수 사용 시 수행시간 비교 결과

- 내장 함수 (to_char) : 2.45초 소요

- 사용자 정의 함수 (date_to_char) : 15.57초 소요


    ☞ Recursive Call 없이 컨텍스트 스위칭 효과만으로 보통 5~10배 느려짐



(3) Recursive Call를 포함하는 함수의 성능 부하

  • 일반적으로 사용자 정의 함수에 Recursive Call을 포함
  • User Call 에 비해 비용이 적지만 매번 Execute Call, Fetch Call 발생으로 성능 저하

       < Ex - 2번의 사용자 정의 함수(date_to_char)에  DUAL 테이블을 읽는 select 문 삽입 >

- 내장 함수 (to_char) : 2.45초 소요                            → 약 23배

- 기존 사용자 정의 함수 (date_to_char) : 15.57초 소요  → 약 4배

- select 문이 추가 된 사용자 정의 함수  : 1분 5초 소요


    ☞ 대용량 조회 쿼리에서 함수 남용 시 읽는 레코드 수만큼 건건이 함수 호출 발생으로 성능이 극도록 저하

    ☞ 사용자 정의 함수는 소량의 데이터 조회 시에만 사용

    ☞ 대용량 조회 시, 부분범위 처리가 가능한 상황에서 제한적 사용

    ※ 성능을 위해서는 되도록 조인 또는 스칼라 서브쿼리 형태로 변환




(4) 함수를 필터 조건으로 사용할 때 주의 사항

  함수를 where 절에서 필터 조건으로 사용 시 주의 필요

  ∵ 조건절과 인덱스 상황에 따라 함수 호출 횟수가 다름


< Ex - 환경 설정 >

   - 함수 생성 : emp 테이블의 평균 급여 리턴

   - 인덱스 생성 : 컬럼 구성을 달리하여 4개 생성


create index EMP_X01 on emp(sal);

create index EMP_X02 on emp(deptno);

create index EMP_X03 on emp(deptno, sal);

create index EMP_X04 on emp(deptno, ename, sal);


ㅁ 케이스 1 : 인덱스 미사용, Full Scan

→ 스캔하면서 읽은 전체 건수만큼 함수 호출 발생 (Excute Call 횟수 확인)


ㅁ 케이스 2 : EMP_X01 인덱스 액세스 (where sal >= emp_avg_sal)

함수 호출 단 한번 발생. 함수를 먼저 실행 후 리턴값으로 인덱스 액세스 


ㅁ 케이스 3 : EMP_X02 인덱스 액세스 (where sal >= emp_avg_sal and deptno = 20)

    deptno = 20 만족 카디날리티  : 5건

    sal >= emp_avg_sal 까지 만족 카디날리티 : 3건

→ "deptno = 20" 조건으로 인덱스 액세스를 하지만 sal 컬럼이 조건으로 사용되어 테이블 액세스 횟수인

   5번의 함수 호출 발생


ㅁ 케이스 4 : EMP_X03 인덱스 액세스 (where sal >= emp_avg_sal and deptno = 20)

→ 인덱스에서 sal, deptno 컬럼을 둘다 이용하였기에 인덱스 액세스 조건으로 사용되는 함수 호출 1번 발생


ㅁ 케이스 5 : EMP_X04 인덱스 액세스 (where sal >= emp_avg_sal and deptno = 20)

→ 인덱스에서 조건에 없는 ename 포함으로 필터 조건으로도 사용

→ 인덱스 스캔할 첫 번째 레코드 액세스 단계에서 함수 호출 1번

→ 필터 단계에서 나머지 4건을 찾는 동안 함수 호출 4번

→ deptno= 20 범위를 벗어나 더이상 조회 레코드가 없는 것을 확인하는 one-plus 스캔 과정에서 함수 호출 1번

총 6번 함수 호출 발생


ㅁ 케이스 6  : 등호가 없는 범위기반 조건 (where sal >= emp_avg_sal and deptno >= 10)

    참고) 인덱스 컬럼 구성상 선행 컬럼이 조건절에 누락 또는 '='조건이 아닐 시 해당 컬럼을 필터 조건으로 사용

→ 인덱스 스캔 시 첫 번째 레코드 액세스 단계에서 함수 호출 1번

→ "deptno >= 10" 조건만족 13건 스캔하는 동안 13번 호출

총 14번 함수 호출 발생


< 요약 >

조건절

사용 인덱스

       함수 호출 횟수       

 sal >=emp_avg_sal              

 Full Table Scan   

 14

 sal >=emp_avg_sal   

 emp_X01 : sal

 1

 sal >=emp_avg_sal

 deptno = 20  

 emp_X02 :  deptno  

 5

 sal >=emp_avg_sal   

 deptno = 20

 emp_X03 :  deptno, sal   

 1

 sal >=emp_avg_sal   

 deptno = 20

 emp_X04 :  deptno, ename, sal                

 6

 sal >=emp_avg_sal   

 deptno >= 10

 emp_X03 :  deptno, sal

 14


     ☞ 예제는 작은 테이블 사용으로 큰 성능의 차이를 느낄 수 없지만

         대용량 테이블에서는 조건절과 인덱스 구성에 따라 성능 차이가 크게 나타남




(5) 함수와 읽기 일관성

  함수를 사용한 쿼리가 수행되어 결과 집합이 Fetch 되는 동안 다른 세션에서 함수가 읽고있는 테이블의 값이 변경 시

  중간부터는 다른 결과 값을 리턴하는 경우 발생

  ☞ 읽기 일관성 보장 되지 않음

  ∵ 함수 내에서 수행되는 Recursive 쿼리는 메인 쿼리의 시작 시점과 무관하게 해당 쿼리 수행 시점을 기준으로 블록 Read


  ☞ 오라클만의 독특한 읽기 일관성 모델을 정확히 이해하지 않는 상태에서 함수/프로시저를 잘못 사용 시

      성능을 떨어뜨릴 뿐 아니라 데이터 정합성까지 해칠 수 있음

       → 불필요한 함수 / 프로시저보다 조인문 또는 스칼라 서브쿼리 사용




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

  •   함수를 사용 시 성능이 느려지는 원리를 이해하고 잘 활용하여 성능 향상
  •   함수/프로시저를 사용하지 않아 User Call을 발생하도록 구현될 시, 오라클 함수/프로시저를 사용하는 편이 더 나음
  •   함수 사용 시 데이터 일관성이 깨지지 않도록 설계 및 개발
  •   정해진 Shared Pool 크기 내에서 소화 할 수 있는 적정 개수의 SQL 과 PL/SQL 단위 프로그램을 유지하도록 노력


  Tip) 연산 위주의 작업 → 애플리케이션 서버 단에서 주로 처리

         SQL 수행이 많은 작업 → 오라클 함수/프로시저를 이용하도록 설계