07. Result Cache

• 오라클은 한번 수행한 쿼리 또는 PL/SQL 함수의 결과값을 Result 캐시에 저장해 두는 기능을 11g버전부터 제공하기 시작함.
• 예를 들어 특정 Query가 반복적으로 수행될 때 이 결과를 캐시하여, 다음부터는 해당 쿼리를 다시 execute하는 것이 아니라 캐시 메모리에 저장된 결과 값을 그대로 가져오게 된다.
• DML이 거의 발생하지 않는 테이블을 참조하면서, 반복 수행 요청이 많은 쿼리에 이 기능을 사용하면 I/O발생량을 현격히 감소시킬 수 있다.
• Result 캐시는 버퍼캐시에 위치하지 않고 Shared Pool에 위치하지만 시스템 I/O 발생량을 최소화하는데 도움이 되는 기능이다.

Result Cache 영역

• SQL Query Result 캐시 : SQL 쿼리 결과를 저장
• PL/SQL 함수 Result 캐시 : PL/SQL 함수 결과값을 저장

• Result Cache는 SGA영역에 존재하므로, 모든 세션에서 공유가능하고, 인스턴스를 재기동하면 초기화되며, 해당 쿼리가 접근하는 오브젝트가 변경될 때 invalid된다. 공유영역에 존재하므로 래치가 필요

Result Cache에 Caching 못하는 경우.

• Dictionary 오브젝트를 참조할 때
• Temporary 테이블을 참조할 때
• 시퀀스로부터 CURRVAL, NEXTVAL Pseudo 컬럼을 호출할 때

• 쿼리에서 아래 SQL함수를 사용할 때
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • LOCAL_TIMESTAMP
  • SYS_CONTEXT(with non-constant variables)
  • SYS_GUID
  • SYSDATE
  • SYSTIMESTAMP
  • USERENV(with non-constant variables)

• 바인드 변수를 사용한 쿼리는 바인딩 되는 값에 따라 개별적으로 캐싱되므로, 변수값 종류가 다양한 쿼리는 등록을 삼가해야한다.
• 쿼리에서 사용하는 테이블에 DML이 발생한 경우 캐싱된 결과집합을 무효화 시킨다.
• 인라인뷰 또는 일부집합만 캐싱도 가능하나 서브쿼리는 불가능하다.

사용권장

• 작은 결과 집합을 얻으려고 대용량 데이터를 읽어야 할 때
• 읽기 전용의 작은 테이블을 반복적으로 읽어야 할 때
• 읽기 전용코드 테이블을 읽어 코드명칭을 반환하는 함수

사용자제

• 쿼리가 참조하는 테이블에 DML이 자주 발생할 때
• 함수 또는 바인드 변수를 가진 쿼리에서 입력되는 값의 종류가 많고, 골고루 입력될 때

• 지금까지 설명한 기능은 서버 측 Result Cache 기능.
• 클라이언트 측 Result Cache기능은 오라클 매뉴얼 참조

Sample
init.ora parameters
~~~~~~~~~~~~~~~~~~~
RESULT_CACHE_MAX_SIZE=0 --- to disable the server side cache
CLIENT_RESULT_CACHE_SIZE=65536 --- >=32K to enable the client side cache

SQL> show parameter RESULT_CACHE_MAX_SIZE;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0
SQL> show parameter CLIENT_RESULT_CACHE_SIZE;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 64K
SQL>

create table table1 (c1 number, c2 varchar2(30))
result_cache (mode default)
/

create table table2 (c1 number, c2 varchar2(30))
result_cache (mode default)
/

begin
for i in 1..5 loop
insert into table1 values (i,'Table 1 - record '||i);
insert into table2 values (i,'Table 2 - record '||i);
end loop;
commit;
end;
/

Client - connect 

  CACHE_ID    STAT_ID NAME                           TO_CHAR(VALUE)
---------- ---------- ------------------------------ --------------------------------------------------------------------------------
         5          1 Block Size                     0
         5          2 Block Count Max                0
         5          3 Block Count Curre              0
         5          4 Hash Bucket Count              0
         5          5 Create Count Succ              0
         5          6 Create Count Fail              0
         5          7 Find Count                     0
         5          8 Invalidation Coun              0
         5          9 Delete Count Inva              0
         5         10 Delete Count Vali              0

SQL> select /*+ NO_RESULT_CACHE */ t1.c1, t1.c2, t2.c2 from table1 t1, table2 t2 where t1.c1=t2.c1 order by t1.c1

        C1 C2                                                           C2
