오라클 성능 고도화 원리와 해법 I (2012년)
PL/SQL 함수 호출 부하 해소
사용자정의 함수의 사용범위
- 소량의 데이터 조회시
- 대용량 데이터를 조회 할때는 부분범위 처리가 가능한 상황에서 제한적으로
- 조인 또는 스칼라 서브쿼리 형태로 변환하려는 노력이 필요
- 어쩔 수 없을 때는 사용하지만, 호출 횟수를 최소화 할수 있는방법을 강구
함수 호출 부하 해소 방안
1) 페이저 처리 또는 부분범위 처리활용
SQL> select *
2 from (
3 select rownum no , a.*
4 from (
5 select lookup(1)
6 from all_objects
7 order by 1
8 ) a
9 where rownum <= 300
10 ) where no between 21 and 300;
280 개의 행이 선택되었습니다.
경 과: 00:00:08.04
select lookup(1),no
from (
select rownum no , a.*
from (
select rownum
from all_objects
order by 1
) a
where rownum <= 300
) where no between 21 and 300
280 개의 행이 선택되었습니다.
경 과: 00:00:02.17
맨마지막에서 함수가 일어나게 처리함으로 속도개선향상
2) Decode, Case 함수 문으로 변환
-- 사용자정의함수
두개의 테이블을 만들고
create table t2 (no varchar2(2));
create table t3 (no varchar2(2));
t2 테이블에 1~5의 값이 들어간 데이터 백만건입력
SQL> insert into t2
2 select ceil(dbms_random.value(1,5)) no from dual
3 connect by level<= 1000000;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:07.78
SQL> commit;
커밋이 완료되었습니다.
함수 생성
CREATE OR REPLACE function trans
(
Str varchar2
)
RETURN VARCHAR2
IS
RET_VAL VARCHAR2 (4000);
BEGIN
if str = '1' then
RET_VAL := '일';
elsif str = '2' then
RET_VAL := '이';
elsif str = '3' then
RET_VAL := '삼';
elsif str = '4' then
RET_VAL := '사';
elsif str = '5' then
RET_VAL := '오';
end if;
RETURN RET_VAL;
end;
-사용자정의 insert
SQL> insert into t3
2 select trans(no) from t2;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:50.76
- 내장함수사용 insert
SQL> insert into t3
2 select decode(no,'1','일','2','이','3','삼','4','사','5','오') no from t2;
1000000 개의 행이 만들어졌습니다.
경 과: 00:01:06.89
-- decode사용
SQL> delete from t3;
2000000 행이 삭제되었습니다.
경 과: 00:01:24.54
SQL> insert into t3
2 select decode(no,'1','일','2','이','3','삼','4','사','5','오') no from t2;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:10.31
-- case사용
SQL> delete from t3;
1000000 행이 삭제되었습니다.
경 과: 00:00:29.32
SQL> insert into t3
2 select case when no = '1' then '일'
3 when no = '2' then '이'
4 when no = '3' then '삼'
5 when no = '4' then '사'
6 when no = '5' then '오' end no from t2;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:02.96
-- 테이블조인
SQL> create table t_code(no,korea_no)
2 as
3 select'1','일' from dual union all
4 select'2','이' from dual union all
5 select'3','삼' from dual union all
6 select'4','사' from dual union all
7 select'5','오' from dual;
SQL> alter table t_code add constraint t_code_pk primary key(no);
테이블이 변경되었습니다.
SQL> delete from t3;
1000000 행이 삭제되었습니다.
경 과: 00:00:24.07
SQL> insert into t3
2 select korea_no
3 from t2 a , t_code b
4 where a.no = b.no;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:01.85
3) 뷰 머지 방지를 통한 함수 호출 최소화
SQL> select sum(decode(trans(no),'일',1,0)) "1의총계",
2 sum(decode(trans(no),'이',1,0)) "2의총계",
3 sum(decode(trans(no),'삼',1,0)) "3의총계",
4 sum(decode(trans(no),'사',1,0)) "4의총계",
5 sum(decode(trans(no),'오',1,0)) "5의총계"
6 from t2
7 ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 22.65 22.70 0 1571 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 22.65 22.71 0 1572 0 1
경 과: 00:00:23.45
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select trans(no) no from t2
8 ) a;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 26.64 26.83 0 1571 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 26.64 26.83 0 1572 0 1
경 과: 00:00:24.09
-- NO_MERGE 흰트를 주다...
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select /*+ NO_MERGE */trans(no) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
경 과: 00:00:06.10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.46 5.48 0 1571 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.46 5.48 0 1572 0 1
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select rownum,trans(no) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
경 과: 00:00:05.39
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select trans(no) no from t2
8 where rownum > 0
9 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
경 과: 00:00:06.96
4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select (select trans(no) from dual) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
경 과: 00:00:00.89
select sum(decode(no,'일',1,0)) "1의총계",
sum(decode(no,'이',1,0)) "2의총계",
sum(decode(no,'삼',1,0)) "3의총계",
sum(decode(no,'사',1,0)) "4의총계",
sum(decode(no,'오',1,0)) "5의총계"
from (
select (select trans(no) from dual) no from t2
) a
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.76 0.76 0 1571 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.76 0.76 0 1572 0 1
-- _query_execution_cache_max_size 파라미터에 의해 캐시 사이즈를 증가후 테스트
SQL> alter session set "_query_execution_cache_max_size" =2097152;
세션이 변경되었습니다.
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select (select trans(no) from dual) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
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.82 0.82 0 1571 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.82 0.82 0 1571 0 1
경 과: 00:00:00.78
별로 변화가 없어서 줄이고 해보았다.
SQL> alter session set "_query_execution_cache_max_size" =100;
세션이 변경되었습니다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 2.00 1.98 0 3142 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 2.00 1.98 0 3142 0 2
5) Deterministic 함수의 캐싱 효과 활용
SQL> create or replace function accum(p_input number) return number
2 deterministic
3 as
4 rvalue number := 0;
5 call_cnt number := 0;
6 call_cnt2 number := 0;
7
8 begin
9 dbms_application_info.read_client_info(call_cnt);
10 if call_cnt is null then
11 call_cnt := 0;
12 end if;
13 call_cnt := call_cnt+1;
14 dbms_application_info.read_client_info(call_cnt2);
15
16 for i in 1..p_input loop
17 rvalue := rValue + i;
18 end loop;
19 return rvalue;
20
21 end;
22 /
SQL> select sum(accum_num)
2 from (
3 select accum(mod(rownum,50)) accum_num
4 from dual
5 connect by level <= 1000000
6 );
SUM(ACCUM_NUM)
--------------
416500000
경 과: 00:00:01.67
SQL> create or replace function accum(p_input number) return numb
2 deterministic
3 as
4 rvalue number := 0;
5 call_cnt number := 0;
6 call_cnt2 number := 0;
7
8 begin
9 dbms_application_info.read_client_info(call_cnt);
10 if call_cnt is null then
11 call_cnt := 0;
12 end if;
13 --call_cnt := call_cnt+1;
14 dbms_application_info.set_client_info(call_cnt+1);
15
16 for i in 1..p_input loop
17 rvalue := rValue + i;
18 end loop;
19 return rvalue;
20
21 end;
22 /
함수가 생성되었습니다.
경 과: 00:00:00.01
SQL> select sum(accum_num)
2 from (
3 select accum(mod(rownum,50)) accum_num
4 from dual
5 connect by level <= 1000000
6 );
SUM(ACCUM_NUM)
--------------
416500000
경 과: 00:00:01.89
- 쿼리는 100000번 호출햇지만 하단의 쿼리로 호출횟수가 50번 일어난걸 확인할수있따.(client_info값)
SQL> select sys_context('userenv', 'client_info') from dual
2 ;
SYS_CONTEXT('USERENV','CLIENT_INFO')
50
-- deterministic 제거후테스트
SQL> create or replace function accum(p_input number) return number
2 --deterministic
3 as
4 rvalue number := 0;
5 call_cnt number := 0;
6 call_cnt2 number := 0;
7
8 begin
9 dbms_application_info.read_client_info(call_cnt);
10 if call_cnt is null then
11 call_cnt := 0;
12 end if;
13 --call_cnt := call_cnt+1;
14 dbms_application_info.set_client_info(call_cnt+1);
15
16 for i in 1..p_input loop
17 rvalue := rValue + i;
18 end loop;
19 return rvalue;
20
21 end;
22 /
함수가 생성되었습니다.
경 과: 00:00:00.06
SQL> exec dbms_application_info.set_client_info(null);
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.00
SQL> select sum(accum_num)
2 from (
3 select accum(mod(rownum,50)) accum_num
4 from dual
5 connect by level <= 1000000
6 );
SUM(ACCUM_NUM)
--------------
416500000
경 과: 00:00:10.07
함수를 1000000 호출한걸 확인할수있다.
SQL> select client_info
2 from v$session
3 where sid= sys_context('userenv','sid');
CLIENT_INFO
----------------------------------------------------------------
1000000
deterministic 요함수를 사용을 하므로써, 성능은 조아지지만 일괄성을 보장해주지않는다.
6) 복잡한 함수 로직을 풀어 SQL로 구현
- 사용자(개발자) 입장에서는 난감한 경우다.
- Function을 사용하여 좀더 가독성 및 객체지향? , 쉬운 유지보수가 젤 어떻게보면 맞을것이다.
- 때와 상황에 맞쳐서 사용하는게 좋을듯하다.
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3115
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.