모 프로젝트에서 개발자들로부터 튜닝 요청받은 쿼리 중에서 가장 많은 패턴중 하나가
아래와 같이 select target 절에 사용자 함수를 사용한 경우임.
사용자 함수를 함부로 사용하지 못하도록 프로젝트 시작시 rule 을 만들어야 하는 게 아닌가 할 정도로 SQL 성능 저하의 주요 원인 중 하나였음.
DB : tibero
1. 유형
사용자 함수를 아래와 같이 select 절에 사용하는 경우임.
select
....
, JISA_NAME( A.JISA_code ) 지사이름
....
from 자금지원이력 A
where A.기간 between sysdate -10/24/60 and sysdate
group by A.region
2. 병목 원인
* 사용자 함수는 recursive call 로 느림
* 최종 결과 건수에 상관없이 조건절에 부합하는 전체 레코드 건수 만큼 함수 호출이 발생함.
3. 튜닝방법
1) 최종 결과 집합에서만 함수 호출이 일어나도록 하는 방법
select
...
, JISA_NAME( jisa_code ) 지사이름
from (
select /*+ no_merge */
A.region
, A.jisa_code
....
from 자금지원이력 A
where A.기간 between sysdate -10/24/60 and sysdate
group by A.region
)
2) decode 함수 또는 case 문으로 변환
함수 로직이 계산식이 주일 경우
3) 함수를 풀어서 inner join 또는 outer join 으로 변경
select
B.JISA_NAME
from 자금지원이력 A, 지사이름 B
where A.jisa_code = B.jisa_code
and A.기간 between sysdate -10/24/60 and sysdate
group by A.region
4) 스칼라 서브쿼리 캐싱 효과를 이용
oracle 은 select 함수명 from dual 구문으로 호출하면 서브쿼리 캐싱 효과를 얻을 수 있음.
tibero 의 경우는 from dual 구문을 사용하지 않아도 plan 상으로는 sub쿼리 caching 표시가 되나 실제도 캐싱되는지는 확실하지 않음.
oracle 과 동일하게 구현되었다면 from dual 구문을 사용하면 성능 효과를 볼 수 있을 듯 하다.
함수의 결과값의 종류가 적을 때 사용가능하다.
from (
select /*+ NO_MERGE */
( select JISA_NAME(A.JISA_code ) from dual ) 지사이름
.....
5) Deterministic 함수로 캐싱 효과
오라클에서 지원함.
함수 생성시 deterministic 구문 명시하면 서브쿼리 캐싱 효과를 낼 수 있음.
함수 생성시
create or replace funciton JISA_NAME( p_jisacode varchar ) return varchar DETERMINISTIC
...
'DBMS > SQL(쿼리튜닝)' 카테고리의 다른 글
| 선택적 검색 조건에 사용할 수 있는 기법 (0) | 2018.05.01 |
|---|
