엑시엄이 보는 DB 세상

Result Cache는 과연 독일까

한 고객사에서 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 적용에 대한 예제를 하나 보도록 하자.

<리스트 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 성능에 막대한 영향을 줄 수 있다라는 사실을 상기해 유용하게 사용한다면 아주 괜찮은 기능이라고 생각한다.