오라클 성능 고도화 원리와 해법 I (2016년-2)
07. PL/SQL 함수의 특징과 성능 부하
(1)PL/SQL 함수의 특징
- 이식성을 고려 오라클서버가 아닌 제품에서도 수행되도록 설계
- PL/SQL 컴파일하면 JAVA 언어처럼 바이트코드가 생성==> 해석하고 실행할 수 있는 PL/SQL 엔진(가상머신)만 있다면 어디서든 실행 가능
- 바이트코드는 데이터 딕셔너리에 저장, 런타임 시 해석
- PL/SQL도 JAVA처럼 인터프리터 언어이기 때문에 Native 코드로 완전 컴파일된 내장(Built-In) 함수에 비해 많이 느림
- 이 문제를 극복하려고 오라클 9i부터 해당 플랫폼 Native 코드로 컴파일 할 수 있는 기능을 제공하기 시작했지만 사용상 복잡
show parameter PLSQL
| NAME | TYPE | VALUE |
| plsql_v2_compatibility | 1 | FALSE |
| plsql_warnings | 2 | DISABLE:ALL |
| plsql_code_type | 2 | INTERPRETED |
| plsql_debug | 1 | FALSE |
| plsql_optimize_level | 3 | 2 |
| plsql_ccflags | 2 |
- 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수행을 많이 요하는 작업은 오라클 함수/프로시저를 이용
"구루비 DB 스터디 모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3185
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.