엑시엄이 보는 DB 세상
Result Cache는 과연 독일까? 1 0 2,252

by axiom Result Cache RESULT_CACHE DBMS_RESULT_CACHE [2015.10.13]


한 고객사에서 DBA로 운영을 하던 중, DB 서버의 CPU가 100% 상태를 지속적으로 유지하면서 DB가 Hang 상태에 빠지는 일이 발생하였다.

확인 결과, Result Cache의 무분별한 사용으로 DB서버 CPU를 모두 점유해 버리는 말도 안 되는 상황이 벌어진 것이다. 11g에서 새롭게 도입된 Result Cache라는 기능은 분명 활용 용도에 따라 약이 될 수도 있고 독이 될 수도 있는 독이 든 성배라고 볼 수 있다. 우선 Result Cache 기능에 대해 알아보도록 하자.

Result Cache

Result Cache는 11g에서 새롭게 추가된 기능으로 반복되는 SQL에 대한 응답속도를 개선하기 위해 SQL의 결과를 메모리 내에 캐시(Cache)할 수 있는 기능이다.

SQL이 반복적으로 수행될 때 해당 결과를 캐싱하여 이후로는 실질적으로 실행하는 것이 아닌 캐시 메모리에 저장된 결과 값을 그대로 가지고 오게 된다. SQL을 저장하는 캐시 영역은 공유된 pool에 저장하며 기본적으로 할당되는 사이즈는 아래와 같다.

기본 사이즈
  • - MEMORY_TARGET 사용 시 : 0.25%
  • - SGA_TARGET 사용 시 : 0.5%
  • - SHARED_POOL_SIZE 사용 시 : 1%

최대 사이즈
  • - 최소 값 : 0(Disable)
  • - 최대 값 : SHARED POOL의 75%

위 기본 할당 사이즈를 보았을 때, 유추할 수 있는 사실이 하나 있을 듯 하다. 바로 Result Cache 사이즈가 매우 작게 설정되어 있다는 사실이다.

Result Cache 설정방법에는 2가지가 있으며 result_cache_mode 파라미터를 변경한다.

MANUAL(기본 값)

해당 값으로 설정한 경우에는 Result Cache를 적용하려는 SQL 마다 /*+ result_cache */ 힌트를 적용

FORCE

모든 SQL이 Result Caching 대상이 된다. 반대로 /*+no_result_cache*/ 힌트를 주어야 만 cache되지 않게 할 수 있다.

  • [표] Result Cache 관련 파라미터
  • 구분 기본값 설명
    RESULT_CACHE_MODE manual 1 Result 캐시 등록 방식을 결정
    - manual : result_cache 힌트를 명시한 SQL 만 등록
    - force : no_result_cache 힌트를 명시하지 않은 모든 SQL을 등록
    RESULT_CACHE_MAX_SIZE N/A SGA내에서 result_cache가 사용할 메모리 총량을 바이트로 지정
    0 : 기능 동작하지 않음
    RESULT_CACHE_MAX_RESULT 5 하나의 SQL 결과 집합이 전체 캐시 영역에서 차지할수 있는 최대 크기를 %로 지정
    RESULT_CACHE_REMOTE_EXPIRATION 0 remote 객체의 결과를 얼마동안 보관할지를 분단위로 지정
    0 : 저장하지 않음

Result Cache 적용에 대한 예제를 하나 보도록 하자.

  • [리스트 1] Result Cache 적용 테스트 환경
  • SQL> show parameter memory_target
    
    NAME                    TYPE        VALUE
    ----------------------- ----------- -------
    memory_target           big integer 1232M
    
    
    
    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 3168K
    result_cache_mode                    string      MANUAL
    result_cache_remote_expiration       integer     0
    

