close
프로필 배경
프로필 로고

나 혼자 DB 레벨업

ORACLE/SQL Tuning · 2024. 10. 15. fullscreen 넓게보기

사용자함수 ( 함수명, 서브쿼리변환, Deterministic, Result Cache ) 호출 결과 분석

 

[ 목차 ]

1. 목표

2. 테스트 계획

3. 실행 결과 분석

3.1. Buffer 관점

3.2 Fetch 중 데이터 변화 추이 관점

3.2.1. 일반 함수 vs Deterministic vs ( Deterministic + 스칼라 서브쿼리 ) 분석

3.2.2 일반 함수 vs Result Cache vs ( Result Cache + 스칼라 서브쿼리 ) 분석

4. 정리

 

 

 

 

1. 목표

: 사용자 함수를 다양한 경우의 수로 Call 하고, 함수 유형 및 호출 방법에 따른 우선 순위 및 결과 분석을 하고자 함

 

※ 호출 방법 경우의 수

: Result Cache는 미리 Input/Ouput값을 캐싱 한 후에 실행 함

함수 유형에 따른 운영 관리 촛점을 두면 매우 길어지므로 위 목표에 한해 이해하고자 함.

 

1. 전통적인 방식 Call ( 함수명만으로 Call )

2. 서브쿼리 변환

3. Deterministic

4. Result Cache

5. Deterministic + 서브쿼리 변환

6. Result Cache + 서브쿼리 변환

 

 

 

 

 

2. 테스트 계획

: 함수 호출 방법 별로 1회씩, 총 7회 실행 후 결과 분석 한다. ( 함수 호출 안한 경우 1회 포함 )

SELECT /*+ KTH201 */ 
        ORG_COD
      , FN_GET_ORG_NAM( ORG_COD ) AS "조직명(일반함수호출)"
      , ( SELECT FN_GET_ORG_NAM( ORG_COD ) FROM DUAL ) AS "조직명(서브쿼리변환)"
      , FN_GET_ORG_NAM_DETER( ORG_COD ) AS "조직명(Deterministic-함수호출)"
      , FN_GET_ORG_NAM_RC( ORG_COD ) AS "조직명(ResultCache-함수호출)"
      , ( SELECT FN_GET_ORG_NAM_DETER( ORG_COD ) FROM DUAL ) AS "조직명(Deterministic-서브쿼리변환)"
      , ( SELECT FN_GET_ORG_NAM_RC( ORG_COD ) FROM DUAL ) AS "조직명(ResultCache-서브쿼리변환)"  
  FROM TB_EMP_BIG
 WHERE ORG_COD in ('00010','00020','00030' )
   AND ROWNUM <= 1000
 

 

 

 

 

 

3. 실행 결과 분석

 

 

3.1. Buffer 관점

- 순번 1 : 함수 호출 없음 ( 순수 테이블 조회 ) = 88

- 순번 2 : 일반 함수 호출 -> Row 마다 함수가 실행되므로 Buffer Get가 높아진다. = 2088

- 순번 3 : 함수 서브쿼리 변환 호출 -> 성능 개선 = 92 ( ORG_COD 3개 IN절에 기입했으나, 2개만 추출 됨 )

- 순번 4 : Deterministic 함수 -> Fetch Call 마다 새롭게 실행 및 Caching 처리하므로, Buffer Get가 순번 3보다 높다. = 130

- 순번 5 : Result Cache 함수 -> 이미 캐싱된 데이터를 조회함으로써 I/O 없다. ( 미리 데이터 캐싱 한 후 질의 함 ) = 88

- 순번 6 : Deterministic 함수 -> 서브쿼리 변환 -> "서브 쿼리"가 우선한다. > Buffer가 순번 3과 동일 = 92

- 순번 7 : Result Cache 함수 -> 서브쿼리 변환 -> "Result Cache"가 우선 > Buffer가 순번 5와 동일 = 88

 

대표사진 삭제

사진 설명을 입력하세요.

 

 

 

 

3.2. Fetch 중 데이터 변화 추이 관점

 

※ 참고

1. 전통적인 방식 Call ( 함수명만으로 Call )

2. 서브쿼리 변환

3. Deterministic

4. Result Cache

5. Deterministic + 서브쿼리 변환

6. Result Cache + 서브쿼리 변환

 

 

 

3.2.1. 일반 함수 vs Deterministic vs ( Deterministic + 스칼라 서브쿼리 ) 분석

    : 순번 1, 3, 5 비교

 

3.2.1.1. 데이터 변경

: 아래 1번 세션에서 Fetch 중 > 2번 세션으로 update commit; 하여 함수 호출 결과 보기

( 강원센터 -> 후암동 으로 update )

 

3.2.1.2. 2번 세션 Update > Commit 즉시 1번 Session 조회 데이터 확인

- 1번 : 일반함수는 "후암동" 데이터 즉시 출력

