꿈꾸는 개발자, DBA 커뮤니티 구루비

한 고객사에서 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 사용 범위를 아래와 같이 정리할 수 있을 것이다.