반응형

Result cache안에 저장되어질 result set의 최고 크기를 지정해 주는 result_cache_max_result 파라메터가 하는 역활 및 데이터가 초과 할 경우 result cache 영역안에 어떤 변화가 있는지 테스트를 통하여 알아 보도록 하겠다.

테스트 시나리오

   - Case#1
      하나의 등록된 Result Set이 점점 커져서 max result 값을 초과할 경우
   - Case#2
      초기에 입력되는 Result Set 자체가 max result값보다 클 경우
   - Case#3
       동적으로 result_cache_max_result 값을 변경 할 경우 정상적으로 등록되어 있던 result set에 미치는
       영향

테스트 파라메터 환경

RESULT_CACHE_MODE=MANUAL
RESULT_CACHE_MAX_SIZE=128K
RESULT_CACHE_MAX_RESULT=10
_RESULT_CACHE_BLOCK_SIZE=1024


테스트 스크립트

- flush.sql

  exec dbms_result_cache.flush ;

- check.sql
 
  set line 150
  col id format 99999
  col name format a50
 
  set serveroutput on  
  exec dbms_result_cache.memory_report(true);
 
  SELECT id ,type , status , name , block_count , row_count , row_size_avg , invalidations
  FROM   v$result_cache_objects ;
 
  SELECT object_id, COUNT( * ) FROM   v$result_cache_memory WHERE  free = 'NO'
  GROUP  BY object_id ;  

- create.sql

   DROP TABLE TEST_RC PURGE ;
  
   CREATE TABLE TEST_RC
   ( ID      NUMBER ,
    NAME CHAR(1000) ) ;

   BEGIN
   FOR I IN 1..10 LOOP
      INSERT INTO TEST_RC VALUES ( i , to_char(i) || ' User') ;
   END LOOP;
      COMMIT;
   END;
   /


- Case#1

