8._PLSQL_함수_호출_부하_해소_방안
2012.05.21 12:22
사용자 정의 함수
1. 소량의 데이터 조회시에만 사용
2. 대용량 데이터를 조회할 때는 부분범위처리가 가능한 상황에서 제한적 사용
3. 조인 또는 스칼라 서브 쿼리 형태로 변환 (메모리에 캐싱됨)
4. 함수를 쓰되 호출 횟수를 최소화함
. 페이지 처리 또는 부분범위 처리 활용
. Decode 함수 또는 Case 문으로 변환
. 뷰 머지 방지를 통한 함수 호출 최소화
. 스칼라 서브 쿼리 캐싱 효과를 이용한 함수 호출 최소화
. Deterministic 함수의 캐싱 효과 활용
. 복잡한 함수 로직을 풀어 SQL로 구현
(1) 페이지 처리 또는 부분 범위 처리 활용
|
select rownum no, a.*
--> 체결 테이블에서 종목 코드와 체결 일자를 받아서 memb_mn으로 회원 번호를 받고, code_mn으로 투자자를 받아 뿌림 , 체결 시각으로 30건을 읽어서 그 중에 21에서 30건만 화면으로 보여줌
|
--> 전체 레코드 건수만큼 함수 호출을 일으킴
--> 그 결과 집합을 Sort Area 또는 Temp 테이블 스페이스에 저장
--> 최종 결과 집합 10건만을 사용자에게 전송
>>> order by 와 rownum에 의한 필터 처리 후 사용자에게 전송하는 최종 결과 집합에 대해서만 함수 호출이 일어남
|
select memb_nm(매수회원번호) 매도 회원명 , memb_nm(매수회원번호) 매수회원명 , code_nm('446' , 매도투자자구분코드) 매도투자자구분명 , code_nm('446' , 매수투자자구분코드) 매수투자자구분명 , code_mn('418', 체결유형코드) 체결 유형명 , 체결 시각 , 체결수량 , 체결가 , 체결수량*체결가 체결 금액 from ( select rownum no, a.* from ( select 매수회원번호 , 매수회원번호 , 매도투자자구분코드 , 매수투자자구분코드 , 매도계좌번호 , 매수계좌번호 from 체결 where 종목코드 = : 종목코드 and 체결일자 = : 체결일자 and 체결시간 between sysdate-10/21/60 and sysdate order by 체결시각 desc ) a where rownum <= 30 ) where no between 21 and 30 --> rownum으로 먼저 30건만 읽어서 최종 쿼리에서 함수 호출을 함 |
(2) Decode 함수 또는 Case 문으로 변환
인라인 뷰에서 order by 절에 사용, 전체 결과 집합을 모두 출력, insert .. select 문에서 사용
---> 함수를 로직으로 풀어서 decode, case 문으로 전환하거나 조인문으로 구현
>> 체결 테이블 생성
|
CREATE TABLE 체결(체결일자, 체결번호, 시장코드, 증권그룹코드, 체결수량, 체결금액) NOLOGGING AS SELECT '20090315' , ROWNUM , DECODE(SIGN(ROWNUM-100000), 1, 'ST', 'KQ') -- 유가증권, 코스닥 , DECODE(MOD(ROWNUM, 8), 0, 'SS', 1, 'EF', 2, 'EW' -- 주식, ETF, ELW , 3, 'DR', 4, 'SW', 5, 'RT' -- DR, 신주인수권, 리츠 , 6, 'BC', 7, 'MF') -- 수익증권, 투자회사 , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1) , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2) FROM DUAL CONNECT BY LEVEL <= 500000 UNION ALL SELECT '20090315' , ROWNUM + 300000 , (CASE WHEN MOD(ROWNUM, 4) < 2 THEN 'SD' ELSE 'GD' END) , (CASE WHEN MOD(ROWNUM, 4) IN (0, 2) THEN 'FU' ELSE 'OP' END) , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1) , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2) FROM DUAL CONNECT BY LEVEL <= 500000; --> 주식 가상 테이블 |
>> 업무에 따라서 주식 상품을 다르게 분류하고 집계 하기도함
상품 분류라는 분류 기준 함수를 정의
|
CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2) RETURN VARCHAR2 IS L_분류 VARCHAR2(20); BEGIN IF 시장코드 IN ('ST', 'KQ') THEN -- 유가증권, 코스닥 IF 증권그룹코드 = 'SS' THEN L_분류 := '주식 현물'; ELSIF 증권그룹코드 IN ('EF', 'EW') THEN -- ETF, ELW L_분류 := '파생'; ELSE L_분류 := '주식외 현물'; END IF; ELSE L_분류 := '파생'; END IF;
--SELECT 순서 || '. ' || L_분류 INTO L_분류 --FROM 분류순서 --WHERE 분류명 = L_분류; RETURN L_분류; END; --> 시장 코드에 따라서 분류를 return 하는 함수
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 , COUNT(*) 체결건수 , SUM(체결수량) 체결수량 , SUM(체결금액) 체결금액 FROM 체결 WHERE 체결일자 = '20090315' GROUP BY SF_상품분류(시장코드, 증권그룹코드) ORDER BY 1 ; ---------------------------------------------------------------------------------------------------------------- |
|
SELECT CASE ORDER BY 1 ;
>>> DECODE와 CASE를 이용하면 훨씬 빠르게 수행됨 SELECT DECODE( 시장코드||증권그룹코드
----------------------------------------------------------------------------------------------------------------
|
>> Recursive Call을 포함하도록 테스트
|
EATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2) SELECT 순서 || '. ' || L_분류 INTO L_분류
---------------------------------------------------------------------------------------------------------------- |
>>> 시장코드와 증권그룹코드별 분류명을 메타정보로써 관리하는 테이블을 만들어 사용
|
SELECT C.순서 ||','|| B.분류명 상품분류
|
(3) 뷰 머지 방지를 위한 함수 호출 최소화
. 함수를 풀어 조인문으로 변경하기 곤란한경우, 함수를 그대로 둔채 함수 호출 횟수를 줄이려는 노력이 필요
|
SELECT SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량)) "주식현물_체결수량"
>>>> 아래와 같이 변환했지만 속도가 전혀 줄지 않음
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
------------------------------------------------------------------------------------- |
----> 옵티마이저의 QUERY TRANSFORMER에 의해 뷰 머지가 발생했기 때문
-> 인라인뷰로 묶기 이전 상태로 되돌아감
>> 아래와 같은 방법으로 해결
|
(1) NO_MERGE HINT 사용 SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량" -------------------------------------------------------------------------------------- (2) ROWNUM 사용 SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
---------------------------------------------------------------------------------------
|
(4) 스칼라서브쿼리의 캐싱효과를 이용, 함수호출 최소화
서브쿼리가 수행될 때마다 입력값을 캐시에서찾아보고 거기 있으면 저장된 출력값을 리턴 함
, 없다면 쿼리를 수행한 후 입력값과 출력 값을 캐시에 저장
|
SELECT SUM(DECODE(상품분류, '1. 주식 현물', 체결수량)) "주식현물_체결수량"
>> 함수 호출을 20번으로 예상했지만 많이 수행됨 --> 해시 충돌이 발생 >> 기존 엔트리를 그대로 둔채 스칼라 서브쿼리만 한번더 수행 >> 스칼라 서브 쿼리를 사용하기 이전 쿼리가 반복 수행됨
. 캐쉬 사이즈 조절 ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;
-> 아래와 같은 개선 효과 가능
경 과: 00:00:00.79
>> 1번에 나온것을 아래와 같이 변환 가능 |
(5) Deterministic 함수의 캐싱효과활용
-> Deterministic 키워드를 넣어주면 스칼라 서브쿼리를 덧입히지 않아도 캐싱 효과가 나타남
->함수의 입출력 값은 CGA(Call Global Area)에 캐싱, 할당된 값은 데이터베이스 CALL 내에서만 유효하므로 Fetch Call이 완료 되면 모두 해제
-> 스칼라 서브쿼리에서의 입출력 값은 UGA(Call Global Area)에 캐싱, Fetch Call에 상관없이 그 효과가 캐싱되는 순간부터 끝까지 유지
|
create or replace function ACCUM (p_input number) return number
select sum(accum_num) from ( select accum(mod(rownum, 50)) accum_num from dual connect by level <= 100000 ) ; select sys_context('userenv', 'client_info') from dual;
create or replace function ACCUM (p_input number) return number select sum(accum_num) from ( select accum(mod(rownum, 50)) accum_num from dual connect by level <= 100000 ) ; select sys_context('userenv', 'client_info') from dual;
100000 |
>> 시점과는 무관하게 항상 일관성있는 결과를 출력하므로 캐싱효괄르 위한 Deterministic 함수의 올바른 활용 사례지만
함수가 쿼리문을 포함할때는 선언해선 안됨, 일관성 측면에서 뜻하지 않은 결과 초래 --.current 모드로 읽기 수행
SQL> create or replace function ACCUM (p_input number) return number
2 DETERMINISTIC
3 as
4 rValue number := 0 ;
5 call_cnt number := 0;
begin
dbms_application_info.read_client_info(call_cnt);
6 7 8 if call_cnt is null then
9 call_cnt := 0;
end if;
10 11
12 dbms_application_info.set_client_info(call_cnt + 1);
13
for i in 1..p_input loop
14 15 rValue := rValue + i ;
16 end loop;
17 return rValue ;
18 end;
19 /
Function created.
SQL> select sum(accum_num) from ( select accum(mod(rownum, 50)) accum_num from dual connect by level <= 100000 ) ;
SUM(ACCUM_NUM)
--------------
41650000
SQL> select sys_context('userenv', 'client_info') from dual;
SYS_CONTEXT('USERENV','CLIENT_INFO')
--------------------------------------------------------------------------------
50
SQL>
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 66 | 부록 | 남송휘 | 2012.06.05 | 2710 |
| 65 | 8._IO_효율화_원리 | 운영자 | 2012.06.05 | 4428 |
| 64 |
3._Deterministic_함수_사용_시_주의사항
 | 정찬호 | 2012.05.29 | 4353 |
