[DB] 공유 풀 Shared Pool 간단하게 이해하기 -2

Result cache

사용자가 임의의 SQL 혹은 PL/SQL 문장을 실행하는 경우 그 결과값(사용자에게 반환되는 SQL 쿼리 결과값 자체)을 메모리(Result cache)에 저장한 후 이후에 같은 결과값이 요청되는 경우 이를 메모리에서 찾아 사용자에게 바로 반환해주는 기능을 제공한다.

▼ 아래와 같은 문장들이 Result cache에 저장된다.

   ● 일반적인 SQL Query

   ● 특정 시점에 대한 Flashback query

   ● Query 결과가 Read-consistent 한 Snapshot 인 경우

   ● View 또는 Inline view 형태의 Query block

▼ 아래와 같은 쿼리들은 Result cache에 저장되지 못한다.

   ● Dictionary 및 Temporary table에 대한 SQL Query 문장

   ● Sequence의 Curval/Nextval에 대한 SQL Query 문장

   ● current_date, current_timestamp, local_timestamp, userenv/sys_context

   ● sys_guid, sysdate, sys_timestamp 등의 함수 호출이 포함된 SQL 쿼리 문장

   ● Non-deterministic PL/SQL 함수를 호출하는 SQL 쿼리 문장 

▼ Result cache는 아래와 같은 기본값으로 공유 풀 내부에 생성된다.

   ● memory_target 사용 시 : memory_target 값의 0.25%

   ● sga_target 사용 시 : sga_target 값의 0.5%

   ● shared_pool_size 사용 시 : shared_pool_size 값의 1%

※ Result cache의 크기를 인위적으로 지정해야 하는 상황일 때에는 result_cache_max_size라는 파라미터를 사용해서 설정 가능

   단, 공유 풀 크기의 75%가 넘지 않는 선에서 할당하는것을 권고함

Result cache 설정 방식

Step 1) result_cache_mode 파라미터 설정 (두 가지 옵션 가능)

       

         ● Manual : 이 값을 설정하는 경우 각각의 SQL 문장마다 /+result_cache/ 힌트를 사용해야만 Result cache에 저장된다.

         ● Force   : 모든 SQL이 Result cache의 대상이 된다.

SQL> show parameter result_cache_mode

NAME                             TYPE                      VALUE

------------------------         ------------------       -------------

result_cache_mode             string                     MANUAL

Step 2) 현재 할당되어있는 메모리 현황을 파악한다.

SQL> show parameter memory_target

NAME                             TYPE                      VALUE

------------------------         ------------------       -------------

memory_target                  big integer              1200M

SQL> show parameter result_cache_max_size

NAME                             TYPE                      VALUE

------------------------         ------------------       -------------

result_cache_max_size         big integer              3M

SQL> show parameter result_cache_max_result

NAME                                 TYPE                      VALUE

----------------------------         ------------------       -------------

result_cache_max_result            integer                   5

Step 3) Result cache, 공유 풀 그리고 데이터베이스 버퍼 캐시까지 모두 Flush 시킨다.

SQL> execute dbms_result_cache.flush;

SQL> alter system flush shared_pool;

SQL> alter system flush buffer_cache;

Step 4) 테스트를 시작하기 전 Result cache 분석 결과를 확인한다.

SQL> set serveroutput on

SQL> execute 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                = 0 bytes

Maximum Cache Size  = 0 bytes  (0 blocks)

Maximum Result Size  = 0 bytes  (0 blocks)

[Memory]

Total Memory = 5440 bytes  [0.003% of the Shared Pool]

Fixed Memory = 5440 bytes  [0.003% of the Shared Pool]

Dynamic Memory = 0 bytes  [0.000% of the Shared Pool]               

※ Dynamic Memory를 통해 현재 Result cache에 저장되어있는 데이터의 용량을 알 수 있다.

   v$result_cache_statistics 뷰를 통해서도 Result cache 관련 통계치를 확인할 수 있다.

SQL> select id, name, value from v$result_cache_statistics;

ID           NAME                                        VALUE

--------    -------------------------------------     ----------------------------

......          ..............                                        ...........

5            Create Count Success                     0

......          ..............                                        ...........

7            Find Count                                   0

※ Create Count Success 값이 0이란 의미는 현재 Result cache 영역에 생성된 오브젝트가 없다는 의미이다.

   Find Count 값이 0이란 의미는 Result cache에 저장된 데이터가 사용된 적이 아직 없다는 의미이다.

Step 5) SQL 문장의 결과값을 Result cache 영역에 저장하고 /*+ result_cache */ 힌트를 사용하여 실행하도록 한다.

SQL> set autotrace on;

SQL> select /*+ result_cache */ empno, ename from scott.emp;

-------------------- 결과 생략----------------------------

SQL> column id format 9999

SQL> column name format a30

SQL> column value format a20

SQL> select id, name, value from v$result_cache_statistics;

ID           NAME                                        VALUE

--------    -------------------------------------     ----------------------------

......          ..............                                        ...........

5            Create Count Success                     1

......          ..............                                        ...........

7            Find Count                                   0

Result cache 영역에 오브젝트가 1개 생성되었고 Result cache로부터 데이터를 반환한 경우가 발생한 적이 없다는걸 알 수 있다.

Step 6) SQL 문장을 다시 실행하여 Find Count 값이 증가하는지 확인하도록 한다. (힌트 제거)

SQL> select empno, ename from scott.emp;

-----------------------결과 생략-------------------

SQL> select id, name, value from v$result_cache_statistics;

ID           NAME                                        VALUE

--------    -------------------------------------     ----------------------------

......          ..............                                        ...........

5            Create Count Success                     1

......          ..............                                        ...........

7            Find Count                                   0

Result cache를 사용하지도 않고 단순히 emp테이블에 대한 풀 스캔을 진행하고 있다.

Result cache를 생성할 때 사용했던 동일한 문장을 실행하도록 한다.

SQL> select /*+ result_cache */ empno, ename from scott.emp;

ID           NAME                                        VALUE

--------    -------------------------------------     ----------------------------

......          ..............                                        ...........

5            Create Count Success                     1

......          ..............                                        ...........

7            Find Count                                   1

이제야 Find Count 값이 1로 변경된 것을 확인할 수 있다.