Query Result Cache

EXEM Knowledge Base

Jump to: navigation, 찾기

반복되는 query에 대한 응답속도를 개선하기 위해 Oracle 11g에서는 메모리 영역에 result cache를 이용하는 기능을 선보였다. Result Cache는 shared pool에 Result Cache Memory로 불리는 영역에 SQL 및 PL/SQL funtion의 결과를 저장하는 것이다.

특정 query가 반복적으로 수행될 때 이 결과를 캐시하여 그 다음 부터는 해당 query를 다시 execute하는 것이 아니라 캐시 메모리에 저장된 결과값을 그대로 가지고 오게 된다.이 경우 db부하 뿐만이 아니라 응답속도도 상당히 빨라질 것을 기대 할 수 있다.

이 result cache는 모든 세션들을 위해 공유가 되며 이 저장된 결과는 해당 query가 접근하는 object가 변경될 때 invalid된다.이 result cache 그 자체는 인스턴스 마다 각각 저장되지만 그 사용면에 있어서는 Database 전체를 커버한다.

이 기능을 사용하지 않으려면 RESULT_CACHE_MAX_SIZE 파라메터를 0으로 세팅하고 instance를 재기동 하면 된다. RAC의 경우도 마찬가지..

Result Cache Memory는 SQL query결과를 저장하는 SQL Query Result Cache와 PL/SQL function의 결과 값을 저장하는 PL/SQL Function Result Cache로 구성된다.

목차

[편집] Result Cache와 관련된 파라메터

NAMEVALUEDESCRIPTION
result_cache_mode MANUAL result cache의 모드를 선택 (MANUAL, FALSE)
_result_cache_auto_size_threshold 100 result cache auto max size를 설정
_result_cache_auto_time_threshold 1000 result cache auto time 임계값
_result_cache_auto_execution_threshold 1 result cache auto execution 임계값
result_cache_max_size 1048576 maximum amount of memory to be used by the cache , 만약 이 값이 0이면 이 기능을 사용하지 않게 됨
result_cache_max_result 5 maximum result size as percent of cache size
result_cache_remote_expiration 0 maximum life time (min) for any result using a remote object
_result_cache_block_size 1024 result cache block size
_result_cache_timeout 60 maximum time (sec) a session waits for a result
_result_cache_auto_time_distance 300 result cache auto time distance
client_result_cache_size 0 client result cache max size in bytes, 이 값이 0이면 Client Level의 Result Cache를 사용하지 않는다는 것임
client_result_cache_lag 3000 client result cache maximum lag in milliseconds, 만약 OCI 어플리케이션이 빈번하게 사용하지 않는다면 이 값을 충분히 크게 잡아주어야 한다.그렇지 않으면 database의 result cache와 동기화 하기 위해 빈번하게 round trip이 발생할 것이다.
_client_result_cache_bypass FALSE bypass the client result cache
_xsolapi_sql_result_set_cache_size 32 OLAP API result set cache size

[편집] Result Cache와 관련된 Dictionary View

  • V$RESULT_CACHE_DEPENDENCY : object와 cache된 Result 사이의 dependancy를 나타냄, 앞서 얘기한 바와 같이 해당 SQL이 접근하고 있는 object가 변경될 경우 Result cache가 invalid되기 때문에 관계가 중요함
  • RESULT_CACHE_MEMORY : 메모리 영역의 사용 현황을 알려줌 Result Cache Memory 영역을 chunk별로 나누어 어떤 Result가 어느 메모리 영역에 있는지를 나타내어 주는 뷰
  • V$RESULT_CACHE_OBJECTS : 가장 중요한 뷰로서 현재 Cache된 현황과 이에 대한 수행 정보 및 결과 셋에 대한 정보를 보여 준다.
  • V$RESULT_CACHE_STATISTICS : Result Cache에 대한 통계정보를 보여준다.
  • CLIENT_RESULT_CACHE_STATS$ : Clent Query Result Cache에 대한 통계정보를 보여준다.