| 63 | 2._Cursor_Sharing | 운영자 | 2012.05.28 | 6278 |
| 62 | 7._Result_캐시 | 운영자 | 2012.05.27 | 4428 |
| 61 |
3._Single_Block_vs._Multiblock_IO
| 정찬호 | 2012.05.22 | 4231 |
| 60 |
2._Memory_vs._Disk_IO
| 정찬호 | 2012.05.22 | 4448 |
| 59 |
1._블록_단위_IO
| 정찬호 | 2012.05.22 | 4213 |
| 58 |
6장._IO_효율화_원리
| 정찬호 | 2012.05.22 | 4087 |
| 57 |
1._Library_Cache_Lock_Pin
| 남송휘 | 2012.05.21 | 4293 |
| 56 |
6._RAC_캐시_퓨전
| 남송휘 | 2012.05.21 | 17799 |
| 55 | 5._Direct_Path_IO | 남송휘 | 2012.05.21 | 7934 |
| 54 |
4._Prefetch
| 남송휘 | 2012.05.21 | 3891 |
| » | 8._PLSQL_함수_호출_부하_해소_방안 | 남송휘 | 2012.05.21 | 3775 |
| 52 | 5._Fetch_Call_최소화 [1] | 박영창 | 2012.05.15 | 6067 |
| 51 |
7._PLSQL_함수의_특징과_성능_부하
| 남송휘 | 2012.05.14 | 6937 |
| 50 | 6._페이지_처리의_중요성 | 남송휘 | 2012.05.14 | 3381 |
| 49 | 4._Array_Processing_활용 | 시와처 | 2012.05.13 | 4045 |
| 48 |
3._데이터베이스_Call이_성능에_미치는_영향
| 시와처 | 2012.05.13 | 3647 |
| 47 |
10._Dynamic_SQL_사용_기준
| 남송휘 | 2012.05.07 | 4558 |