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

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

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

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

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

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 0.5% MEMORY_TARGET 0.25% ALTER SYSTEM SET … Result cache

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

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

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

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) ------------------ ---------------- 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

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) ------------------ ---------------- 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

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

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

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

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

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

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 뷰를 이용하여 조회

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 경합 발생

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

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

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

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

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

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

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

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

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

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

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

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

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 됨

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 구문 사용가능

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;

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

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

제약사항 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인 경우

관련 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

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

Thank You