Memory_target을 사용중 이고 크기는 1,232MB 이며 Result_cache_max_size는 3168K 이다. 위에서도 설명했지만, 메모리 설정에 따라 기본 사이즈는 변경이 된다고 했다. 해당 경우에는 0.25%이기에 식을 만들면 (1232*1024) / 400이고 답은 3153.93이다. db_block_size가 8k 이므로 3168의 값이 측정된다.

  • [리스트 2] Result Cache 미적용
  • SQL> ALTER SYSTEM FLUSH SHARED_POOL;
    시스템이 변경되었습니다.
    경   과: 00:00:00.01
    
    
    SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
    시스템이 변경되었습니다.
    경   과: 00:00:03.13
    
    
    SQL> SELECT COUNT(*) FROM SCOTT.DBA_OBJECT_BAK;
      COUNT(*)
    ----------
       1667799
    경   과: 00:00:02.34
    
    
    SQL> /
      COUNT(*)
    ----------
       1667799
    경   과: 00:00:02.13
    
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name           | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                |       |  6529 (100)|          |
    |   1 |  SORT AGGREGATE    |                |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| DBA_OBJECT_BAK |  1803K|  6529   (1)| 00:01:19 |
    -------------------------------------------------------------------------
    

<리스트 2>는 Result Cache가 미적용된 상태다. <리스트 3>은 Result Cache를 적용했다. 두 상황을 비교해보겠다.

  • [리스트 3] Result Cache 적용
  • SQL> EXEC DBMS_RESULT_CACHE.FLUSH
    PL/SQL 처리가 정상적으로 완료되었습니다.
    경   과: 00:00:00.01
    
    
    SQL> ALTER SYSTEM FLUSH SHARED_POOL;
    시스템이 변경되었습니다.
    경   과: 00:00:00.01
    
    
    SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
    시스템이 변경되었습니다.
    경   과: 00:00:03.13
    
    
    SQL> SELECT /*+ RESULT_CACHE */ COUNT(*) FROM SCOTT.DBA_OBJECT_BAK;
      COUNT(*)
    ----------
       1667799
    경   과: 00:00:02.15
    
    
    SQL> /
      COUNT(*)
    ----------
       1667799
    경   과: 00:00:00.00
    
    ------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                            |       |  6529 (100)|          |
    |   1 |  RESULT CACHE       | cv8058t7z28ak9bh7gy0r7v36c |       |            |          |
    |   2 |   SORT AGGREGATE    |                            |     1 |            |          |
    |   3 |    TABLE ACCESS FULL| DBA_OBJECT_BAK             |  1803K|  6529   (1)| 00:01:19 |
    ----------------------------------------------------------------------------------------
    

아주 간단하게 Result Cache를 적용 전/후에 대하여 테스트를 진행하였다. 위의 SQL을 두 번씩 실행한 이유는 Hard Parsing이나 Buffer Cache로 캐싱한 후의 결과를 보여주기 위해 실행하였음을 참고하기 바란다.

결과적으로 Result Cache 힌트를 적용하였을 때, <리스트 2>과는 다르게 <리스트 3>의 경우 메모리에 캐싱을 하고 난 후 수행시간이 거의 0초에 가깝게 나왔음을 볼 수 있으며, 실행계획에서도 1번과는 다르게 2번에서는 RESULT CACHE가 적용이 되었음을 확인할 수 있다.

Result Cache 사용 범위를 아래와 같이 정리할 수 있을 것이다.

사용이 적절한 경우
  • - 작은 결과 집합을 얻는 SQL에 대한 수행이 많을 경우
  • - 읽기 전용의 작은 테이블에 대한 수행이 많을 경우
  • - 읽기 전용의 코드성 테이블에 대한 수행이 많을 경우

사용이 적절하지 않은 경우

SQL에서 사용하는 테이블의 DML이 자주 발생하는 경우

결과 반환이 많은 Literal SQL을 사용하는 경우 Result Cache가 메모리에서 빠른 결과값을 얻어올 수 있는 점은 긍정적이다.

앞에서 확인한 대로 Result Cache의 기본 사이즈는 공유된 Pool 크기에 따라 다르긴 하지만 작게 설정 돼있다. 메모리에 캐싱하는 SQL 또한 적게 사용하라는 의미가 아닐까 싶다.

무분별한 Result Cache의 사용은 오히려 DB 성능에 막대한 영향을 줄 수 있다라는 사실을 상기해 유용하게 사용한다면 아주 괜찮은 기능이라고 생각한다.

- 강좌 URL : http://www.gurubee.net/lecture/2923

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입