Practical Internals in Oracle 11 g Result Cache

1. Practical Internals in Oracle 11gResult Cache 최 영 준 교육/컨텐츠 책임 컨설턴트 bstar@ex-em.com ㈜엑셈

2. Agenda • Result cache란 ? • Result cache의 구조 • Result cache 사용 • Parameter • Latch & Event • Invalidation • LRU 알고리즘 • 실습 – enq : RC • PL/SQL Function Result Cache • Client-Side Result cache • 제약사항 • 요약

3. Result cache란 ? • Concept • 자주 사용되는 쿼리 또는 Function에 대하여 수행결과를 메모리 상에 저장시켜 놓았다가 이후 수행되는동일 쿼리 또는 Function에서 결과를 재 사용하기 위함 • 수행 결과가 저장되는 위치에 따라서 Server-side / Client-side로 구분 • 데이터가 저장되어 있는 데이터 블록이 아닌 수행결과 자체를 저장함

4. Result cache 구조 • Result Cache구조 • 여러 세션에서 공유 해서 사용할 수 있도록 SGA 영역 중 Shared Pool 안에 존재 함 • 데이터를 저장하기 위한 Result cache 블록의 사이즈는 1024Byte(1K) 이다. • 데이터를 저장하기 위해서 32K (1K 블록 * 32개) 단위로 메모리를 할당 받아서 데이터를 저장 시킴 • LRU 알고리즘을 통하여 Result Cache 블록을 관리 함 • Object정보 , 세션 정보, SQL/PLSQL 수행 결과 , Bind 변수 등이 Result cache 블록에 저장 됨

5. Result cache 구조 • Result Cache구조 oradebug dump heapdump 7 Chunk 285f6b3c sz= 540 recreate "KQR PO " latch=2D9B719C Chunk 285f6d58 sz= 540 recreate "KQR PO " latch=2D9B719C Chunk 285f6f74 sz= 32816 freeable "Result Cache" ds=2D390E70 Chunk 285fefa4 sz= 32816 freeable "Result Cache" ds=2D390E70 Chunk 28606fd4 sz= 32816 recreate "Result Cache" latch=00000000 ds 2d390e70 sz= 98448 ct= 3 285f6f74 sz= 32816 285fefa4 sz= 32816 dbms_result_cache.memory_report R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 64K bytes (64 blocks) Maximum Result Size = 64K bytes (64 blocks) [Memory] Total Memory = 103536 bytes [0.043% of the Shared Pool] ... Fixed Memory = 5140 bytes [0.002% of the Shared Pool] ... Dynamic Memory = 98396 bytes [0.041% of the Shared Pool] ....... Overhead = 65628 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 30 blocks ........... Used Memory = 2 blocks ............... Dependencies = 1 blocks (1 count) ............... Results = 1 blocks ................... SQL = 1 blocks (1 count) v$sgastat POOL NAME BYTES shared pool Result Cache: State Objs 2852 shared pool Result Cache 98396 shared pool Result Cache: Memory Mgr 128 shared pool Result Cache: Bloom Fltr 2048 shared pool Result Cache: Cache Mgr 112

6. Result cache 구조 • Result Cache구조 - Server-side Shared Pool Default : SHARED_POOL_SIZE 1% SGA_TARGET 0.5% MEMORY_TARGET 0.25% ALTER SYSTEM SET … RESULT_CACHE_MAX_SIZE 1K * 32 * 4 = 128 K Result cache

7. Result cache 구조 • Result Cache구조 - Client-side Shared Pool sqlnet.ora(optional) OCI_RESULT_CACHE_MAX_SIZE OCI_RESULT_CACHE_MAX_RSET_SIZE OCI_RESULT_CACHE_MAX_RSET_ROWS CLIENT_RESULT_CACHE_SIZE CLIENT_RESULT_CACHE_LAG Result cache Result sets OCI8 driver

8. Result cache 사용 • SQL 실행결과 캐싱 • RESULT_CACHE_MODE 파라메터를 이용하여 캐싱 방식 지정 • SQL문 실행 결과를 Result cache에 저장하는 방법으로 오라클 11g에서는 Manual/Force 두 가지 방법을 제공 • Manual : /*+ result_cache */ 힌트를 사용 • Force : 세션레벨이나 시스템 레벨에서 수행되는 모든 쿼리 결과가 캐싱 • Fetch 단계에서 Result cache로 캐싱

