Practical Internals in Oracle 11g Result Cache

Presentation on theme: "Practical Internals in Oracle 11g Result Cache"— Presentation transcript:

1 Practical Internals in Oracle 11g Result Cache
최 영 준 교육/컨텐츠 책임 컨설턴트 ㈜엑셈

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= recreate "KQR PO " latch=2D9B719C Chunk 285f6d58 sz= recreate "KQR PO " latch=2D9B719C Chunk 285f6f74 sz= freeable "Result Cache" ds=2D390E70 Chunk 285fefa4 sz= freeable "Result Cache" ds=2D390E70 Chunk 28606fd4 sz= recreate "Result Cache" latch= ds 2d390e70 sz= ct= 285f6f74 sz= 285fefa4 sz= 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 = bytes [0.043% of the Shared Pool] ... Fixed Memory = 5140 bytes [0.002% of the Shared Pool] ... Dynamic Memory = bytes [0.041% of the Shared Pool] Overhead = 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 shared pool Result Cache shared pool Result Cache: Memory Mgr shared pool Result Cache: Bloom Fltr shared pool Result Cache: Cache Mgr

6 RESULT_CACHE_MAX_SIZE
Result Cache구조 - Server-side Shared Pool RESULT_CACHE_MAX_SIZE 1K * 32 * 4 = 128 K Default : SHARED_POOL_SIZE 1% SGA_TARGET % MEMORY_TARGET 0.25% ALTER SYSTEM SET … 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 Result cache
SELECT /*+ result_cache */ department_id , max(salary) FROM employees GROUP BY department_id ; Result cache DEPARTMENT_ID MAX(SALARY) 7000 12 rows selected… Dependency - employees Result – 12 rows TYPE STATUS NAME CREATION_TIMESTAMP BLOCK_COUNT Dependency Published BSTAR.EMPLOYEES 오후 3:31: Result Published select /*+ result_cache */ department_id , 오후 3:31:27 1 max(salary) from employees group by department_id

11 Result cache 사용 SQL 실행결과 캐싱 - Force Result cache
ALTER SESSION SET result_cache_mode=force; SELECT department_id , max(salary) FROM employees GROUP BY department_id ; Result cache DEPARTMENT_ID MAX(SALARY) 7000 12 rows selected… Dependency - employees Result – 12 rows TYPE STATUS NAME CREATION_TIMESTAMP BLOCK_COUNT Dependency Published BSTAR.EMPLOYEES 오후 3:41: Result Published select department_id , max(salary) from 오후 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)
캐시 크기가 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
경합이 발생 할 경우 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 wait
SELECT /*+ result_cache */ .. FROM table SELECT /*+ result_cache */ .. FROM table wait wait SELECT /*+ result_cache */ .. FROM table

25 Invalidation Result set invalidation
참조 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 1 1 1 1 1 1 1 1 1
RESULT_CACHE_MAX_SIZE=64K 1K * 32 * 2 = 64K SELECT /*+ result_cache */ .. FROM table2 4 block needs.. SELECT /*+ result_cache */ .. FROM table1 5 block needs.. SELECT /*+ result_cache */ .. FROM table1 5 block needs.. Dependency Result LRU MRU 1 1 1 1 1 1 1 1 1

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

30 실습 enq: RC - Result Cache: Contention 실습 실습 시나리오

31 PL/SQL 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 result_cache relies_on (departments ) is ….. END name_in_rc;

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