---------- ------------------------------------------------------------ ------------------------------------------------------------
         1 Table 1 - record 1                                           Table 2 - record 1
         2 Table 1 - record 2                                           Table 2 - record 2
         3 Table 1 - record 3                                           Table 2 - record 3
         4 Table 1 - record 4                                           Table 2 - record 4
         5 Table 1 - record 5                                           Table 2 - record 5


  1*  select CACHE_ID, STAT_ID ,substr(NAME,1,17) name ,to_char(VALUE) from CLIENT_RESULT_CACHE_STATS$
SQL> /

  CACHE_ID    STAT_ID NAME                           TO_CHAR(VALUE)
---------- ---------- ------------------------------ --------------------------------------------------------------------------------
         6          1 Block Size                     0
         6          2 Block Count Max                0
         6          3 Block Count Curre              0
         6          4 Hash Bucket Count              0
         6          5 Create Count Succ              0
         6          6 Create Count Fail              0
         6          7 Find Count                     0
         6          8 Invalidation Coun              0
         6          9 Delete Count Inva              0
         6         10 Delete Count Vali              0


SQL> select t1.c1, t1.c2, t2.c2 from table1 t1, table2 t2 where t1.c1=t2.c1 order by t1.c1;                      

        C1 C2                                                           C2
---------- ------------------------------------------------------------ ------------------------------------------------------------
         1 Table 1 - record 1                                           Table 2 - record 1
         2 Table 1 - record 2                                           Table 2 - record 2
         3 Table 1 - record 3                                           Table 2 - record 3
         4 Table 1 - record 4                                           Table 2 - record 4
         5 Table 1 - record 5                                           Table 2 - record 5


  CACHE_ID    STAT_ID NAME                           TO_CHAR(VALUE)
---------- ---------- ------------------------------ --------------------------------------------------------------------------------
         6          1 Block Size                     256
         6          2 Block Count Max                256
         6          3 Block Count Curre              128
         6          4 Hash Bucket Count              1024
         6          5 Create Count Succ              0
         6          6 Create Count Fail              0
         6          7 Find Count                     0
         6          8 Invalidation Coun              0
         6          9 Delete Count Inva              0
         6         10 Delete Count Vali              0

SQL> select /*+ RESULT_CACHE */ t1.c1, t1.c2, t2.c2 from table1 t1, table2 t2 where t1.c1=t2.c1 order by t1.c1;


        C1 C2                                                           C2
---------- ------------------------------------------------------------ ------------------------------------------------------------
         1 Table 1 - record 1                                           Table 2 - record 1
         2 Table 1 - record 2                                           Table 2 - record 2
         3 Table 1 - record 3                                           Table 2 - record 3
         4 Table 1 - record 4                                           Table 2 - record 4
         5 Table 1 - record 5                                           Table 2 - record 5


  CACHE_ID    STAT_ID NAME                           TO_CHAR(VALUE)
---------- ---------- ------------------------------ --------------------------------------------------------------------------------
         7          1 Block Size                     256
         7          2 Block Count Max                256
         7          3 Block Count Curre              128
         7          4 Hash Bucket Count              1024
         7          5 Create Count Succ              1
         7          6 Create Count Fail              0
         7          7 Find Count                     0
         7          8 Invalidation Coun              0
         7          9 Delete Count Inva              0
         7         10 Delete Count Vali              0


select /*+ RESULT_CACHE */ t1.c1, t1.c2, t2.c2
from
 table1 t1, table2 t2 where t1.c1=t2.c1 order by t1.c1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.05          0          0          2           0
Fetch       10      0.00       0.00          0         70          0          25
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.00       0.06          0         70          2          25

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 43
Number of plan statistics captured: 1 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         5          5          5  SORT ORDER BY (cr=14 pr=0 pw=0 time=983 us cost=8 size=300 card=5)
         5          5          5   HASH JOIN  (cr=14 pr=0 pw=0 time=914 us cost=7 size=300 card=5)
         5          5          5    TABLE ACCESS FULL TABLE1 (cr=7 pr=0 pw=0 time=134 us cost=3 size=150 card=5)
         5          5          5    TABLE ACCESS FULL TABLE2 (cr=7 pr=0 pw=0 time=38 us cost=3 size=150 card=5)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  reliable message                                7        0.02          0.05
  SQL*Net message to client                      10        0.00          0.00
  SQL*Net message from client                    10       10.75         33.06
  asynch descriptor resize                        1        0.00          0.00