오라클 성능 고도화 원리와 해법 I (2016년-2)
08.PL/SQL 함수 호출 부하 해소
사용자 정의 함수 사용 방법
- 소량의 데이터 조회시만 사용
- 대용량 처리시 부분범위처리 제한적 사용
- 조인 또는 스칼라 함수로 변환
- 호출 횟수 최소화
h5.(1)페이지 처리 또는 부분범위처리 활용
select *
From (select rownum no, a.*
From (select memb_nm(매수회원번호) 매도 회원명 ,
memb_nm(매수회원번호) 매수회원명 ,
code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명 ,
code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명 ,
code_nm('418' , 체결 유형코드) 체결 유형명 ,
매도계좌번호,
매수계좌번호 ,
체결시각 ,
체결수량 ,
체결가 ,
체결수량 * 체결가 체결금액
from 체결
where 종목코드 = :종목코드
and 체결일자 = :체결일자
and 체결시간 between sysdate-10/21/60 and sysdate
order by 체결시각 desc ) a
where rownum <= 30
)
where no between 21 and 30
)
select memb_nm(매수회원번호) 매도 회원명 ,
memb_nm(매수회원번호) 매수회원명 ,
code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명 ,
code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명 ,
code_nm('418' , 체결 유형코드) 체결 유형명 ,
매도계좌번호,
매수계좌번호 ,
체결시각 ,
체결수량 ,
체결가 ,
체결수량 * 체결가 체결금액
from (select rownum no, a.*
from (select 매도회원번호 ,
매수회원번호 ,
매도투자자구분코드,
매수투자자구분묘드 ,
매도계화변호,
매수계좌번호 ,
체결유형코드 ,
체결시각 ,
체결수량 ,
체결가
from 체결
where 종목코드 = :종목코드
and 체결일자 = :체결일자
and 체결시각 between sysdate - 10/24/60 and sysdate
order by 체결시각 desc ) a
where rownum <= 30
)
where no between 21 and 30
)
- 페이지 처리를 하지 않더라고 부분범위처리가 가능한 상황이라면, 데이터 전송 마지막 단계에서 함수 호출
h5.(2)Decode 함수 또는 Case 문으로 변환
- 함수 로직을 풀어서 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;
RETURN L_분류;
END;
- 함수 사용
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY SF_상품분류(시장코드, 증권그룹코드)
ORDER BY 1 ;
경 과: 00:00:07.31
- CASE 문으로 변경
SELECT CASE
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 = 'SS' THEN '주식 현물'
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물'
WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생'
END 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY
CASE
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 = 'SS' THEN '주식 현물'
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물'
WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생'
END
ORDER BY 1
경 과: 00:00:00.68
- Decode 문으로 변경
SELECT DECODE( 시장코드||증권그룹코드
, 'STSS', '주식 현물'
, 'KQSS', '주식 현물'
, 'SDFU', '파생'
, 'SDOP', '파생'
, 'GDFU', '파생'
, 'GDOP', '파생'
, 'STEF', '파생'
, 'STEW', '파생'
, 'KQEF', '파생'
, 'KQEW', '파생'
, '주식외 현물' ) 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY
DECODE( 시장코드||증권그룹코드
, 'STSS', '주식 현물'
, 'KQSS', '주식 현물'
, 'SDFU', '파생'
, 'SDOP', '파생'
, 'GDFU', '파생'
, 'GDOP', '파생'
, 'STEF', '파생'
, 'STEW', '파생'
, 'KQEF', '파생'
, 'KQEW', '파생'
, '주식외 현물' )
ORDER BY 1
경 과: 00:00:00.87
- Recursive call 발생을 위한 테이블생성, 함수 수정
CREATE TABLE 분류순서(분류명, 순서)
AS
SELECT '주식 현물', 1 FROM DUAL
UNION ALL
SELECT '주식외 현물', 2 FROM DUAL
UNION ALL
SELECT '파생', 3 FROM DUAL ;
CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 IN VARCHAR2, 증권그룹코드 IN VARCHAR2)
RETURN VARCHAR2
IS
L_분류 VARCHAR2(14);
BEGIN
SELECT 순서 || '. ' || L_분류 INTO L_분류
FROM 분류순서
WHERE 분류명 = L_분류;
RETURN L_분류;
END;
- Recursive call 이 100만번 수행
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY SF_상품분류(시장코드, 증권그룹코드)
ORDER BY 1 ;
경 과: 00:00:45.42
- 분류 및 메타정보로써 관리하는 테이블 생성 후 조인
CREATE TABLE 상품분류(시장코드, 증권그룹코드, 분류명)
AS
SELECT 'ST', 'SS', '주식 현물' FROM DUAL UNION ALL
SELECT 'ST', 'EF', '파생' FROM DUAL UNION ALL
SELECT 'ST', 'EW', '파생' FROM DUAL UNION ALL
SELECT 'ST', 'DR', '주식외 현물' FROM DUAL UNION ALL
SELECT 'ST', 'SW', '주식외 현물' FROM DUAL UNION ALL
SELECT 'ST', 'RT', '주식외 현물' FROM DUAL UNION ALL
SELECT 'ST', 'BC', '주식외 현물' FROM DUAL UNION ALL
SELECT 'ST', 'MF', '주식외 현물' FROM DUAL UNION ALL
SELECT 'KQ', 'SS', '주식 현물' FROM DUAL UNION ALL
SELECT 'KQ', 'EF', '파생' FROM DUAL UNION ALL
SELECT 'KQ', 'EW', '파생' FROM DUAL UNION ALL
SELECT 'KQ', 'DR', '주식외 현물' FROM DUAL UNION ALL
SELECT 'KQ', 'SW', '주식외 현물' FROM DUAL UNION ALL
SELECT 'KQ', 'RT', '주식외 현물' FROM DUAL UNION ALL
SELECT 'KQ', 'BC', '주식외 현물' FROM DUAL UNION ALL
SELECT 'KQ', 'MF', '주식외 현물' FROM DUAL UNION ALL
SELECT 'SD', 'FU', '파생' FROM DUAL UNION ALL
SELECT 'SD', 'OP', '파생' FROM DUAL UNION ALL
SELECT 'GD', 'FU', '파생' FROM DUAL UNION ALL
SELECT 'GD', 'OP', '파생' FROM DUAL
;
ALTER TABLE 상품분류
ADD
CONSTRAINT 상품분류_PK PRIMARY KEY(시장코드, 증권그룹코드)
;
- 100건을 그대로 조인하는 것이 아니고(hash join 처리시 빠를수 있음), group by 선행 후 20건에 대해서만 조인하므로 성능이 느리지 않음
SELECT C.순서 || '. ' || B.분류명 상품분류
, SUM(체결건수) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM (SELECT 시장코드, 증권그룹코드
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY 시장코드, 증권그룹코드) A
, 상품분류 B, 분류순서 C
WHERE A.시장코드 = B.시장코드
AND A.증권그룹코드 = B.증권그룹코드
AND C.분류명 = B.분류명
GROUP BY C.순서 || '. ' || B.분류명
ORDER BY 1 ;
경 과: 00:00:00.68
h5.(3)뷰머지(View Merge)방지를 통한 함수 호출 최소화
- 100만건 스캔하면서 건당 SF_상품분류 함수 3번 수행하여 총 300만번 호출
SELECT SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량)) "주식현물_체결수량"
, SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '3. 파생', 체결수량)) "파생_체결수량"
FROM 체결
WHERE 체결일자 = '20090315' ;
경 과: 00:02:13.51
- 함수 호출 1/3 로 감소
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
경 과: 00:02:13.64
속도감소 없음 : 옵티마이저 (Query Transformer) 에 의해 뷰머지(View Merge) 발생 ==> 인라인뷰 이전 상태로 되돌아 감
- 뷰머지 방지
(1) no_merge 힌트
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */ SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
경 과: 00:00:45.34
(2) rownum 사용
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT ROWNUM, SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
경 과: 00:00:45.29
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
AND ROWNUM > 0
) ;
경 과: 00:00:46.50
h5.(4)스칼라 서브쿼리의 캐싱효과를 이용한 함수 호출 최소화
- 스칼라 서브쿼리를 사용하면 오라클은 그 수행횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장
- 스칼라 서브쿼리에 있어 입력값은 거기서 참조하는 메인 쿼리의 컬럼 값
select (
select d.dname /*출력값 d.dname */
from dept d
where d.deptno = e.empnno /*입력값 e.empno */
)
from emp e
- 서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력값을 리턴, 없으면 쿼리 수행 후 캐시에 저장
- 함수 호출 횟수를 줄이는데 사용, 함수를 Dual 을 이용해 스칼라 서브쿼리로 감싸서 캐시
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */
(SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
경 과: 00:00:34.42
- 함수 호출이 725,010 번 발생
SELECT 순서||'.'||:B1
FROM
분류순서 WHERE 분류명 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 725010 11.37 10.39 0 0 0 0
Fetch 725010 17.57 17.63 0 2175030 0 725010
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1450021 28.95 28.03 0 2175030 0 725010
- 해시충돌이 발생하여 기존 캐시 엔트리를 그대로 둔채 스칼라 서브쿼리만 한번 더 수행
- 해시 충돌이 발생한 입력값이 반복적으로 입력되면, 쿼리가 반복 수행
- _query_execution_cache_max_size : 캐시사이즈 조정 (8i, 9i 는 기본 256 개, 해시 충돌 없이 20번만 수행 )
ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */
(SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
경 과: 00:00:00.79
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 20 0.00 0.00 0 0 0 0
Fetch 20 0.00 0.00 6 60 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41 0.00 0.00 6 60 0 20
h5.(5)Deterministic 함수의 캐싱 효과 활용
- Deterministic 키워드 함께 함수 선언하면 , 서브쿼리 없어도 캐싱효과
- 함수 입력값과 출력값은 CGA(Call Global Area) 에 캐싱 : 데이터베이스 Call 내에서만 유효, Fetch Call 이 완료되면 해제
- 스칼라 서브쿼리에서 입력, 출력은 UGA 에 저장되므로 Fetch Call 상관없이 끝까지 유효
create or replace function ACCUM (p_input number) return number
DETERMINISTIC
as
rValue number := 0 ;
call_cnt number := 0;
begin
dbms_application_info.read_client_info(call_cnt);
if call_cnt is null then
call_cnt := 0;
end if;
dbms_application_info.set_client_info(call_cnt + 1); /* 함수내 호출 횟수 확인 , client_info 변경 */
for i in 1..p_input loop
rValue := rValue + i ;
end loop;
return rValue ;
end;
- 1,000,000 번 함수 호출 ( 실제 함수 호출은 50번 (한번의 Fetch Call 내에서 캐시 상태를 유지하며 처리)
select sum(accum_num)
from (
select accum(mod(rownum, 50)) accum_num
from dual
connect by level <= 1000000
) ;
경 과: 00:00:01.54
select sys_context('userenv', 'client_info') from dual;
SYS_CONTEXT('USERENV','CLIENT_INFO')
-----------------------------------------------------------------
50
- Deterministic 제거 (함수 호출 1,000,000 회)
exec dbms_application_info.set_client_info( NULL );
select sum(accum_num)
from (
select accum(mod(rownum, 50)) accum_num
from dual
connect by level <= 1000000
) ;
경 과: 00:00:13.56
select client_info
from v$session
where sid = sys_context('userenv', 'sid');
CLIENT_INFO
-------------------------------------------------------
1000000
- Deterministic 가 있더라고 함수안에 쿼리 문장이 포함하고 있다면 일관성 보장되지 않음( 같은 입력값에 언제라도 다른 출력 값을 낼수 있음)
- 그 함수가 일관성 있는 결과를 리턴함을 선언할 뿐
- 함수가 쿼리문을 포함할 때는 캐싱효과를 위해 함부로 Deterministic 으로 선언해선 안됨
h5.(6)복잡한 함수 로직을 풀어 SQL 구현
"구루비 DB 스터디 모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3186
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.