- 3번 : Deterministic 함수는 Fetch Call 단위로 함수 수행 및 캐싱처리 함 ( Fetch 단위로 데이터 변경 출력 )

- 5번 : Deterministic 함수 + 서브쿼리 변환 -> 서브쿼리 변환시 종료 될 때까지 실행시점 데이터 유지

< Deterministic 함수를 사용하지 않고, 서브쿼리 변환만으로도 동일하나 우선 순위 테스트상 해 봄 >

 

* Test Sql

-- 1번 세션
SELECT /*+ KTH207 */ 
       ORG_COD
     , FN_GET_ORG_NAM( ORG_COD ) AS "조직명(일반함수호출)"
     , FN_GET_ORG_NAM_DETER( ORG_COD ) AS "조직명(Deterministic-함수호출)"
     , ( SELECT FN_GET_ORG_NAM_DETER( ORG_COD ) FROM DUAL ) AS "조직명(Deterministic-서브쿼리변환)" 
  FROM TB_EMP_BIG
 WHERE ORG_COD in ( '00010','00020','00030' )
 ;
 
-- 2번 세션
UPDATE TB_ORG_CD 
    SET ORG_NAM = '후암동'
   WHERE ORG_COD = '00010';
  COMMIT;
 

일반 함수 : 즉시 변경된 데이터 output 값 반환 / Deterministic : Fetch Call 단위 반환 / 스칼라 서브 쿼리 : 호출 시점 데이터 유지

 

 

 

 

3.2.1.3. 일반 함수 vs Deterministic vs ( Deterministic + 스칼라 서브쿼리 ) 정리

1) Buffer 비교

- 서브쿼리 변환 = 92 = ( Deterministic + 서브쿼리 변환 ) <> 130 Deterministic

- 세션 캐싱 (서브쿼리변환) vs Fetch 캐싱 ( Deterministic )

- Deterministic 보다 우선순위 높은 서브쿼리 변환

 

2) 데이터 변경시 데이터 읽기 일관성 관점

- Deterministic 함수 + 서브쿼리변환 : 호출 시점 데이터 유지

- Deterministic 함수 : Fetch Call 단위로 변화

 

 

 

 

 

 

3.2.2 일반 함수 vs Result Cache vs ( Result Cache + 스칼라 서브쿼리 ) 분석

    :  순번 1, 4, 6 비교 

 

※ 참고

1. 전통적인 방식 Call ( 함수명만으로 Call )

2. 서브쿼리 변환

3. Deterministic

4. Result Cache

5. Deterministic + 서브쿼리 변환

6. Result Cache + 서브쿼리 변환

 

 

3.2.2.1. Result Cache 조회

: v$result_cache_object에 2개 row에 scan_count , Lru_number 컬럼 수치가 1씩 올라감

( in절에 3개 코드를 넣었으나 실제 2개 00010, 00020만 조회 되는 데이터 상황 )

 SELECT 
       ORG_COD
     , ( SELECT FN_GET_ORG_NAM_RC( ORG_COD ) FROM DUAL ) AS "조직명(ResultCache-서브쿼리변환)"
  FROM TB_EMP_BIG
 WHERE ORG_COD in ('00010','00020','00030' ) ;
 

 

 

3.2.2.2 데이터 변경

: 아래 1번 세션에서 Fetch 중에, 2번 세션으로 update commit; 하여 함수 호출 결과 다르게 나오도록 하기.

( 강원센터 -> 후암동으로 update )

 

 

 

 

3.2.2.3. 2번 세션 Update > Commit 즉시 1번 Session 조회 데이터 확인

 

- 1번 : 일반함수는 "후암동" 데이터 즉시 출력

- 4번 : Result Cache 함수 "후암동" 데이터 즉시 출력

- 6번 : Result Cache 함수 + 서브쿼리 변환 -> 서브쿼리 변환시 종료 될 때까지 실행시점 데이터 유지

 

Buffer 관점에서는 "Result Cache"가 "서브 쿼리 변환"보다 우선 했는데,

실제 데이터 변경 관점에서 보면 "서브 쿼리 변환(호출 시점 데이터 유지)"이 우선한것으로 해석 된다.

 

 

※ 참고

[ Results Cache 함수 사용시 캐싱한 데이터가 모두 무효화 되는 경우가 2가지 ]

1) 함수 내용 사용하는 테이블 데이터 DML ( commit 여부 무관 )

2) 소스 변경 후 컴파일

무효화 이후 함수 재실행으로 다시 Result Cache에 등록된다. ( 사용되는 INPUT/OUTPUT 값만 등록됨 )

그러므로 TEST상 UPDATE 했으므로, 기존에 등록 된 캐싱 데이터는 무효화 되어, 함수 실행 하게 된다.

특이하게도 일반함수 호출보다 Result Cache가 더 빠르게 "후암동" 데이터가 나온다.

 

 

 

 

3.2.2.4. 일반 함수 vs Result Cache vs ( Result Cache + 스칼라 서브쿼리 ) 정리

 

1) Buffer 비교

