(1) PL/SQL 함수의 특징

  • PL/SQL로 작성된 함수/프로시저
    • Oracle Forms, Roacle Reports 같은 제품에서도 수행가능
    • 컴파일 -> 바이트코드 생성 -> pl/sql 엔진만 있으면 어디서든 실행
    • Interpreted




SQL> show parameter PLSQL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL
SQL>


  • PL/SQL 특징
    • 함수실행시 sql 실행엔진과 pl/sql 가상머신 사이에 컨텍스트 스위칭이 일어남
    • sql 실행 엔진이 사용하던 레이스터 정보를 백업후 pl/sql 엔진이 실행을 마치면 다시 복원
    • c++,java,vb와 같은 일반 언어-> 모듈화, pl/sql 과 함수는 느려질수 있음


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

  • 남용사례





SQL> create or replace function date_to_char(p_dt date) return varchar2
  2  as
  3  begin
  4     return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
  5  end;
  6  /

Function created.

SQL> create table t (no number,char_time varchar2(21) );

Table created.

SQL> set timing on
SQL> insert into t
  2  select rownum no
  3  ,  to_char(sysdate+rownum,'yyyy/mm/dd hh24:mi:ss') char_time
  4  from dual
  5  connect by level <=1000000;

1000000 rows created.

Elapsed: 00:00:06.76
SQL> insert into t
  2  select rownum no
  3  ,  date_to_char(sysdate+rownum) char_time
  4  from dual
  5  connect by level <=1000000;

1000000 rows created.

Elapsed: 00:00:27.02
SQL>



  • 내장함수 to_char -> 6.76초
  • 사용자함수 date_to_char ->27.02
  • Recursive Call 없이 컨텍스트 스위칭 효과만으로 5~10배 느려질수 있음



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

  • User Call
    • 네트워크 트래필 발생
  • Recursive Call
    • 매번 Execute Call과 Fetch call 발생
    • 대용량 데이터 조회시 레코드 단위로 함수를 호출하도록 쿼리 작성시 성능 저하




SQL> create or replace function date_to_char(p_dt date) return varchar2
  2  as
  3     l_empno number;
  4  begin
  5     select 1 into l_empno  from dual;
  6     return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
  7  end;
  8  /


Elapsed: 00:00:00.19

SQL> insert into t
  2  select rownum no
  3     ,date_to_char(sysdate+rownum) char_time
  4  from dual
  5  connect by level <=1000000
  6  /

Elapsed: 00:00:59.78



  • dual 의 경우 i/o 전혀 발생하지 않는 쿼리
  • Recursive Call 없는 함수와 비교하면 수배,
  • 함수를 사용하지 않았을 때와 비교하면 매우 느려짐


1.png

  • 데이터베이스 Call이 200만번 추가 발생
  • dual 이 아니라는 실제라면 훨씬 큰 성능 저하가 발생


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


  • 함수를 where 절에서 필터 조건으로 사용할때 주의 가 필요
  • 조건절과 인덱스 상황에 따라 함수 호출 횟수가 달라짐



create or replace function emp_avg_sal return number
is
    l_avg_sal number;
begin
    select avg(sal) into l_avg_sal from emp;
    return l_avg_sal;
end;
/

SQL> create index EMP_X01 on emp(sal);

Index created.


SQL> create index EMP_X02 on emp(deptno);

Index created.

SQL> create index EMP_X03 on emp(deptno,sal);

Index created.

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

Index created.



<case 1>

2.png

  • 스캔 하면서 읽은 전체 건수 만큼 함수 호출
  • emp table 레코드 14건


<case 2>

 
select /*+ index(emp (sal)) */* from emp
where sal>=emp_avg_sal

  • sal 컬럼을 선두로 갖는 인덱스를 이용하면 함수호출이 단 한번
  • 함수를 먼저 실행하고 , 리턴된 값으로 emp_x01인덱스를 액세스하는 상수 조건으로 사용



<case 3>


3.png  


  • exp_x02 인덱스,. deptno=20
  • deptno=20 ; 5건, sal>=emp_avg_sal ; 3건
  • deptno=20은 인덱스 액세스 조건, sal>= 테이블 필터 조건
  • 테이블 액세스 하는 횟수 만큼 5번의 호출