9. Result cache 사용 • SQL 실행결과 캐싱 • Force 모드로 수행 시 캐싱 되기를 원치 않는 쿼리 수행 시는 /*+ no_result_cache */ 힌트를 사용함 • Result cache에 저장되는 데이터 • 참조 Object • SQL문 / SCN / Bind변수 / Select절 칼럼개수 • 세션 환경 변수 (dbtimezone , sessiontimezone , userenv/sys_conext , uid, user ) • NLS Parameter

10. Result cache 사용 • SQL 실행결과 캐싱 - Manual SELECT /*+ result_cache */ department_id , max(salary) FROM employees GROUP BY department_id ; Result cache DEPARTMENT_ID MAX(SALARY) ------------------ ---------------- 100 12000 30 11000 7000 20 13000 70 10000 90 24000 110 12000 50 8200 40 6500 80 14000 10 4400 60 9000 12 rows selected… Dependency - employees Result – 12 rows TYPE STATUS NAME CREATION_TIMESTAMP BLOCK_COUNT ------------- ----------- ---------------------------------------------- ---------------------------- ------------------ Dependency Published BSTAR.EMPLOYEES 2008-03-13 오후 3:31:27 1 Result Published select /*+ result_cache */ department_id , 2008-03-13 오후 3:31:27 1 max(salary) from employees group by department_id

11. Result cache 사용 • SQL 실행결과 캐싱 - Force ALTER SESSION SET result_cache_mode=force; SELECT department_id , max(salary) FROM employees GROUP BY department_id ; Result cache DEPARTMENT_ID MAX(SALARY) ------------------ ---------------- 100 12000 30 11000 7000 20 13000 70 10000 90 24000 110 12000 50 8200 40 6500 80 14000 10 4400 60 9000 12 rows selected… Dependency - employees Result – 12 rows TYPE STATUS NAME CREATION_TIMESTAMP BLOCK_COUNT ------------- ----------- ---------------------------------------------- ---------------------------- ------------------ Dependency Published BSTAR.EMPLOYEES 2008-03-13 오후 3:41:27 1 Result Published select department_id , max(salary) from 2008-03-13 오후 3:41:27 1 employees group by department_id

12. Result cache 사용 • Result cache 데이터 공유 • 캐시 되어 있는 SQL문의 대소문자, 공백은 구별하지 않음 • 칼럼개수 , 바인드 변수, 세션환경 변수가 동일하면 데이터 공유 • 읽기 일관성을 보장하기 위하여 scn을 참조

13. Parameter • Result cache 파라메터 • RESULT_CACHE_MODE (SYSTEM/SESSION) • 수행 결과를 저장시킬 방법 결정 • MANUAL (Default) • /*+ result_cache */ 힌트를 사용 함 • 힌트 자체의 의미가 쿼리 수행 결과를 Result cache에 저장 시키라는 뜻임 • FORCE • 수행되는 모든 쿼리에 대해서 Result cache에 저장 시킴 • /*+ no_result_cache */힌트를 사용하여 불 필요한 쿼리 결과 배제

14. Parameter • Result cache 파라메터 • RESULT_CACHE_MAX_SIZE (SYSTEM) • Result cache 영역의 크기 지정 • 캐시 크기가 32K단위로 할당 되므로 이 값을 무시할 경우 비슷한 크기로 오라클이 변경 함 • 0 을 사용시 Result cache 사용 안 함 • 동적으로 메모리 크기를 줄일 경우 현재 로드 되어 있는 데이터를 다 담을 수 없으면 전체가 age-out됨 • 이 크기가 너무 작고 참조 하는 Object가 많을 경우 Result Cache에 dependency 정보만 남을 수 있으므로 주의 • 이 크기가 너무 클 경우 Result cache 영역 조회 시 overhead 발생 가능(latch wait)

15. Parameter • Result cache 파라메터 • RESULT_CACHE_MAX_RESULT (SYSTEM) • 하나의 SQL 또는 Function 수행 결과로 저장되는 Result set의 최대 크기를 제한 • Fetch 수행 중 이 크기를 초과 하는 Result set은 더 이상 Result cache 블록에 저장되지 않음 • 크기를 초과 하기 전 캐싱 된 데이터는 SQL 또는 Function에서 재 사용되지 못함

16. Parameter • Result cache 파라메터 • RESULT_CACHE_REMOTE_EXPIRATION (SYSTEM/SESSION) • 분산 환경에서 데이터 베이스 링크 등을 이용하여 가져온 데이터를 캐싱 할지 여부 결정 • 기본값은 0으로 캐싱 하지 않음 • 단위는 분(min) • 설정 시 해당 시간 동안 Remote object의 데이터 변경이 있어도 적용되지 않음