[편집] Result Cache를 제어하는 Package

Oracle은 result cache를 제어하기 위해 DBMS_RESULT_CACHE라는 팩키지를 제공한다. 이 팩키지의 서브 프로그램은 다음과 같다,

  • BYPASS Procedure : Result Cache의 bypass모드를 설정한다.
  • FLUSH Function&Procedure : Result Cache의 오브젝트, 인수, 통계정보, result set을 모두 flush한다.
  • INVALIDATE Function&Procedure : Result Cache의 모든 오브젝트와 result set을 invalid한다.
  • INVALIDATE_OBJECT Function&Procedure : Result Cache의 특정 오브젝트와 result set을 invalid한다.
  • MEMORY_RESULT Procedure : Result Cache의 현재 메모리 사용에 대한 보고서를 출력한다.
  • STATUS Function : Result Cache의 상태를 체크한다.

그런데 좀 이상한 것은 이 Status Funtion의 값은 아래의 표와 같이 나온다고 한다.

STATUS_CLSD Cache is not available
STATUS_OPEN Cache is available
STATUS_SYNC Cache is available, but synchronizing with RAC nodes

그러나 실제로 수행해 보면 아래와 같다.

 SQL>select dbms_result_cache.status from dual

 STATUS                                 
 ----------------------------------------
 ENABLED                                

아래는 DBMS_RESULT_CACHE.MEMORY_REPORT를 수행한 결과이다.

SQL> exec DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
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  = 1M bytes (1K blocks)
Maximum Result Size = 51K bytes (51 blocks)
[Memory]
Total Memory = 103528 bytes [0.085% of the Shared Pool]
 
... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
....... Cache Mgr  = 108 bytes
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
 
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.081% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8284 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)

[편집] SQL Query result Cache의 사용

SQL query result Cache의 사용은 RESULT_CACHE_MODE 파라메터에 의해 제어가가능하다. 이 파라메터는 MANUAL과 FORCE라는 값을 가질 수 있다.

MANUAL의 경우 Result Cache를 사용하려면 반드시 특정 SQL 블록에 result_cache 힌트를 사용하여야 한다. FORCE의 경우는 모든 결과를 저장하기 때문에 no_result_cache라는 힌트를 사용하여 이를 회피할 수 있다. 가장 최근에 사용된 알고리즘은 cache result를 age out되게 하기 때문에 만약 result가 가용한 메모리 공간보다 클 경우는 캐시가 되지 않는다.

MANUAL 모드에서 Result Cache를 사용하기 위해서는 다음과 같이 사용하면 된다.

select /*+ result_cache */ deptno, avg(sal)
from emp
group by deptno;

이 경우 아래와 같은 Plan이 나타나게 된다.

SELECT STATEMENT ALL_ROWS-Cost : 4
 RESULT CACHE 
  HASH GROUP BY
   TABLE ACCESS FULL SCOTT.EMP(1)

RESULT CACHE 라는 오퍼레이션이 보이는가. RESULT CACHE 오퍼레이션은 일단 result cache memory를 찾게되고 만약 이 결과가 cache에 있으면 바로 결과값을 가져오고 그렇지 않으면 이것을 수행하여 결과값을 result cache memory에 저장한다.

몇가지 실험을 해보자.

자바 어플리케이션 query_result_cache.class를 통해 Cache를 사용하는 경우와 사용하지 않는 경우의 성능을 비교해 보자.

이 어플리케이션에서 사용한 SQL과 Plan은 다음과 같다.

*CACHE
SELECT emp.deptno , sal , dname , loc
FROM   dept , (SELECT /*+ result_cache */
                      deptno ,AVG( sal ) sal
               FROM   emp
               GROUP  BY deptno ) emp