SQL>@create.sql
SQL>@flush.sql
SQL>@check.sql
SQL>SELECT /*+ result_cache */ * FROM test_rc ;
SQL>@check.sql

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          = 1K bytes
Maximum Cache Size  = 128K bytes (128 blocks)
Maximum Result Size = 12K bytes (12 blocks)
[Memory]
Total Memory = 96336 bytes [0.062% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
....... Cache Mgr  = 112 bytes
....... Memory Mgr = 128 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 91196 bytes [0.059% of the Shared Pool]
....... Overhead = 58428 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 1084 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 20 blocks
........... Used Memory = 12 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 11 blocks
................... SQL     = 11 blocks (1 count)


    ID TYPE       STATUS    NAME       BLOCK_COUNT  ROW_COUNT ROW_SIZE_AVG INVALIDATIONS
------ ---------- --------- -------------------------------------------------- -----------
     0 Dependency Published BSTAR.TEST_RC                                          1     0        0          0
     1 Result          Published SELECT /*+ result_cache */ * FROM test_rc  11   10    1007       0


 OBJECT_ID   COUNT(*)
---------- ----------
         1         11
         0          1


위의 결과를 보면 하나의 Result Set이 등록될 수 있는 최대 크기가 12 블록임을 확인 할 수 있다.
TEST_RC 테이블에 대한 조회 결과 현재 하나의 쿼리가 11개의 블록을 사용하고 있다.
아직 Max Size를 넘지 않아서 상태는 Published 상태 임을 확인 할 수 있다.
하나의 행을 더 insert 한 후에 동일 SQL문을 재 수행 해 보도록 하자

SQL> insert into test_rc values ( 11 , '11 User') ;
SQL> commit ;
SQL>SELECT /*+ result_cache */ * FROM test_rc ;
SQL>@check.sql

....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 8 blocks
........... Used Memory = 24 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 23 blocks
................... SQL     = 12 blocks (1 count)
................... Invalid = 11 blocks (1 count)

PL/SQL 처리가 정상적으로 완료되었습니다.

    ID TYPE       STATUS    NAME         BLOCK_COUNT  ROW_COUNT ROW_SIZE_AVG INVALIDATIONS
------ -------------------------------------------------- ----------- ---------- ------------ ----
     0 Dependency Published BSTAR.TEST_RC                                           1       0         0         1
    12 Result         Published SELECT /*+ result_cache */ * FROM test_rc   12      11    1007         0
     1 Result          Invalid      SELECT /*+ result_cache */ * FROM test_rc    11     10    1007         0


 OBJECT_ID   COUNT(*)
---------- ----------
         1         11
         0          1
        12         12


id값이 12인 Row를 보면 쿼리 결과로 12개의 블록을 사용하고 있다. 아직 Result set 의 크기가 32K를 넘지 않았고, 다른 쿼리에 의해서 사용중이 아니기 때문에 참조 Object가 invalidation이 발생할 경우 새로운 Result Set이 등록이 되었다. 중요것은 block_count값이 12블록 이라는 점이다.

그럼 max값을 넘어 가도록 데이터를 한 건 더 넣어 보자

SQL> insert into test_rc values ( 12 , '12 User') ;
SQL> commit ;
SQL>SELECT /*+ result_cache */ * FROM test_rc ;
SQL>@check.sql

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          = 1K bytes
Maximum Cache Size  = 128K bytes (128 blocks)
Maximum Result Size = 12K bytes (12 blocks)
[Memory]
Total Memory = 118872 bytes [0.077% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
....... Cache Mgr  = 112 bytes
....... Memory Mgr = 128 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 113732 bytes [0.073% of the Shared Pool]
....... Overhead = 80964 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 23620 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 8 blocks
........... Used Memory = 24 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 23 blocks
................... Invalid = 23 blocks (2 count)


    ID TYPE       STATUS    NAME         BLOCK_COUNT  ROW_COUNT ROW_SIZE_AVG INVALIDATIONS
------ -------------------------------------------------- ----------- ---------- ------------ ----    0 Dependency Published BSTAR.TEST_RC                                           1       0         0         2
    12 Result          Invalid     SELECT /*+ result_cache */ * FROM test_rc   12      0         0         0
     1 Result          Invalid      SELECT /*+ result_cache */ * FROM test_rc    11     10    1007         0


 OBJECT_ID   COUNT(*)
---------- ----------
         1         11
        24         12
         0          1


max result 크기를 넘어 가는 순간 Result Cache안에 존재하던 Result set이 invalid 상태로 변한다.
하지만 아직까지는 메모리의 사용공간에 대한 해제가 일어나지 않는다. 다른 쿼리에서 공간 요청시 해제가 될때 까지 해당 데이터는 유지가 된다.

그럼 result cache안에 남아있는 12 블록의 데이터라도 사용이 되는 것인가 ?

다음 두 쿼리를 수행한 후 Stat값을 비교해 보자

SQL> set autotrace on exp stat
SQL> SELECT * FROM test_rc ;
SQL> SELECT /*+ result_cache */ * FROM test_rc ;

Statistics
------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
      12670  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed


비교해 보면 두 쿼리다 Result Cache를 사용하지 않고 있다. 즉 , Invalid 상태의 Result Set에 대해서는 사용을 하지 않는다.


- Case#2

SQL>@create.sql
SQL>@flush.sql
SQL>@check.sql
SQL> insert into test_rc values ( 11 , '11 User') ;
SQL> insert into test_rc values ( 12 , '12 User') ;
SQL> commit ;
SQL>SELECT /*+ result_cache */ * FROM test_rc ;
SQL>@check

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          = 1K bytes
Maximum Cache Size  = 128K bytes (128 blocks)
Maximum Result Size = 12K bytes (12 blocks)
[Memory]
Total Memory = 118872 bytes [0.077% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
....... Cache Mgr  = 112 bytes
....... Memory Mgr = 128 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 113732 bytes [0.073% of the Shared Pool]
....... Overhead = 80964 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 23620 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 19 blocks
........... Used Memory = 13 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 12 blocks
................... Invalid = 12 blocks (1 count)


   ID TYPE       STATUS    NAME         BLOCK_COUNT  ROW_COUNT ROW_SIZE_AVG INVALIDATIONS
------ -------------------------------------------------- ----------- ---------- ------------ ----     0 Dependency Published BSTAR.TEST_RC                                          1       0         0          0
     1 Result         Invalid       SELECT /*+ result_cache */ * FROM test_rc 12        0         0          0


 OBJECT_ID   COUNT(*)
---------- ----------
         1         12
         0          1


예상을 했듯이 max result값을 넘어서는 데이터는 max result 크기 까지는 등록이 되지만 invald상태 이므로 사용은 할 수 없는 상태이다.


- Case#3

SQL>@create.sql
SQL>@flush.sql
SQL>@check.sql
SQL> insert into test_rc values ( 11 , '11 User') ;
SQL> insert into test_rc values ( 12 , '12 User') ;
SQL> commit ;
SQL>ALTER SYSTEM SET result_cache_max_result=15;
SQL>SELECT /*+ result_cache */ * FROM test_rc ;
SQL>@check

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          = 1K bytes
Maximum Cache Size  = 128K bytes (128 blocks)
Maximum Result Size = 19K bytes (19 blocks)
[Memory]
Total Memory = 103536 bytes [0.067% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
....... Cache Mgr  = 112 bytes
....... Memory Mgr = 128 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.063% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8284 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 18 blocks
........... Used Memory = 14 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 13 blocks
................... SQL     = 13 blocks (1 count)


   ID TYPE       STATUS    NAME         BLOCK_COUNT  ROW_COUNT ROW_SIZE_AVG INVALIDATIONS
------ -------------------------------------------------- ----------- ---------- ------------ ----     0 Dependency Published BSTAR.TEST_RC                                          1       0         0          0
     1 Result         Published  SELECT /*+ result_cache */ * FROM test_rc 13     12     1006        0

 OBJECT_ID   COUNT(*)
---------- ----------
         1         13
         0          1


SQL>ALTER SYSTEM SET result_cache_max_result=10;
SQL>SELECT /*+ result_cache */ * FROM test_rc ;
SQL>@check

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          = 1K bytes
Maximum Cache Size  = 128K bytes (128 blocks)
Maximum Result Size = 12K bytes (12 blocks)
[Memory]
Total Memory = 103536 bytes [0.067% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
....... Cache Mgr  = 112 bytes
....... Memory Mgr = 128 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.063% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8284 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 18 blocks
........... Used Memory = 14 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 13 blocks
................... SQL     = 13 blocks (1 count)


   ID TYPE       STATUS    NAME         BLOCK_COUNT  ROW_COUNT ROW_SIZE_AVG INVALIDATIONS
------ -------------------------------------------------- ----------- ---------- ------------ ----     0 Dependency Published BSTAR.TEST_RC                                          1       0         0          0
     1 Result         Published  SELECT /*+ result_cache */ * FROM test_rc 13     12     1006        0

 OBJECT_ID   COUNT(*)
---------- ----------
         1         13
         0          1


동적으로 max result 크기를 변경해 주었어도 기존에 등록되어 있던 결과에는 영향을 미치지 못한다.


SQL> insert into test_rc values ( 13 , '13 User') ;
SQL> commit ;
SQL>SELECT /*+ result_cache */ * FROM test_rc ;
SQL>@check

   ID TYPE       STATUS    NAME         BLOCK_COUNT  ROW_COUNT ROW_SIZE_AVG INVALIDATIONS
------ -------------------------------------------------- ----------- ---------- ------------ ----     0 Dependency Published BSTAR.TEST_RC                                          1       0         0          1
     1 Result         Invalid     SELECT /*+ result_cache */ * FROM test_rc 13     12     1006          0
   14  Result        Invalid      SELECT /*+ result_cache */ * FROM test_rc 12     0           0         0


 OBJECT_ID   COUNT(*)
---------- ----------
         1         13
        14         12
         0          1


동적으로 max result 크기를 줄여도 사용가능했던 result set 이 invalid 상태로 전환됨을 확인 할 수 있다.

위의 세가지 테스트를 통하여 result_cache_max_result 파라메터는 Result Cache 영역에 저장 될 수 있는 하나의 Result Set의 최대 크기를 지정 하며, result set이 저장되는 시점에 해당 값을 초과하는 result set에 대해서 invalid 시킴으로써 사용을 할 수 없도록 한다.

일반 데이터크기는 max result 크기를 넘지 않도록 제한을 두고 싶다. 하지만 하나의 쿼리에 의해서 리턴되는 result set이 큰 경우가 하나 있다고 가정하자.
어떻게 할 수 있을까 ? 하나의 쿼리를 위해서 result_cache_max_result값을 크게 설정해 놓을 수 만은 없는 일이다.
위의 Case#3번의 방법을 사용하면 될 것이다. 대용량 쿼리 결과를 저장하기 위하여 일시적으로 max result파라메터를 크기 해 놓은 후 데이터가 캐시되면 다시 작은 사이즈로 변경을 해 놓는 방법을 사용하면 될 것이다.

반응형

+ Recent posts