17. Latch & Event • Latch란 ? • 오라클에서 메모리 구조체를 보호하기 위하여 사용하는 일종의 메모리 Lock • SGA영역의 데이터를 사용하기 위해서는 latch를 획득 하여야 함 • Oracle 11g에서 Result Cache관련 자원경합을 관리 하기 위하여 다음과 같은 두 개의 latch가 추가 됨 • Result Cache: Latch • Result Cache: SO Latch • 두 개의 latch는 child latch가 존재 하지 않으며 v$latch, v$latch_parent 뷰를 이용하여 조회

18. Latch & Event • Result Cache: Latch • Result cache 영역을 탐색 하기 위하여 획득해야 하는 latch • 경합이 발생 할 경우 latch free 이벤트로 대기 함 • P1값을 사용하여 v$latch.addr 을 조회 하면 확인 가능 • Result Cache 관련 View 조회 시에도 latch 획득 필요 • Result cache 영역이 클(Size) 경우 dbms_result_cache.memory_report 패키지를 사용하거나 v$result_cache_memory뷰를 조회 할 경우 Latch 경합 발생

19. Latch & Event • Result Cache: SO Latch • 새로운 세션에서 최초로 Result cache에 대한 접근을 시도 할 경우 획득해야 하는 latch • 최초 사용할 경우 한번 만 획득을 시도 하게 되므로 최초 접속한 여러 세션에서 동시에 result cache에 대한 접근을 시도 하지 않는 이상 경합 발생 소지 적음

20. Latch & Event • enq: RC - Result Cache: Contention • enq는 enqueue로 관리되는 lock을 뜻하며 lock은 데이터 자체의 보호를 위하여 획득해야 한다 • RC 이벤트는 캐싱 되어 있는 데이터를 사용하기 위하여 시도를 하였으나 획득하지 못할 경우 발생 • 데이터를 캐싱중인 세션은 result set에 대하여 lock모드를 6으로 획득 함 • 캐싱 되어 있는 result set을 사용 하고자 하는 세션은 lock모드를 4로 대기 함

21. Latch & Event • _result_cache_timeout (SYSTEM/SESSION) • 기본값은 60초 임 • 세션이 result set에 대한 자원 요청 후 획득할때 까지 기다리는 시간 설정 • 세션에서 해당 시간 동안 result set에 대한 자원을 획득하지 못할 경우 result set에 대한 사용을 포기 하고 기존 방식대로 쿼리를 실행 시킴 • 이 값을 0으로 설정 시 result set에 대한 요청 시도후 자원을 획득하지 못할 경우 사용을 포기 하고 기존 방식대로 쿼리를 실행 시킴

22. Latch & Event • 최초 Result Cache 등록 Result cache Parse Execute Fetch Result cache : SO latch SELECT /*+ result_cache */ .. FROM table Result cache : latch Result cache lock - Exclusive db file sequential read db file scattered read Result cache : latch

23. Latch & Event • 캐싱 된 Result Cache 사용 Result cache Parse Execute Fetch Result cache : latch SELECT /*+ result_cache */ .. FROM table Result cache lock - Share Result cache : latch

24. Latch & Event • enq: RC - Result Cache: Contention Result cache SELECT /*+ result_cache */ .. FROM table SELECT /*+ result_cache */ .. FROM table wait wait SELECT /*+ result_cache */ .. FROM table

25. Invalidation • Result set invalidation • 메모리에 캐싱 되어 있는 Result set이 사용 못하는 상태로 변경되는 것을 뜻함 • 참조 Object의 데이터에 변화가 생긴 경우에 발생 함 • 참조 Object가 invalidation 되면 관련 result set은 모두 invalid 상태로 변화 하며 LRU 알고리즘에 의해서 사용되었던 블록이 먼저 재 사용됨

26. LRU 알고리즘 • LRU 알고리즘 • Result cache의 블록을 관리 하기 위한 기법 • 사용 된지 오래된 순서부터 재 사용 • Dependency Object는 재 사용에서 제외

27. LRU 알고리즘 • LRU 알고리즘 • Result cache의 블록을 관리 하기 위한 기법 • 사용 된지 오래된 순서부터 재 사용 • Dependency Object는 재 사용에서 제외 • Free 블록 찾는 순서 • Result set 중에 invalid 상태인 블록 사용 • Free 블록사용 • Free 블록이 없을 경우 사용 된지 오래된 블록 부터 사용

