05.데이터베이스 Call 최소화 원리 - 07.PL/SQL 함수의 특징과 성능부하

(1) PL/SQL함수의 특징
오라클은 PL/SQL로 작성된 함수/프로시저의 이식성을 고려해 오라클 서버가 아닌 Oracle Forms, Oracle Reports 같은 제품에서도 수행될 수 있도록 설계하였습니다. 그래서 PL/SQL로 작성한 함수와 프로시저를 컴파일하면 JAVA언어처럼 바이트코드가 생성되며, 이를 해석하고 실행할 수 있는 PL/SQL엔진만 있다면 어디서든 실행될 수 있습니다. 바이트코드는 데이터 딕셔너리에 저장되었다가 런타임 시 해석됩니다.
지금은 가장 인기 있는 개발언어가 된 JAVA가 초기에 고전했던 이유는 바로 속도 때문이었는데, PL/SQL도 JAVA처럼 인터프리터 언어기 때문에 Native 코드로 완전 컴파일된 내장 함수에 비해 많이 느립니다. 이 문제를 극복하려고 오라클 9i부터 해당 플랫폼 Native 코드로 컴파일할 수 있는 기능을 제공하기 시작했지만 사용상 복잡성 때문에 잘 사용되지 않고 있습니다.
그리고 연산 위주의 작업을 주로 수행한다면 큰 효과를 보겠지만, 뒤에서 설명하는 것처럼 함수/프로시저 성능이 나빠지는 이유가 대부분 그 안에서 Recursive SQL을 수행하기 때문이므로 Native 코드로 컴파일하더라도 큰 도움이 되지 못할때가 많습니다. PL/SQL은 인터프리터 실행 환경에서 개발하기 때문에 이를 기준으로 설명합니다.

PL/SQL은 그것으로 작성한 함수 실행 시 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어납니다. SQL에서 함수를 호출할 때마다 SQL 실행엔진이 사용하던 레지스터 정보들을 백업했다가 PL/SQL엔진이 실행을 마치면 다시 복원하는 작업을 반복하게 되므로 느려질 수 밖에 없습니다.
C++,JAVA,VB와 같은 일반 프로그래밍 언어에서는 될 수 있으면 함수/프로시저를 이용해 잘게 모듈화,공용화하는 것이 권장사항이지만 PL/SQL함수와 프로시저를 그런식으로 활용하면 안되는 이유가 있습니다. 원리를 잘 알고 사용하면 성능을 높일 수 있지만 잘못 사용하면 성능부하를 일으킵니다.

(2) Recursive Call을 포함하지 않는 함수의 성능 부하
가장 흔히 볼 수 있는 사용자 정의함수 사례입니다. 날짜형 데이터를 공통 문자열 포맷으로 변환하려고 함수를 정의하는 경우입니다.

오라클 내장함수to_char와 사용자정의 함수를 사용할 때의 수행시간을 비교하였더나 2.45초가 수행된 것이 15.57초로 늘어났습니다. 서버 사양에 따라 다른데, Recursive Call 없이 컨텍스트 스위칭 효과만으로 보통 5~10배정도 느려집니다. 수행시간이 오래 걸리는 쿼리일 수록 느려지는 효과를 체감하는 것이 심해질 것입니다.

(3)Recursive Call을 포함하는 함수의 성능부하
대개의 사용자 정의함수에는 Recursive Call을 포함합니다. 네트워크 트래픽을 발생시키는 User Call에 비해 그 비용이 훨씬 적지만 Recursive Call도 매번 Execute Call과 Fetch Call을 발생시키기 때문에 대량의 데이터를 조회하면서 레코드 단위로 함수를 호출하도록 쿼리를 작성하면 성능이 극도로 나빠집니다.
위에서 사용했던 date_to_char 함수에 dual 테이블을 읽는 간단한 select문을 삽입하고 다시 테스트 해보겠습니다.

1:04.57초가 소요되었습니다. I/O가 발생하지 않는 쿼리(10g부터는 dual테이블을 쿼리하면 fast dual방식으로 수행되므로 I/O가 전혀 발생하지 않습니다)를 삽입하였지만 Recursive Call 없는 함수와 비교하면 4배, 함수를 사용하지 않았을 때와 비교하면 23배가량 느려졌습니다.

SQL트레이스 결과를 보면 데이터베이스 Call이 execute시 1000000, Fetch시 1000000건해서 총 200만 건이나 추가로 발생하였습니다. 다행히 Parse Call은 한번뿐입니다. 함수가 커서를 캐싱한 채로 라이브러리 캐시에 캐싱 돼 있었기 때문입니다. 여기에는 앞의 테스트와 공정한 비교를 위해 추가 I/O를 발생하지 않도록 dual테이블을 사용했지만 실제 테이블을 사용한다면 Recursive Call은 대부분 I/O를 수반하게 되므로 훨씬 큰 성능저하를 일으킵니다.
따라서 사용자 정의 함수는 소량의 데이터 조회 시에만 사용하거나, 대용량 조회 시에는 부분범위처리가 간으한 상황에서 제한적으로 사용해야 합니다. 그리고성능을 위해서라면 가급적 조인 또는 스칼라 서브쿼리 형태로 변환하려고 노력해야 합니다.