<case 4>


4.png  


  • deptno+sal , emp_x03 인덱스
  • sa.+ 조건까지 인덱스 액세스
  • 함수 호출이 1번만



<case 5>
 

  • deptno와 sal 컬럼 중간에 ename 컬럼, emp_x04
  • ename 조건이 없어서 sal >= 이 인덱스 액세스 조건과 필터로 같이 사용
  • 첫번째 레코드 액세스 단계에서 1번,
  • 필터단계에서 나머지 4건을 찾는 동안 4번,
  • deptno=20 범위를 넘어서는 조건 확인 에서 +1 , 총 6번의 함수 호출


  • 5.png

<case 6>

  •  6.png


  • ‘=’ 조건이 아닌경우
  • 선행 컬럼이 조건절에 누락되거나 ‘=’ 조건이 아닌 경우 필터 조건으로 사용
  • 인덱스 스캔할 첫번째 레코드 액세스 1번
  • deptno >=10 조건 만족하는 나머지 13건 스캔시 13번
  • 총 14번 발생 (맨마지막 까지 가므로 +1 없음)



조건절사용인덱스함수 호출 횟수
sal>=emp_avg_salFull Table Scan14
sal>=emp_avg_salemp_x01: sal1
sal>=emp_avg_sal
deptno=20
emp_x02: deptno5
sal>=emp_avg_sal
deptmp=20
emp_x03: deptno,sal1
sal>=emp_avg_sal
deptno=20
emp_x04: deptno,ename,sal6
sal>=emp_avg_sal
deptno>=10
emp_x03: deptno,sal14


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


(5) 함수와 읽기 일관성




SQL> create table lookuptable (key number,value varchar2(100) );

Table created.

Elapsed: 00:00:00.16
SQL> insert into lookuptable values (1,'YAMAHA');

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> create or replace function lookup (l_input number) return varchar2
  2  as
  3  l_output lookuptable.value%TYPE;
  4  begin
  5  select value into l_output from lookuptable where key=l_input;
  6  return l_output;
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.05
SQL>




  • lookup 함수를 참조하는 쿼리를 수행하고 결과 집합을 fetch 하는 동안
  • 다른세션에서 lookuptable 로부터 value 값을 변경한다면?
  • fetch 하면서 lookup 함수가 반복 호출되서 중간부터 다른 결과 값을 리턴
  • 문장 수준 읽기 일관성이 보장되지 않음


 session 1

 session 2


SQL>  select lookup(1) from all_objects;

LOOKUP(1)
-----------------------------------------------
YAMAHA
YAMAHA
YAMAHA
YAMAHA
YAMAHA
YAMAHA
YAMAHA
YAMAHA
YAMAHA
YAMAHA

LOOKUP(1)
-------------------------------------------------
YAMAHA
YAMAHA
YAMAHA
YAMAHA
babo
babo
babo
babo
babo
babo
babo

LOOKUP(1)
---------------------------------------------------
babo
babo
babo

 SQL> update lookuptable set value='babo';

1 row updated.

SQL> commit;

Commit complete.


  • 함수 내에서 수행되는 Recursive 쿼리는 메인 쿼리의 시작 시점와 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을 읽음



  • 7.png
  • 주식 종몰별 현재가와 시가총액이 수시로 변함
  • 함수에 스칼라 서브쿼리를 덧씌우더라도 인 문제를 해소할수 업음

  • 아래처럼 조인문또는 스칼라 서브쿼리를 사용할때만 완벽한 문장수준 읽기 일관성이 보장


  • 8.png
  • 프로시저, 패키지, 트리거 사용시 공통으로 나타남


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

  • 정해진 shared pool 크기 내에서 소화할 수 있는 적정 개수의 SQL과 pl/sql 단위 프로그램을 유지하도록 노력해야함
  • 연산 위주의 작업은 애플리케이션 서바단에서
  • SQL 수행이 많은 작업은 오라클 함수 /프로시저를 이용



  • 오라클 고도화 원리와 해법 1 (bysql.net 2012년 1차 스터디)
  • 작성자:  남송휘
  • 최초작성일: 2012년 5월 13 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^