28. LRU 알고리즘 • LRU 알고리즘 Result cache RESULT_CACHE_MAX_SIZE=64K 1K * 32 * 2 = 64K SELECT /*+ result_cache */ .. FROM table1 5 block needs.. SELECT /*+ result_cache */ .. FROM table1 5 block needs.. SELECT /*+ result_cache */ .. FROM table2 4 block needs.. Dependency 0 0 Result LRU MRU 0 0 0 0 0 1 1 1 1 0 0 0 0 1 1 1 1 1

29. LRU 알고리즘 • LRU 알고리즘 - Invalidation Result cache RESULT_CACHE_MAX_SIZE=64K 1K * 32 * 2 = 64K UPDATE table1 SET ….. WHERE ….. Dependency 0 0 0 0 0 0 0 0 Result LRU MRU 0 0 0 0 0 1 1 1 1 2 3 4 4 4 5 6 6 7 8 9 9 10 10 11 12 12 1 0 0 0 0 0 0 0 0 1 1 1 2 3 3 3 4 4 5 6 7 7 8 8 9 10 10 0

30. 실습 • enq: RC - Result Cache: Contention 실습 • 실습 시나리오 • 여러 세션에서 동시에 Result cache에 대한 접근 시도

31. PL/SQL Function result cache • PL/SQL Function result cache • Function의 수행 결과를 Result cache에 저장 • RESULT_CACHE 구문과 RELIES_ON 구문을 펑션 생성시 사용 • CREATE OR REPLACE FUNCTION function_name RETURN RESULT_CACHE RELIES_ON (object)… • RELIES_ON 절이 없으면 펑션이 재 생성되거나 compile 될 때만 Result cache 안의 데이터가 invalid됨 • RELIES_ON 절에 나열된 테이블의 데이터에 변경이 생기게 되면 Result cache 안의 데이터도 invalid 됨

32. PL/SQL Function result cache • Using PACKAGE • Package Spec 부분에서는 relies_on절 사용 못함 • PLS-00999: 구현 제한 사항(일시적일 수 있음) RELIES_ON clause is disallowed on function declaration • Spec 부분에서 result_cache 구문을 지정해 주어야 Body부분에서 result_cache & relies_on구문 사용가능

33. PL/SQL Function result cache • Using PACKAGE create or replace package pkg_dept_name as function name_in_rc ( p_id in number ) return departments.department_name%type result_cache ; end pkg_dept_name ; create or replace package body pkg_dept_name as function name_in_rc ( p_id in number ) return departments.department_name%type result_cache relies_on(departments ) is ….. END name_in_rc; end pkg_dept_name ;

34. PL/SQL Function result cache • 기대효과 • Select 절에 사용되는 펑션일 경우 Result cache를 이용하여 Function call 줄임 • 최초 수행되는 세션에서만 function call이 존재 하며 나머지 세션은 function call이 존재 하지 않음

35. 실습 • Function result cache VS. normal Function • Function result cache VS. SQL result cache

36. 제약사항 • SQL Result Cache • Dictionary, Temporary Table을 사용한 쿼리 • 시퀀스의 CURRVAL , NEXTVAL을 포함한 쿼리 • SQL functions current_date, current_timestamp, local_timestamp, userenv/sys_context (with non-constant variables), sys_guid, sysdate, and sys_timestamp를 포함한 쿼리 • PL/SQL Function Result Cache • IN OUT , OUT 타입의 파라메터를 가진 경우 • In Parameter가 BLOB,CLOB,NCLOB,REF Cursor, Collection,Record타입인 경우 • 리턴값이BLOB, CLOB ,NCLOB, REF Cursor, Object타입을 포함하는 Record 또는 Collection인 경우

37. 관련 View • V$RESULT_CACHE_DEPENDENCY • V$RESULT_CACHE_MEMORY • V$RESULT_CACHE_OBJECTS • V$CLIENT_RESULT_CACHE_STATS • CLIENT_RESULT_CACHE_STATS$ • DBMS_RESULT_CACHE Package

38. 요약 • SQL or PL/SQL result cache는 참조 하는 Object에 데이터 변경이 없는 경우에 유용함 • 데이터 변경이 있는 테이블 데이터를 Result cache에 캐싱 할 경우 enq: RC 경합이 발생할 수 있음 • 변경이 심한 테이블 데이터를 캐싱 해야 할 경우 _result_cache_timeout 파라메터 값을 줄여서 사용할 것 • result cache 크기가 클 경우 업무 중 dbms_result_cache.memory_report 사용을 금할 것

39. Thank You