(4)함수를 필터조건으로 사용할 때 주의 사항
함수를 where 절에서 필터 조건으로 사용할 때도 주의가 필요합니다. 조건절과 인덱스 상황에 따라 함수 호출 횟수가 달라지기 떄문입니다.
테스트를 위해 emp 테이블을 읽어 평균 급여를 리턴하는 함수를 만들고, 컬럼 구성을 달리하는 인덱스 4개를 만들겠습니다.

<케이스1>
인덱스를 사용하지 않고 Full Scan할때는 스캔하면서 읽은 전체 건수만큼 함수 호출이 일어납니다. 함수 내에서 수행된 SQL의 수행횟수(execute 횟수)를 체크해보면 알 수 있습니다.

<케이스2>
sal컬럼을 선두로 갖는 인덱스를 이용하도록 하면 함수 호출이 단 한번만 일어납니다. 함수를 먼저 실행하고, 거기서 리턴된 값으로 EMP_X01인덱스를 액세스하는 상수 조건으로 사용되기 때문입니다.
select /*+ index(emp (sal)) */ * from emp
where sal >= emp_avg_sal

<케이스3>
조건절에 deptno=20 조건을 하나 더 추가하고, deptno 컬럼 하나만으로 구성된 EMP_X02 인덱스를 이용하도록 해보겠습니다.
deptno=20조건만을 만족하는 레코드는 5건이고, sal>=emp_avg_sal 조건필터링을 거친 최종 건수는 3건입니다.
deptno=20조건은 인덱스 액세스 조건으로 사용되지만, sal >= 조건은 테이블 필터조건으로 사용되므로 테이블을 액세스하는 횟수만큼 5번의 함수 호출이 일어납니다.

<케이스4>
dept+sal순으로 구성된 EMP_X03인덱스를 이용해보겠습니다.
그러면 sal >= 조건까지 인덱스 액세스 조건으로 사용되므로 함수 호출이 1번만 일어납니다.

<케이스5>
조건절을 같은데, deptno와sal 컬럼 중간에 ename 컬럼이 낀 EMP_X04인덱스를 사용해보겠습니다. 그러면 함수 호출이 6번 일어납니다. sql >= 조건이 인덱스 액세스 조건으로 사용되지만 ename조건이 없는 상황이어서 필터조건으로도 사용되는 것을 볼 수 있습니다. 따라서 인덱스를 스캔할 첫 번째 레코드 액세스 단계에서 1번, 필터 단계에서 나머지 4건을 찾는 동안 4번, deptno=20범위를 넘어 더 이상 조건을 만족하는 레코드가 없을을 확인하는 one-plus 스캔과정에서 1번 총6번의 함수 호출이 일어납니다.

<케이스6>
앞에서는 선행 컬럼이 누락된 경우를 보았고, 케이스6은 =조건이 아닌 경우입니다. 인덱스 컬럼 구성상 선행 컬럼이 조건절에 누락되거나, =조건이 아닌 경우 그 컬럼은 필터 조건으로 사용됩니다. 따라서 함수 호출이 인덱스를 스캔할 첫 번째 레코드를 액세스하는 단계에서 1번, deptno >= 10조건을 만족하는 나머지 13건을 스캔하는 동안 13번, 그래서 총 14번이 발생합니다. 여기서는 맨 마지막 레코드에 도달하였으므로 one-plus 스캔에 의한 함수 호출은 없습니다.

(5)함수와 읽기 일관성
성능 문제 외에도 사용자 정의 함수를 사용할 때 꼭 알아야할 사항이 있습니다. 1장에서 설명한 읽기 일관성과 관련이 있습니다.

여기서 생성한 lookup함수를 참조하는 쿼리가 있다고 가정하고 그 쿼리를 수행하고 결과 집합을 Fetch하는 동안 다른 세션에서 lookupTable로 부터 value값을 변경한다면 어떤일이 발생할까요. 레코드를 Fetch하면서 lookup함수가 반복 호출되는데, 중간부터 다른 결과 값을 리턴하게 됩니다. 가장 기본적인 문장수준 읽기 일관성이 보장되지 않으므로써 함수 내에서 수행되는 Recursive 쿼리는 메인 쿼리의 시작 시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을 읽기 때문에 생기는 현상입니다.

(6)함수의 올바른 사용기준
함수를 가급적 쓰지말라는게 아니라 원리를 이해하고 잘 사용해야 합니다.
정해진 Shared Pool 크기 내에서 소화할 수 있는 적정 개수의 SQL과 PL/SQL단위 프로그램을 유지하도록 노력해야 합니다. 그러려면 역할을 분담해 연산 위주의 작업은 애플리케이션 서버 단에서 주로 처리하고, SQl수행을 많이 요하는 작업은 오라클 함수/프로시저를 이용하도록 설계할 필요가 있습니다.