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

PLSQL 함수의 특징과 성능 부하




PLSQL 함수의 특징과 성능 부하

(1)PL/SQL 함수의 특징

1. 인터프리터 언어다.

SQL> show parameter plsql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
plsql_debug                          boolean     FALSE
plsql_native_library_dir             string
plsql_native_library_subdir_count    integer     0
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL

2. Oracle Forms, Oracle Reports 같은 제품에서도 수행될 수 있도록 설계
3. PL/SQL엔진(가상머신, Virtual machine)만 있으면 어디서든 실행가능.
4. native 코드로 완전 컴파일된 내장 함수에 비해 많이 느리다.
5. 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.

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

내장함수 TO_CHAR와 사용자정의한 함수를 사용했을때 차이
SQL> create or replace function data_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  /

함수가 생성되었습니다.

SQL> create table t (no number, char_time varchar2(21));
테이블이 생성되었습니다.

경   과: 00:00:00.01

-- 내장함수사용
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 개의 행이 만들어졌습니다.
경   과: 00:01:52.21

-- 사용자가만든 함수사용
SQL> insert into t
  2  select rownum no,
  3         data_to_char(sysdate+rownum) char_time
  4         from dual
  5         connect by level <= 1000000;

1000000 개의 행이 만들어졌습니다.
경   과: 00:03:27.40

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

select 문 삽입후 테스트

- 책에 소스 오류있슴.
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
  7    return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
  8  end;
  9  /

함수가 생성되었습니다.

경   과: 00:00:00.03

SQL> delete from t;

2000000 행이 삭제되었습니다.

경   과: 00:01:27.14
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 개의 행이 만들어졌습니다.
경   과: 00:01:51.39

- 트레이스결과
SELECT 1 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1000001     15.09      12.98          0          0          0           0
Fetch   1000000     10.64       8.73          0          0          0     1000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   2000002     25.73      21.71          0          0          0     1000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 73     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
1000000  FAST DUAL  (cr=0 pr=0 pw=0 time=6174443 us)


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

함수를 where절에 필터조건으로 사용할때
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
  7        return l_avg_sal;
  8      end;
  9      /

함수가 생성되었습니다.

SQL> create index emp_x01 on emp(sal);
인덱스가 생성되었습니다.

SQL> create index emp_x02 on emp(deptno);
인덱스가 생성되었습니다.

SQL> create index emp_x03 on emp(deptno, sal);
인덱스가 생성되었습니다.

SQL> create index emp_x04 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.01       0.00          0          0          0           0
Execute     15      0.01       0.02          0          0          0           0
Fetch       14      0.00       0.00          0         98          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       30      0.03       0.02          0         98          0           6

<케이스 2 : 인덱스를 이용하도록 하면 함수호출이 한번일어난다.>
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        2      0.00       0.00          0          4          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           6

<케이스 3 : 조건절에 deptno = 20 추가, exp_x02인덱스이용하여 조회>
SQL>  select /*+ index(emp, emp_x02) */ * from emp
  2         where sal >= emp_avg_sal   <-- 필터링 건수 3건
  3           and deptno =20;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X02 |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">="EMP_AVG_SAL"())
   2 - access("DEPTNO"=20)

테이블액세스 하는 횟수만큼 5번 함수호출이 일어난걸 확인할수있다.

<케이스 4 : deptno + sal 순으로 구성된 emp_x03 인덱스이용
SQL>  select /*+ index(emp(deptno, sal) */ * from emp
  2         where sal >= emp_avg_sal
  3           and deptno =20;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X03 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"() AND "SAL" IS NOT NULL)

sal >= 까지 조건까지 인덱스 조건으로 사용으로 함수호출이 한번 일어난걸확인

<케이스 5 : 조건은 같고, deptno 와 sal 컬럼중간인 ename 컬럼이 낀 emp_x04 인덱스사용
SQL> select /*+ index(emp(deptno, ename, sal)) */ * from emp
  2         where sal >= emp_avg_sal
  3           and deptno =20;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X04 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"())
       filter("SAL">="EMP_AVG_SAL"())

첫번째 액세스 단계에서 1번
필터 단계에서 나머지 4건을 찾는동안 4번 , deptno = 20 범위를 넘어 더이상 조건을 만족하는 레코드가 
없음을 확인하는 one-plus 스캔과정에서 1번, 하여 총6번의 함수 호출이 일어난다고 하는데 1번만 발생.

<케이스 6 : = 조건이 아닌경우
SQL>  select /*+ index(emp(deptno, sal)) */ * from emp
  2         where sal >= emp_avg_sal
  3           and deptno >=10;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X03 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO">=10 AND "SAL">="EMP_AVG_SAL"() AND "DEPTNO" IS NOT NULL)
       filter("SAL">="EMP_AVG_SAL"())

인덱스 스캔할 첫 번째 레코드를 액세스하는 단계에서 1번
deptno >= 10 조건을 만족하는 나머지 13건을 스캔하는 동안 13번
하여 총 14번 스캔이 말생한다고 하는데 테스트 결과는 한번만 나왔다.

  • 대용량테이블에서 조건절과 인덱스 구성에 따라 성능 차이가 매우 크게나타날 수 있음을 확인할수있다.

(5) 함수와 읽기 일관성

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

테이블이 생성되었습니다.

SQL> insert into lookuptable values (1, 'BSHMAN');

1 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

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  /

함수가 생성되었습니다.

SQL> update lookuptable
  2  set value ='BSH';

1 행이 갱신되었습니다.

SQL> commit;

커밋이 완료되었습니다.

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

적당히 알아서 잘써야한다.(때와 장소를 맞쳐서 개발자님이 만들어주세요,)
뒷장에서 나오겟지만, 개발자입장에서는 함수하나로 모든걸 컨트롤할수있는걸
속도때문에 개발의 효율성을 떨어트리는 일이 발생되므로 좀 계륵이지 않을수없다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 6월 26, 2012

    배성환 says:

    case 1와 , case 2의 같은조건인데 왜 row가 다른가 확인

    case 1와 , case 2의 같은조건인데 왜 row가 다른가 확인