Transcript Oracle 11g (Result Cache) 슬라이드x
Slide 1
Practical Internals in Oracle 11g
Result Cache
교육/컨텐츠 책임 컨설턴트
[email protected]
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
㈜엑셈
Slide 2
Agenda
Result cache란 ?
Result cache의 구조
Result cache 사용
Parameter
Latch & Event
Invalidation
LRU 알고리즘
실습 – enq : RC
PL/SQL Function Result Cache
Client-Side Result cache
제약사항
요약
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
2
㈜엑셈
Slide 3
Result cache란 ?
• Concept
– 자주 사용되는 쿼리 또는 Function에 대하여 수행결
과를 메모리 상에 저장시켜 놓았다가 이후 수행되는
동일 쿼리 또는 Function에서 결과를 재 사용하기 위
함
– 수행 결과가 저장되는 위치에 따라서 Server-side /
Client-side로 구분
– 데이터가 저장되어 있는 데이터 블록이 아닌 수행결
과 자체를 저장함
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
3
㈜엑셈
Slide 4
Result cache 구조
• Result Cache구조
– 여러 세션에서 공유 해서 사용할 수 있도록 SGA 영역
중 Shared Pool 안에 존재 함
– 데이터를 저장하기 위한 Result cache 블록의 사이즈
는 1024Byte(1K) 이다.
– 데이터를 저장하기 위해서 32K (1K 블록 * 32개) 단위
로 메모리를 할당 받아서 데이터를 저장 시킴
– LRU 알고리즘을 통하여 Result Cache 블록을 관리 함
– Object정보 , 세션 정보, SQL/PLSQL 수행 결과 , Bind
변수 등이 Result cache 블록에 저장 됨
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
4
㈜엑셈
Slide 5
Result cache 구조
• Result Cache구조
oradebug dump heapdump 7
Chunk 285f6b3c
Chunk 285f6d58
Chunk 285f6f74
Chunk 285fefa4
Chunk 28606fd4
ds 2d390e70
285f6f74
285fefa4
sz= 540
sz= 540
sz= 32816
sz= 32816
sz= 32816
sz= 98448
sz= 32816
sz= 32816
recreate "KQR PO
" latch=2D9B719C
recreate "KQR PO
" latch=2D9B719C
freeable "Result Cache" ds=2D390E70
freeable "Result Cache" ds=2D390E70
recreate "Result Cache" latch=00000000
ct=
3
dbms_result_cache.memory_report
v$sgastat
POOL
shared
shared
shared
shared
shared
pool
pool
pool
pool
pool
NAME
Result Cache:
Result Cache
Result Cache:
Result Cache:
Result Cache:
BYTES
2852
98396
Memory Mgr
128
Bloom Fltr
2048
Cache Mgr
112
State Objs
Practical Internals in Oracle 11g
Result Cache Memory Report
[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)
Copyright. 2001-2008 EXEM all rights reserved
5
㈜엑셈
Slide 6
Result cache 구조
• Result Cache구조 - Server-side
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
6
㈜엑셈
Slide 7
Result cache 구조
• Result Cache구조 - Client-side
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
7
㈜엑셈
Slide 8
Result cache 사용
• SQL 실행결과 캐싱
– RESULT_CACHE_MODE 파라메터를 이용하여 캐싱 방
식 지정
– SQL문 실행 결과를 Result cache에 저장하는 방법으
로 오라클 11g에서는 Manual/Force 두 가지 방법을
제공
• Manual : /*+ result_cache */ 힌트를 사용
• Force : 세션레벨이나 시스템 레벨에서 수행되는
모든 쿼리 결과가 캐싱
– Fetch 단계에서 Result cache로 캐싱
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
8
㈜엑셈
Slide 9
Result cache 사용
• SQL 실행결과 캐싱
– Force 모드로 수행 시 캐싱 되기를 원치 않는 쿼리 수
행 시는 /*+ no_result_cache */ 힌트를 사용함
– Result cache에 저장되는 데이터
• 참조 Object
• SQL문 / SCN / Bind변수 / Select절 칼럼개수
• 세션 환경 변수 (dbtimezone , sessiontimezone ,
userenv/sys_conext , uid, user )
• NLS Parameter
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
9
㈜엑셈
Slide 10
Result cache 사용
• SQL 실행결과 캐싱 - Manual
SELECT /*+ result_cache */ department_id , max(salary)
FROM employees
GROUP BY department_id ;
Dependency - employees
Result – 12 rows
TYPE
------------Dependency
Result
STATUS
----------Published
Published
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…
NAME
CREATION_TIMESTAMP
---------------------------------------------- ---------------------------BSTAR.EMPLOYEES
2008-03-13 오후 3:31:27
select /*+ result_cache */ department_id , 2008-03-13 오후 3:31:27
max(salary) from employees group by
department_id
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
BLOCK_COUNT
-----------------1
1
10
㈜엑셈
Slide 11
Result cache 사용
• SQL 실행결과 캐싱 - Force
SELECT department_id , max(salary) FROM employees
GROUP BY department_id ;
Dependency - employees
Result – 12 rows
TYPE
------------Dependency
Result
STATUS
----------Published
Published
NAME
---------------------------------------------BSTAR.EMPLOYEES
select department_id , max(salary) from
employees group by department_id
Practical Internals in Oracle 11g
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…
CREATION_TIMESTAMP
---------------------------2008-03-13 오후 3:41:27
2008-03-13 오후 3:41:27
Copyright. 2001-2008 EXEM all rights reserved
BLOCK_COUNT
-----------------1
1
11
㈜엑셈
Slide 12
Result cache 사용
• Result cache 데이터 공유
– 캐시 되어 있는 SQL문의 대소문자, 공백은 구별하지
않음
– 칼럼개수 , 바인드 변수, 세션환경 변수가 동일하면 데
이터 공유
– 읽기 일관성을 보장하기 위하여 scn을 참조
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
12
㈜엑셈
Slide 13
Parameter
• Result cache 파라메터
– RESULT_CACHE_MODE (SYSTEM/SESSION)
• 수행 결과를 저장시킬 방법 결정
• MANUAL (Default)
/*+ result_cache */ 힌트를 사용 함
힌트 자체의 의미가 쿼리 수행 결과를 Result
cache에 저장 시키라는 뜻임
• FORCE
수행되는 모든 쿼리에 대해서 Result cache에
저장 시킴
/*+ no_result_cache */힌트를 사용하여 불 필
요한 쿼리 결과 배제
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
13
㈜엑셈
Slide 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)
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
14
㈜엑셈
Slide 15
Parameter
• Result cache 파라메터
– RESULT_CACHE_MAX_RESULT (SYSTEM)
• 하나의 SQL 또는 Function 수행 결과로 저장되는
Result set의 최대 크기를 제한
• Fetch 수행 중 이 크기를 초과 하는 Result set은 더
이상 Result cache 블록에 저장되지 않음
• 크기를 초과 하기 전 캐싱 된 데이터는 SQL 또는
Function에서 재 사용되지 못함
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
15
㈜엑셈
Slide 16
Parameter
• Result cache 파라메터
– RESULT_CACHE_REMOTE_EXPIRATION
(SYSTEM/SESSION)
• 분산 환경에서 데이터 베이스 링크 등을 이용하여
가져온 데이터를 캐싱 할지 여부 결정
• 기본값은 0으로 캐싱 하지 않음
• 단위는 분(min)
• 설정 시 해당 시간 동안 Remote object의 데이터
변경이 있어도 적용되지 않음
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
16
㈜엑셈
Slide 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 뷰를 이용하여 조회
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
17
㈜엑셈
Slide 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
경합 발생
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
18
㈜엑셈
Slide 19
Latch & Event
• Result Cache: SO Latch
– 새로운 세션에서 최초로 Result cache에 대한 접근을
시도 할 경우 획득해야 하는 latch
– 최초 사용할 경우 한번 만 획득을 시도 하게 되므로
최초 접속한 여러 세션에서 동시에 result cache에 대
한 접근을 시도 하지 않는 이상 경합 발생 소지 적음
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
19
㈜엑셈
Slide 20
Latch & Event
• enq: RC - Result Cache: Contention
– enq는 enqueue로 관리되는 lock을 뜻하며 lock은 데
이터 자체의 보호를 위하여 획득해야 한다
– RC 이벤트는 캐싱 되어 있는 데이터를 사용하기 위하
여 시도를 하였으나 획득하지 못할 경우 발생
– 데이터를 캐싱중인 세션은 result set에 대하여 lock모
드를 6으로 획득 함
– 캐싱 되어 있는 result set을 사용 하고자 하는 세션은
lock모드를 4로 대기 함
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
20
㈜엑셈
Slide 21
Latch & Event
• _result_cache_timeout (SYSTEM/SESSION)
– 기본값은 60초 임
– 세션이 result set에 대한 자원 요청 후 획득할때 까지
기다리는 시간 설정
– 세션에서 해당 시간 동안 result set에 대한 자원을 획
득하지 못할 경우 result set에 대한 사용을 포기 하고
기존 방식대로 쿼리를 실행 시킴
– 이 값을 0으로 설정 시 result set에 대한 요청 시도후
자원을 획득하지 못할 경우 사용을 포기 하고 기존 방
식대로 쿼리를 실행 시킴
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
21
㈜엑셈
Slide 22
Latch & Event
• 최초 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
Result cache : latch
db file scattered read
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
22
㈜엑셈
Slide 23
Latch & Event
• 캐싱 된 Result Cache 사용
Parse
Execute
Fetch
Result cache : latch
SELECT /*+ result_cache */ ..
FROM table
Result cache lock - Share
Result cache : latch
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
23
㈜엑셈
Slide 24
Latch & Event
• enq: RC - Result Cache: Contention
SELECT /*+ result_cache */ ..
FROM table
SELECT /*+ result_cache */ ..
FROM table
wait
wait
SELECT /*+ result_cache */ ..
FROM table
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
24
㈜엑셈
Slide 25
Invalidation
• Result set invalidation
– 메모리에 캐싱 되어 있는 Result set이 사용 못하는 상
태로 변경되는 것을 뜻함
– 참조 Object의 데이터에 변화가 생긴 경우에 발생 함
– 참조 Object가 invalidation 되면 관련 result set은 모
두 invalid 상태로 변화 하며 LRU 알고리즘에 의해서
사용되었던 블록이 먼저 재 사용됨
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
25
㈜엑셈
Slide 26
LRU 알고리즘
• LRU 알고리즘
– Result cache의 블록을 관리 하기 위한 기법
– 사용 된지 오래된 순서부터 재 사용
– Dependency Object는 재 사용에서 제외
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
26
㈜엑셈
Slide 27
LRU 알고리즘
• LRU 알고리즘
–
–
–
–
Result cache의 블록을 관리 하기 위한 기법
사용 된지 오래된 순서부터 재 사용
Dependency Object는 재 사용에서 제외
Free 블록 찾는 순서
• Result set 중에 invalid 상태인 블록 사용
• Free 블록사용
• Free 블록이 없을 경우 사용 된지 오래된 블록 부
터 사용
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
27
㈜엑셈
Slide 28
LRU 알고리즘
• LRU 알고리즘
RESULT_CACHE_MAX_SIZE=64K
1K * 32 * 2 = 64K
SELECT /*+ result_cache */ ..
FROM table2
table1
4 block needs..
5
Dependency
0 0
Result
LRU
MRU
0 0 0 0 0
1 1 1 1 1
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
28
㈜엑셈
Slide 29
LRU 알고리즘
• LRU 알고리즘 - Invalidation
RESULT_CACHE_MAX_SIZE=64K
1K * 32 * 2 = 64K
UPDATE table1
SET
…..
WHERE …..
Dependency
0 0 0 0 0 0 0 0
Result
LRU
5 46 46 57 68 79 79 810 810 911
03 24 34 34 30
0 0 00 00 00 01 01 11 11 12 1
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
29
MRU
12
10
12
10
㈜엑셈
Slide 30
실습
• enq: RC - Result Cache: Contention 실습
– 실습 시나리오
– 여러 세션에서 동시에 Result cache에 대한 접근 시
도
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
30
㈜엑셈
Slide 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 됨
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
31
㈜엑셈
Slide 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 구문 사용가
능
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
32
㈜엑셈
Slide 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 ;
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
33
㈜엑셈
Slide 34
PL/SQL Function result cache
• 기대효과
– Select 절에 사용되는 펑션일 경우 Result cache를 이
용하여 Function call 줄임
– 최초 수행되는 세션에서만 function call이 존재 하며
나머지 세션은 function call이 존재 하지 않음
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
34
㈜엑셈
Slide 35
실습
• Function result cache VS. normal Function
• Function result cache VS. SQL result cache
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
35
㈜엑셈
Slide 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인 경우
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
36
㈜엑셈
Slide 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
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
37
㈜엑셈
Slide 38
요약
• SQL or PL/SQL result cache는 참조 하는 Object에 데이
터 변경이 없는 경우에 유용함
• 데이터 변경이 있는 테이블 데이터를 Result cache에 캐
싱 할 경우 enq: RC 경합이 발생할 수 있음
• 변경이 심한 테이블 데이터를 캐싱 해야 할 경우
_result_cache_timeout 파라메터 값을 줄여서 사용할 것
• result cache 크기가 클 경우 업무 중
dbms_result_cache.memory_report 사용을 금할 것
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
38
㈜엑셈
Slide 39
Thank You
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
㈜엑셈
Practical Internals in Oracle 11g
Result Cache
교육/컨텐츠 책임 컨설턴트
[email protected]
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
㈜엑셈
Slide 2
Agenda
Result cache란 ?
Result cache의 구조
Result cache 사용
Parameter
Latch & Event
Invalidation
LRU 알고리즘
실습 – enq : RC
PL/SQL Function Result Cache
Client-Side Result cache
제약사항
요약
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
2
㈜엑셈
Slide 3
Result cache란 ?
• Concept
– 자주 사용되는 쿼리 또는 Function에 대하여 수행결
과를 메모리 상에 저장시켜 놓았다가 이후 수행되는
동일 쿼리 또는 Function에서 결과를 재 사용하기 위
함
– 수행 결과가 저장되는 위치에 따라서 Server-side /
Client-side로 구분
– 데이터가 저장되어 있는 데이터 블록이 아닌 수행결
과 자체를 저장함
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
3
㈜엑셈
Slide 4
Result cache 구조
• Result Cache구조
– 여러 세션에서 공유 해서 사용할 수 있도록 SGA 영역
중 Shared Pool 안에 존재 함
– 데이터를 저장하기 위한 Result cache 블록의 사이즈
는 1024Byte(1K) 이다.
– 데이터를 저장하기 위해서 32K (1K 블록 * 32개) 단위
로 메모리를 할당 받아서 데이터를 저장 시킴
– LRU 알고리즘을 통하여 Result Cache 블록을 관리 함
– Object정보 , 세션 정보, SQL/PLSQL 수행 결과 , Bind
변수 등이 Result cache 블록에 저장 됨
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
4
㈜엑셈
Slide 5
Result cache 구조
• Result Cache구조
oradebug dump heapdump 7
Chunk 285f6b3c
Chunk 285f6d58
Chunk 285f6f74
Chunk 285fefa4
Chunk 28606fd4
ds 2d390e70
285f6f74
285fefa4
sz= 540
sz= 540
sz= 32816
sz= 32816
sz= 32816
sz= 98448
sz= 32816
sz= 32816
recreate "KQR PO
" latch=2D9B719C
recreate "KQR PO
" latch=2D9B719C
freeable "Result Cache" ds=2D390E70
freeable "Result Cache" ds=2D390E70
recreate "Result Cache" latch=00000000
ct=
3
dbms_result_cache.memory_report
v$sgastat
POOL
shared
shared
shared
shared
shared
pool
pool
pool
pool
pool
NAME
Result Cache:
Result Cache
Result Cache:
Result Cache:
Result Cache:
BYTES
2852
98396
Memory Mgr
128
Bloom Fltr
2048
Cache Mgr
112
State Objs
Practical Internals in Oracle 11g
Result Cache Memory Report
[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)
Copyright. 2001-2008 EXEM all rights reserved
5
㈜엑셈
Slide 6
Result cache 구조
• Result Cache구조 - Server-side
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
6
㈜엑셈
Slide 7
Result cache 구조
• Result Cache구조 - Client-side
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
7
㈜엑셈
Slide 8
Result cache 사용
• SQL 실행결과 캐싱
– RESULT_CACHE_MODE 파라메터를 이용하여 캐싱 방
식 지정
– SQL문 실행 결과를 Result cache에 저장하는 방법으
로 오라클 11g에서는 Manual/Force 두 가지 방법을
제공
• Manual : /*+ result_cache */ 힌트를 사용
• Force : 세션레벨이나 시스템 레벨에서 수행되는
모든 쿼리 결과가 캐싱
– Fetch 단계에서 Result cache로 캐싱
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
8
㈜엑셈
Slide 9
Result cache 사용
• SQL 실행결과 캐싱
– Force 모드로 수행 시 캐싱 되기를 원치 않는 쿼리 수
행 시는 /*+ no_result_cache */ 힌트를 사용함
– Result cache에 저장되는 데이터
• 참조 Object
• SQL문 / SCN / Bind변수 / Select절 칼럼개수
• 세션 환경 변수 (dbtimezone , sessiontimezone ,
userenv/sys_conext , uid, user )
• NLS Parameter
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
9
㈜엑셈
Slide 10
Result cache 사용
• SQL 실행결과 캐싱 - Manual
SELECT /*+ result_cache */ department_id , max(salary)
FROM employees
GROUP BY department_id ;
Dependency - employees
Result – 12 rows
TYPE
------------Dependency
Result
STATUS
----------Published
Published
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…
NAME
CREATION_TIMESTAMP
---------------------------------------------- ---------------------------BSTAR.EMPLOYEES
2008-03-13 오후 3:31:27
select /*+ result_cache */ department_id , 2008-03-13 오후 3:31:27
max(salary) from employees group by
department_id
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
BLOCK_COUNT
-----------------1
1
10
㈜엑셈
Slide 11
Result cache 사용
• SQL 실행결과 캐싱 - Force
SELECT department_id , max(salary) FROM employees
GROUP BY department_id ;
Dependency - employees
Result – 12 rows
TYPE
------------Dependency
Result
STATUS
----------Published
Published
NAME
---------------------------------------------BSTAR.EMPLOYEES
select department_id , max(salary) from
employees group by department_id
Practical Internals in Oracle 11g
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…
CREATION_TIMESTAMP
---------------------------2008-03-13 오후 3:41:27
2008-03-13 오후 3:41:27
Copyright. 2001-2008 EXEM all rights reserved
BLOCK_COUNT
-----------------1
1
11
㈜엑셈
Slide 12
Result cache 사용
• Result cache 데이터 공유
– 캐시 되어 있는 SQL문의 대소문자, 공백은 구별하지
않음
– 칼럼개수 , 바인드 변수, 세션환경 변수가 동일하면 데
이터 공유
– 읽기 일관성을 보장하기 위하여 scn을 참조
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
12
㈜엑셈
Slide 13
Parameter
• Result cache 파라메터
– RESULT_CACHE_MODE (SYSTEM/SESSION)
• 수행 결과를 저장시킬 방법 결정
• MANUAL (Default)
/*+ result_cache */ 힌트를 사용 함
힌트 자체의 의미가 쿼리 수행 결과를 Result
cache에 저장 시키라는 뜻임
• FORCE
수행되는 모든 쿼리에 대해서 Result cache에
저장 시킴
/*+ no_result_cache */힌트를 사용하여 불 필
요한 쿼리 결과 배제
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
13
㈜엑셈
Slide 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)
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
14
㈜엑셈
Slide 15
Parameter
• Result cache 파라메터
– RESULT_CACHE_MAX_RESULT (SYSTEM)
• 하나의 SQL 또는 Function 수행 결과로 저장되는
Result set의 최대 크기를 제한
• Fetch 수행 중 이 크기를 초과 하는 Result set은 더
이상 Result cache 블록에 저장되지 않음
• 크기를 초과 하기 전 캐싱 된 데이터는 SQL 또는
Function에서 재 사용되지 못함
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
15
㈜엑셈
Slide 16
Parameter
• Result cache 파라메터
– RESULT_CACHE_REMOTE_EXPIRATION
(SYSTEM/SESSION)
• 분산 환경에서 데이터 베이스 링크 등을 이용하여
가져온 데이터를 캐싱 할지 여부 결정
• 기본값은 0으로 캐싱 하지 않음
• 단위는 분(min)
• 설정 시 해당 시간 동안 Remote object의 데이터
변경이 있어도 적용되지 않음
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
16
㈜엑셈
Slide 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 뷰를 이용하여 조회
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
17
㈜엑셈
Slide 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
경합 발생
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
18
㈜엑셈
Slide 19
Latch & Event
• Result Cache: SO Latch
– 새로운 세션에서 최초로 Result cache에 대한 접근을
시도 할 경우 획득해야 하는 latch
– 최초 사용할 경우 한번 만 획득을 시도 하게 되므로
최초 접속한 여러 세션에서 동시에 result cache에 대
한 접근을 시도 하지 않는 이상 경합 발생 소지 적음
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
19
㈜엑셈
Slide 20
Latch & Event
• enq: RC - Result Cache: Contention
– enq는 enqueue로 관리되는 lock을 뜻하며 lock은 데
이터 자체의 보호를 위하여 획득해야 한다
– RC 이벤트는 캐싱 되어 있는 데이터를 사용하기 위하
여 시도를 하였으나 획득하지 못할 경우 발생
– 데이터를 캐싱중인 세션은 result set에 대하여 lock모
드를 6으로 획득 함
– 캐싱 되어 있는 result set을 사용 하고자 하는 세션은
lock모드를 4로 대기 함
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
20
㈜엑셈
Slide 21
Latch & Event
• _result_cache_timeout (SYSTEM/SESSION)
– 기본값은 60초 임
– 세션이 result set에 대한 자원 요청 후 획득할때 까지
기다리는 시간 설정
– 세션에서 해당 시간 동안 result set에 대한 자원을 획
득하지 못할 경우 result set에 대한 사용을 포기 하고
기존 방식대로 쿼리를 실행 시킴
– 이 값을 0으로 설정 시 result set에 대한 요청 시도후
자원을 획득하지 못할 경우 사용을 포기 하고 기존 방
식대로 쿼리를 실행 시킴
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
21
㈜엑셈
Slide 22
Latch & Event
• 최초 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
Result cache : latch
db file scattered read
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
22
㈜엑셈
Slide 23
Latch & Event
• 캐싱 된 Result Cache 사용
Parse
Execute
Fetch
Result cache : latch
SELECT /*+ result_cache */ ..
FROM table
Result cache lock - Share
Result cache : latch
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
23
㈜엑셈
Slide 24
Latch & Event
• enq: RC - Result Cache: Contention
SELECT /*+ result_cache */ ..
FROM table
SELECT /*+ result_cache */ ..
FROM table
wait
wait
SELECT /*+ result_cache */ ..
FROM table
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
24
㈜엑셈
Slide 25
Invalidation
• Result set invalidation
– 메모리에 캐싱 되어 있는 Result set이 사용 못하는 상
태로 변경되는 것을 뜻함
– 참조 Object의 데이터에 변화가 생긴 경우에 발생 함
– 참조 Object가 invalidation 되면 관련 result set은 모
두 invalid 상태로 변화 하며 LRU 알고리즘에 의해서
사용되었던 블록이 먼저 재 사용됨
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
25
㈜엑셈
Slide 26
LRU 알고리즘
• LRU 알고리즘
– Result cache의 블록을 관리 하기 위한 기법
– 사용 된지 오래된 순서부터 재 사용
– Dependency Object는 재 사용에서 제외
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
26
㈜엑셈
Slide 27
LRU 알고리즘
• LRU 알고리즘
–
–
–
–
Result cache의 블록을 관리 하기 위한 기법
사용 된지 오래된 순서부터 재 사용
Dependency Object는 재 사용에서 제외
Free 블록 찾는 순서
• Result set 중에 invalid 상태인 블록 사용
• Free 블록사용
• Free 블록이 없을 경우 사용 된지 오래된 블록 부
터 사용
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
27
㈜엑셈
Slide 28
LRU 알고리즘
• LRU 알고리즘
RESULT_CACHE_MAX_SIZE=64K
1K * 32 * 2 = 64K
SELECT /*+ result_cache */ ..
FROM table2
table1
4 block needs..
5
Dependency
0 0
Result
LRU
MRU
0 0 0 0 0
1 1 1 1 1
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
28
㈜엑셈
Slide 29
LRU 알고리즘
• LRU 알고리즘 - Invalidation
RESULT_CACHE_MAX_SIZE=64K
1K * 32 * 2 = 64K
UPDATE table1
SET
…..
WHERE …..
Dependency
0 0 0 0 0 0 0 0
Result
LRU
5 46 46 57 68 79 79 810 810 911
03 24 34 34 30
0 0 00 00 00 01 01 11 11 12 1
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
29
MRU
12
10
12
10
㈜엑셈
Slide 30
실습
• enq: RC - Result Cache: Contention 실습
– 실습 시나리오
– 여러 세션에서 동시에 Result cache에 대한 접근 시
도
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
30
㈜엑셈
Slide 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 됨
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
31
㈜엑셈
Slide 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 구문 사용가
능
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
32
㈜엑셈
Slide 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 ;
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
33
㈜엑셈
Slide 34
PL/SQL Function result cache
• 기대효과
– Select 절에 사용되는 펑션일 경우 Result cache를 이
용하여 Function call 줄임
– 최초 수행되는 세션에서만 function call이 존재 하며
나머지 세션은 function call이 존재 하지 않음
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
34
㈜엑셈
Slide 35
실습
• Function result cache VS. normal Function
• Function result cache VS. SQL result cache
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
35
㈜엑셈
Slide 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인 경우
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
36
㈜엑셈
Slide 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
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
37
㈜엑셈
Slide 38
요약
• SQL or PL/SQL result cache는 참조 하는 Object에 데이
터 변경이 없는 경우에 유용함
• 데이터 변경이 있는 테이블 데이터를 Result cache에 캐
싱 할 경우 enq: RC 경합이 발생할 수 있음
• 변경이 심한 테이블 데이터를 캐싱 해야 할 경우
_result_cache_timeout 파라메터 값을 줄여서 사용할 것
• result cache 크기가 클 경우 업무 중
dbms_result_cache.memory_report 사용을 금할 것
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
38
㈜엑셈
Slide 39
Thank You
Practical Internals in Oracle 11g
Copyright. 2001-2008 EXEM all rights reserved
㈜엑셈