: 서브쿼리 변환 (92) <> Result Cache = 88 = ( Result Cache + 서브쿼리 변환 )

- Result Cache는 캐싱된 input/output을 읽어 성능 상 유리함. I/O 0임

 

2) 데이터 변경시 데이터 읽기 일관성 관점

- 일반함수는 "후암동" 데이터 즉시 출력

- Result Cache : Result Cache 메카니즘에 의해 데이터 변경시 즉시 변경된 데이터 조회됨

- Result Cache + 서브쿼리 변환 -> 서브쿼리 변환시 종료 될 때까지 실행시점 데이터 유지 

-- 1번 세션
 SELECT /*+ KTH207 */ 
       ORG_COD
     , FN_GET_ORG_NAM( ORG_COD ) AS "조직명(일반함수호출)"
     , FN_GET_ORG_NAM_RC( ORG_COD ) AS "조직명(ResultCache-함수호출)"
     , ( SELECT FN_GET_ORG_NAM_RC( ORG_COD ) FROM DUAL ) AS "조직명(ResultCache-서브쿼리변환)"
  FROM TB_EMP_BIG
 WHERE ORG_COD in ('00010','00020','00030' ) ;
  
-- 2번 세션
UPDATE TB_ORG_CD 
    SET ORG_NAM = '후암동'
   WHERE ORG_COD = '00010';
  COMMIT;
 

 

 

 

 

4. 정리

 

순번 1 : 함수 호출 없음 ( 순수 테이블 조회 ) = 88

순번 2 : 일반 함수 호출인 경우, Row 마다 함수가 실행되므로 Buffer Get가 높아진다. = 2088

순번 3 : 함수 서브쿼리 변환함으로써 성능 개선됨. Buffer 4 차이는 실제 Input ORG_COD NDV 값 2 이기 때문. = 92

순번 4 : Deterministic 함수는 Fetch Call 마다 새롭게 실행 및 Caching 처리하므로, Buffer Get가 순번 3보다 높다. = 130

순번 5 : Result Cache 함수는 이미 캐싱된 데이터를 조회함으로써 I/O 없다. ( 미리 데이터 캐싱 함 ) = 88

* 순번 5는 데이터 캐싱 작업이 선행된 후 88 로 유지됨, 데이터 등록하는 SQL은 BUFFER I/O가 다소 높아짐.

순번 6 : Deterministic 함수를 서브쿼리 변환하면 "서브 쿼리"가 우선한다. Buffer가 순번 3과 동일 = 92

순번 7 : Result Cache 함수를 서브쿼리 변환하면 "Result Cache"가 우선한다. Buffer가 순번 5와 동일 = 88

 

 

 

4.1. Buffer 순위

: Result Cache > 함수 서브쿼리 > Deterministic > 함수명 호출

1) 88 Block = 함수호출 없음 = Result Cache = Result Cache + 서브쿼리

- Result Cache에 캐싱된 데이터 조회시 I/O 0 처리됨

2) 92 Block = 함수 서브쿼리 = Deterministic + 서브쿼리

- Input 값별 첫 실행시 I/O 발생

3) 130 Block = Deterministic 함수

4) 2088 Block = 함수명만 호출

 

 

 

4.2. 데이터 변화

: B 세션 Update & Commit -> 조회 중 이던 A 세션 SQL 데이터 변화 확인

1) 함수명 호출 : 즉시 변경된 데이터 출력

2) Result Cache : 즉시 변경된 데이터 출력

3) Deterministic : Fetch 단위로 변경된 데이터 출력 시작됨

4) ( 함수명 / Deterministic / Result Cache ) 서브 쿼리 : 호출 시점 데이터 유지

 

 

 

※ Test 사용한 함수

[ 함수 ]

CREATE OR REPLACE FUNCTION KTH.FN_GET_ORG_NAM ( P_ORG_COD VARCHAR2 )
RETURN VARCHAR2
-- DETERMINISTIC, RESULT_CACHE
IS
   V_DNAME TB_ORG_CD.ORG_NAM%type;
BEGIN
     
   	 SELECT ORG_NAM
       INTO V_DNAME
       FROM TB_ORG_CD A         
      WHERE ORG_COD = P_ORG_COD;

      RETURN V_DNAME;

EXCEPTION 
	WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(sqlerrm);
	  RETURN NULL; 
END;
 

 

 

원글 : https://cafe.naver.com/oraclebank/1110

 

저작자표시 비영리 변경금지

'ORACLE > SQL Tuning' 카테고리의 다른 글

만화로 보는 SQL Wait Event  (2) 2024.11.15
성능 수치 단위  (0) 2024.10.22
LITERAL SQL 찾기 - 심화  (1) 2024.10.01
like '%'|| :1 ||'%' 성능 개선 방법 고민  (2) 2024.10.01
[ OZ Report ] Literal SQL 때려잡기  (2) 2024.09.25
ORACLE/SQL Tuning 관련 글
더 보기

티스토리툴바