WHERE  dept.deptno = emp.deptno
-----------------------------------------------------------
 SELECT STATEMENT ALL_ROWS-Cost : 7
  MERGE JOIN 
   TABLE ACCESS BY INDEX ROWID SCOTT.DEPT(1)
    INDEX FULL SCAN SCOTT.PK_DEPT (DEPTNO)
   SORT JOIN  ("DEPT"."DEPTNO"="EMP"."DEPTNO")  ("DEPT"."DEPTNO"="EMP"."DEPTNO")
    VIEW  SCOTT.(2)
     RESULT CACHE 
      HASH GROUP BY
       TABLE ACCESS FULL SCOTT.EMP(3)

* NO CACHE
SELECT emp.deptno , sal , dname , loc
FROM   dept , (SELECT deptno ,AVG( sal ) sal
               FROM   emp
               GROUP  BY deptno ) emp
WHERE  dept.deptno = emp.deptno
-----------------------------------------------------------
SELECT STATEMENT ALL_ROWS-Cost : 7
  MERGE JOIN 
   TABLE ACCESS BY INDEX ROWID SCOTT.DEPT(1)
    INDEX FULL SCAN SCOTT.PK_DEPT (DEPTNO)
   SORT JOIN  ("DEPT"."DEPTNO"="EMP"."DEPTNO")  ("DEPT"."DEPTNO"="EMP"."DEPTNO")
    VIEW  SCOTT.(2)
     HASH GROUP BY
      TABLE ACCESS FULL SCOTT.EMP(3)

이것을 각각 수행해 보니 다음과 같은 차이가 나타났다.

StatNo CacheCache
table scan rows gotten 140072 72
session logical reads 90052 20052
table scan blocks gotten 50003 3
table fetch by rowid 40016 40016
execute count 10009 10009
user calls 10007 10007
CPU used by this session 483 85
Elapsed_Time 10.157 5.313

지표에서 보면 Full Scan의 일량이 현격하게 차이가 나 결국 Logical Read나 수행 시간에서 큰 차이를 나타내는 것을 알 수 있다. Plan에서 보면 Full Scan이 사용되는 부분은 in line view이기 때문에 Result Cache를 사용하면 상당부분 성능을 개선할 수 있다는 것을 증명한다 하겠다.

Client Query Result Cache의 사용 지금까지는 Shared Pool을 이용한 Query Result Cache를 살펴 보았다. 여기에 Client의 메모리영역 특히 OCI의 메모리 공간을 사용한 Query Result Cache도 사용이 가능하다. 이것은 사용하는 방법은 같지만 어디에 Cache가 되는냐의 차이가 발생한다.

그런데 Client에 cache된다고 해서 해당 세션이나 프로세스만 적용되는 메커니즘은 아니다. 이 기능을 사용하면 우선적으로 Shared memory에 cache가 되고 OCI에 이에 대한 복사본을 다시 Cache하게 된다. 그러므로 database영역에 걸쳐 Result Cache가 이루어지게 된다. 이기능을 사용하면 latch의 획득 시도가 감소 하는 등의 Shared memory의 부하를 줄여준다는 잇점을 누릴수 있게 된다.

OCI에 있는 Result Cache는 계속 사용을 하게 되면 계속 유지가 되지만 그렇지 않다면 client_result_cache_lag에 설정된 시간에 한번씩 Shared Memory와 동기화를 하게 된다.

이 기능을 사용하려면 앞서 언급한 바와 같이 CLIENT_RESULT_CACHE_SIZE의 값을 부여하면 된다.

[편집] SQL Query Result Cache의 제약

다음의 경우에는 SQL Query Result Cache의 사용에 제약을 받게 된다.

  • Dictionary나 Temporary 테이블의 경우
  • Currval이나 Nextval과 같이 Sequence 컬럼이 들어가 있는 경우
  • SQL function중 sysdate, current_date, userenv/sys_context(변수로 상수가 아닌경우), local_timestamp, current_timestamp, sys_guid, sys_timestamp등 가변적인 결과값을 사용하는 경우
  • 바인드 변수를 사용한 경우는 변수만 같다면 cache가 가능
  • 서브 쿼리

등등 결과값이 변경될 여지가 있는 경우는 사용이 불가능 하거나 제약을 받게 된다는 것을 명심하자