ORACLE 11g NF RESULT CACHE
from
1. RESULT CACHE
RESULT CACHE 란 말그대로 결과를 캐싱한다는 의미이다. 물론 RESULT 를 그대로
가져다 사용하기 위해서는 제약사항이 따른다.
2. RESULT CACHE 의 종류
<SQL QUERY RESULT CACHE>
- SERVER-SIDE
- CLIENT-SIDE
<PL/SQL FUNCTION RESULT CACHE>
- SERVER-SIDE
SERVER-SIDE 의 경우 SQL 이든 PL/SQL 이든 동일한 메커니즘을 사용한다.
SERVER-SIDE / CLIENT-SIDE 의 차이는 결과가 저장되는 위치의 차이이다.
CLIENT-SIDE 의 경우 CLIENT 의 메모리 공간에 결과를 저장해 사용하는데
이로인해 클라이언트-서버 간의 라운드트립을 줄일 수 있다. 다만 서버측 정보와 정기적으로 동기화 하는 과정이 필요하다.
3. RESULT CACHE
A. PARAMETER
RESULT CACHE MEMORY 는 SHARED POOL 에 속해있다. 사이즈는 아래와 같이
메모리 관련 파라미터의 설정값에 대해 자동으로 이루어진다.
MEMORY_TARGET (사용시) : 0.25%
SGA_TARGET(사용시) : 0.5%
SHARED_POOL_SIZE(사용시) : 1%
자동으로 사용하지 않고 명시적으로 사용하기 위해서는 아래 파라미터를 지정한다.
RESULT_CACHE_MAX_SIZE
값의 범주는 0 (DISABLE) ~ SHARED POOL 크기의 75%
이 파라미터를 DISABLE 시킨 경우 다시 ENABLE 하려면 (값을 설정)
RESULT_CACHE_MAX_RESULT
RESULT CACHE 사이즈를 정하는 파라미터는 아니다. 전체 RESULT CACHE 에서 하나의 RESULT 가 차지할 수 있는 크기를 %로 정한다. 기본값은 5%이다.
B. SQL QUERY RESULT CACHE 의 흐름
메모리 (SHARED POOL) 에 캐싱되고서 에이징아웃 되기까지의 과정이다.
CACHE-IN 은 메모리에 결과를 적재하는걸 말하며 CACHE-HIT 는 적재된 결과를
다시 이용하는 걸 의미한다. 마지막으로 CACHE-OUT 은 적재되어있는 결과를
제거한다.
<CACHE-IN>
적재를 결정하는 파라미터는 RESULT_CACHE_MODE 이다. 사용할 수 있는 값으로
MANUAL,FORCE 가 있다. CACHING 할 수 있는 대상은 제약되어 있다. 일례로 dynamic performance view 에 대해선 캐싱되지 않는다. 그 이유는 이 값이 시시각각 변하는 값이기 때문이다.
/*+ 여기서는 result_cache 의 제약에 대해 따로 언급하지 않겠다 */
MANUAL : RESULT CACHING 하려면 /*+ result_cache */ 힌트를 사용해야 한다.
FORCE : 기본적으로 RESULT CACHING 한다. 예외로 지정하려면
/*+ no_result_cache */ 힌트를 사용한다.
<CACHE-HIT>
SQL 문장과 BIND 변수 NLS 와 같은 환경변수가 모두 일치할 때 RESULT SET 을 재이용한다. 이를 CACHE-HIT 라 한다.
<CACHE-OUT>
CACHING 된 RESULT 는 LRU에 의해 AGING OUT 된다.
C. QUERY RESULT CACHE 의 흐름
SQL QUERY RESULT CACHE 와 동일하게 CACHING 되고 CACHE HIT 되고 AGING OUT 되는 과정을 가진다.
설정방법은 PLSQL 내에 힌트대신에 RESULT_CACHE 절과 RELIES_ON (DEPENDENT OBJECT) 를 명시한다.
4. RESULT CACHE 예제
A. SQL QUERY RESULT CACHE
result_cache 파라미터 조회
SQL> show parameter result_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2080K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 804M
현재 db에서는 memory_target 을 사용하고 있다. 메모리 설정에 따라 디폴트 사이즈가 달라진다고 하였다. 이 파라미터의 경우 0.25% 이다. (804*1024)k * (1/400) 이고
이를 계산하면 2058.24 이란 값이 나오며 db_block_size 가 8192 (8k) 이므로 2080 이란 값이 나온것으로 추정된다.
<dba_objects_copy 를 count (1회,2회 수행)>
hr schema 에 sys.dba_objects 를 dba_objects_copy 를 복사해 111만개까지 불렸다.
이를 카운트하여 elapsed time 을 비교해 보았다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
경 과: 00:00:00.24
SQL> alter system flush buffer_cache;
시스템이 변경되었습니다.
경 과: 00:00:00.14
SQL> select count(*) from hr.dba_objects_copy;
COUNT(*)
----------
1114624
1 개의 행이 선택되었습니다.
경 과: 00:00:08.19
SQL> /
COUNT(*)
----------
1114624
1 개의 행이 선택되었습니다.
경 과: 00:00:06.66
위에서 쿼리를 두번 반복한 것은 hard parsing 이나 블록을 db buffer cache 로 캐싱하는 동작을 고려하기 위해 실행하였다.
<dbms_result_cache 를 flush>
SQL> exec dbms_result_cache.flush;
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.00
<dbms_result_cache 를 report>
SQL> exec dbms_result_cache.flush;
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.42
SQL> exec 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 = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 5140 bytes [0.002% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.06
result cache 에 대한 report 화면이다. block size 는 정해져있는 값으로 변경이
불가능하다. maximum cache size 는 memory_target 의 0.25% 값으로부터 나온 값이며 maximum result size 는 result_cache_max_result 의 % 값에 의한 결과값이다.
<v$result_cache_statistics 를 조회>
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
---------- ---------------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 0
4 Result Size Maximum (Blocks) 104
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 개의 행이 선택되었습니다.
경 과: 00:00:00.04
위에서 create count success 는 cache-in 에 해당하는 값이다. 그 밑 2번째 값 find count 는 cache-hit 에 해당하는 값이다. 이 view 는 dbms_result_cache.flush 에 의해 초기화 될 수 있다.
<dba_objectct_copy 를 count ( result_cache 힌트 사용, 1회,2회 수행)>
SQL> exec dbms_result_cache.flush;
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.37
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
경 과: 00:00:06.14
SQL> alter system flush buffer_cache;
시스템이 변경되었습니다.
경 과: 00:00:03.55
SQL> select /*+ result_cache */ count(*) from hr.dba_objects_copy;
COUNT(*)
----------
1114624
1 개의 행이 선택되었습니다.
경 과: 00:00:08.39
SQL> /
COUNT(*)
----------
1114624
1 개의 행이 선택되었습니다.
경 과: 00:00:00.00
8초 걸리던 쿼리가 0초에 가깝게 나왔다.
<dbms_result_cache 를 report>
SQL> exec 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 = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 103536 bytes [0.039% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.037% 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)
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:01.66
위 결과를 보면 아까와 달리 수정되고 추가된 부분이 있는걸 알 수 있다.
수정된 부분은 Dynamic Memory 값이 0 bytes 에서 변했다는 것이다.
그 밑 부분은 새로이 추가된 부분이다. 그 중 눈여겨 볼 부분은 맨 밑의 세줄
인데 dependencies 는 캐싱할 결과가 사용하는 원본테이블에 대한 정보를 말한다.
이 경우에는 dba_objects_copy 가 이에 해당한다. results 는 말 그대로 결과값을
가지고 있으며 sql 은 수행한 쿼리를 말한다.
<v$result_cache_statistics 를 조회>
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
---------- ---------------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 32
4 Result Size Maximum (Blocks) 104
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 1
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 개의 행이 선택되었습니다.
경 과: 00:00:00.14
위의 ID 5, 7을 확인하면 값이 증가한 걸 알 수 있다. 5번은 CACHE-IN을 의미하며
7번은 CACHE-HIT를 의미한다.
B. PL/SQL FUNCTION RESULT CACHE
<function 생성>
두가지 function 을 생성한다.
CREATE OR REPLACE FUNCTION "SYS"."DEPT_RESULT1" (l_deptno number)
return varchar2
is
l_tmp varchar2(30);
begin
select dname into l_tmp
from scott.dept
where deptno = l_deptno;
return l_tmp;
end;
/
CREATE OR REPLACE FUNCTION "SYS"."DEPT_RESULT2" (l_deptno number)
return varchar2
result_cache relies_on(scott.emp)
is
l_tmp varchar2(30);
begin
select dname into l_tmp
from scott.dept
where deptno = l_deptno;
return l_tmp;
end;
/
dept_result1 은 일반적인 function 이며 dept_result2 는 result_cache 를 사용한
function 이다.
scott.emp 를 조회하는 쿼리를 만들고 dept_result1 을 사용하는 쿼리와 dept_result2
를 사용하는 쿼리로 dept1.sql, dept2.sql 두개를 생성하였다.
<result cache flush>
SQL> @f_rc_flush
PL/SQL 처리가 정상적으로 완료되었습니다.
<result cache 조회>
SQL> @f_rcr
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 = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 5140 bytes [0.003% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 처리가 정상적으로 완료되었습니다.
<v$result_cache_statistics 조회>
SQL> @f_rcs
ID NAME VALUE
---------- -------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 0
4 Result Size Maximum 104
(Blocks)
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
ID NAME VALUE
---------- -------------------- ----------
10 Delete Count Valid 0
10 개의 행이 선택되었습니다.
<function 실행>
set autot trace stat 으로 통계정보만 조회하였다.
-dept1 을 실행
*1회 실행*
Statistics
----------------------------------------------------------
2824 recursive calls
0 db block gets
631 consistent gets
67 physical reads
0 redo size
762 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
62 sorts (memory)
0 sorts (disk)
14 rows processed
*2회 실행*
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
762 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
1회차에는 db 블록을 메모리에 캐싱하는 작업이 있었고 때문에 2회차 실행에서 consistent gets 가 감소한 걸 볼 수 있다. physical reads 의 경우는 타겟 테이블 자체가 크기가 크지 않기에 메모리에 전부 캐싱되어있다는 걸 추측할 수 있다. (0이라는 수치로부터)
*shared_pool, buffer_cache flush
-dept2 를 실행
*1회 실행*
Statistics
----------------------------------------------------------
2837 recursive calls
0 db block gets
611 consistent gets
94 physical reads
0 redo size
762 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
63 sorts (memory)
0 sorts (disk)
14 rows processed
*2회 실행*
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
762 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
위의 stat 을 보면 recursive calls 와 consistent gets 가 크게 줄어들었다.
<result cache 조회>
SQL> @f_rcr
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 = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 103536 bytes [0.056% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.053% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 27 blocks
........... Used Memory = 5 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 3 blocks
................... PLSQL = 3 blocks (3 count)
dependencies 와 result 는 sql query result cache 와 동일하다. 다른점은 위에 표시한
PLSQL 이다. (SQL QUERY RESULT CACHE 에서는 SQL 로 표현된다.)
<v$result_cache_statistics 조회>
SQL> @f_rcs
ID NAME VALUE
---------- -------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 32
4 Result Size Maximum 104
(Blocks)
5 Create Count Success 3
6 Create Count Failure 0
7 Find Count 25
8 Invalidation Count 0
9 Delete Count Invalid 0
ID NAME VALUE
---------- -------------------- ----------
10 Delete Count Valid 0
10 개의 행이 선택되었습니다.
SQL QUERY RESULT CACHE 와 동일하게 CACHE-IN CACHE-HIT 에 대한 결과가 v$result_cache_statistics 에 표현된다.