반응형
Result Cache관련 파라메터 중에서 _result_cache_timeout 히든 파라메터에 대해서 알아 보겠다.
이 파라메터가 어디에 사용이 되는 파라메터 이며 이 파라메터 설정을 변경할때 어떤 이점이 있는지 확인해 보자.

테스트 시나리오

    - Case#1
       여러 세션에서 동시에 같은 테이블 데이터를 Result Cache에 Load 함(세션별 10046 Trace 수행)
    - Case#2
       여러 세션에서 동시에 다른 테이블 데이터를 Result Cache에 Load 함(세션별 10046 Trace 수행)
    - Case#3
       _result_cache_timeout 파라메터 값을 수정 후 Case#1 재 수행
    - Case#4
       오랜시간 수행되는 쿼리에 의해서 리턴되는 결과가 한 개인 쿼리를 여러 세션에서 동시 수행
    - Case#5
       Result Cache 영역이 늘어난 상태에서 세션5에서 Result Cache영역안의 상태를 조회
       ( dbms_result_cache 패키지나 v$result_cache_objects , v$result_cache_memory ..사용)
       이때 다른 세션들에서 Result Cache 영역을 사용하기 위해서 엑세스 시도

테스트 환경

  많은 양의 데이터를 Result Cache에 등록하기 위하여 다음과 같이 테스트 환경을 구성하자

ALTER SYSTEM SET result_cache_max_size = 100M ;
ALTER SYSTEM SET result_cache_max_result=100 ;

 다음 스크립트를 이용하여 테이블 생성 및 데이터 생성

DROP TABLE TEST_RC Purge ;

CREATE TABLE TEST_RC
(id   number ,  name char(1000) );

BEGIN
    FOR i IN 1..100000 LOOP
         insert into test_rc values ( i , 'Test') ;
    END LOOP;
    COMMIT;
END;
/


우선 파라메터에 대해서 확인해 보기 위해 다음 쿼리를 이용하여 히든 파라메터를 조사해 본다.

SELECT ksppinm name ,
       ksppstvl VALUE ,
       decode( ksppstdf , 'TRUE' , '' , ksppstdf ) "DEFAULT ?" ,
       decode( bitand( ksppstvf , 7 ) , 1 , 'MODIFIED' , 4 , 'SYSTEM_MOD' ) "CHANGED ?" ,
       decode( bitand( ksppiflg/65536 , 3 ) , 1 , 'IMMEDIATE' , 2 , 'DEFERRED' , 3 , 'IMMEDIATE' ) "SYS MODIFIABLE" ,
       decode( bitand( ksppiflg/256 , 1 ) , 1 , 'TRUE' ) "SESSION MODIFIABLE" ,
       ksppdesc description
FROM   sys.x$ksppi x ,
       sys.x$ksppsv y
WHERE  x.indx = y.indx
AND    x.inst_id = y.inst_id
AND    x.inst_id = USERENV( 'instance' )
AND    LOWER( ksppinm ) LIKE '_result_cache_timeout' ;


조사해 보면 기본 값이 60임을 확인 할 수 있다.

60이 의미 하는 것이 무엇인지 테스트를 통하여 확인해 보도록 하자.

- Case#1

 네 개의 세션에서 세션별로 10046 트래이스를 12 레벨로 설정 후 SELECT /*+ result_cache */ * FROM test_rc ; 쿼리를 수행해 보자.

ALTER SESSION SET tracefile_identifier='S1' ; <-- 세션별로 S1 , S2 , S3 , S4
ALTER SESSION SET  statistics_level=all ;
ALTER SESSION SET EVENTS '10046 trace name context forever , level 12' ;
SELECT /*+ result_cache */ * FROM test_rc ;


최초 수행한 세션1만 Fetch 중이고 세션2~세션4까지의 세션은 대기 하고 있음을 확인 할 수 있다.
대기 이벤트를 확인하기 위하여 v$session 뷰를 조회해 보면

SELECT sid , SCHEMANAME , event , p1 , p1raw , p2 , p2raw , p3 , p3raw FROM v$session
WHERE wait_class <> 'Idle' ;

세 개의 세션에서  enq : RC - Result Cache : Contention 대기를 하고 있다.

SELECT * FROM v$lock where type ='RC';

v$lock 뷰를 이용하여 확인해 보면 최초 세션이 6번 모드로 Lock을 획득 중에 있으며 나머지 세션들은 4번 모드로 락을 대기 하고 있는 것이 확인이 된다.

세션1에서 Fetch를 진행하면서 Result Cache에 로드 중이고 나머지 세션들은 이 데이터를 사용하기 위하여 대기를 하고 있는 것이다.

즉, _result_cache_timeout 파라메터에 설정 된 시간까지 Cache된 Result를 사용하려고 대기를 한다는 사실에 주목을 해야 한다.

SELECT sid , event , total_waits , time_waited FROM v$session_event
WHERE event like '%RC%' or event like '%Result%' ;

SID   EVENT                                           TOTAL_WAITS  TIME_WAITED
---   ---------------------------------- -------------   -------------
135   enq: RC - Result Cache: Contention                   6                5536
138   enq: RC - Result Cache: Contention                   6                5655
139   enq: RC - Result Cache: Contention                   6                5590


위의 쿼리를 이용하여 세션별 enq:RC 이벤트 대기 시간을 확인해 보면 10초당 1번씩 Result Cache를 사용하기 위하여 시도 하였음을 확인 할 수 있다.

시도를 하다가 Result Cache에 대한 Access가 가능해 지면 Result Cache를 사용하고 그렇지 않을 경우 Result Cache 사용을 포기 하고 직접 데이터에 대한 엑세스를 시도 하게 된다.

time_waited가 60초가 안되고 55초 대 인것을 보면 60초까지 기다리기 전에 Result cache 데이터에 대하여 엑세스가 가능해져서 Result cache 영역안의 데이터를 사용하였다. 데이터를 좀 더 넣은 후 테스트를 진행해 보면 실제 세션2~세션4는 테이블에 엑세스 하고 있는 것을 확인 할 수 있다.

다음 쿼리를 이용하면 보다 분명히 Result cache 를 사용하였는지 확인 가능하다.

SELECT name , scan_count FROM v$result_cache_objects WHERE type = 'Result' ;

NAME                                                         SCAN_COUNT
-----------------------------------------    -------------
SELECT /*+ result_cache */ * FROM test_rc     
3


- Case#2

   네 개의 세션에서 서로 다른 테이블 TEST_RC , TEST_RC1 , TEST_RC2 , TEST_RC3 테이블의 데이터 Load 테스트
  테이블 생성 스크립트는 동일하나 테이블 이름만 변경해서 테스트를 진행 하였다.
  테스트를 진행 하기 전에 Result cache를 flush 시킨 후 테스트를 진행 하였다.

exec dbms_result_cache.flush

  테스트를 수행해 보면 서로 다른 테이블의 데이터를 로드 시키는데는 아무런 제약 사항이 없음을 확인 할 수 있다. 서로 다른 세션에서 서로 다른 테이블 데이터를 로드 시키는데는 세션간의 경합이 발생되지 않았다.

- Case#3

  _result_cache_timeout 파라메터 값을 줄인 후 Case#1의 테스트를 재 수행해 보도록 하자.
 히든 파라메터를 수정하기 위해서는 데이터베이스를 종료 후 변경해 주어야 한다. spfile은 수정이 불가 하므로 pfile로 변경 후 수정하고 다시 spfile로 변경하여 데이터 베이스를 재 가동 시킨다.

SHUTDOWN immediate
CREATE pfile FROM spfile ;
Edit pfile : _result_cache_timeout=0 추가
CREATE spfile FROM pfile ;
STARTUP

OR

ALTER SYSTEM SET "_result_cache_timeout"=0


위와 같이 수정 후 Case#1을 수행하면 세션2~세션4는 쿼리를 수행하자 마자 실제 테이블을 조회 하여서 데이터를 가져 오고 있음을 확인할 수 있다.( Trace File 또는 Scan_count 값을 확인 하면 됨)
더불어 enq : RC 이벤트도 발견되지 않는다.

- Case#4

   쿼리 수행 시간이 오래 걸리는 쿼리를 여러 세션에서 동시 수행 할 경우 어떤 결과가 나타나는지 확인해 보도록 하자.

ALTER SYSTEM SET "_result_cache_timeout"=10;

- 수행할 쿼리

SELECT /*+ result_cache */ count(*) FROM test_rc r1 , test_rc r2 , test_rc r3 ;

- Plan 확인

EXPLAIN PLAN FOR SELECT /*+ result_cache */ count(*) FROM test_rc r1 , test_rc r2 , test_rc r3 ;
SELECT * FROM table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 1048373918
---------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                               | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                                           |      1 |         41T  (1)|999:59:59 |
|   1 |  RESULT CACHE              |75wjruu4nj43x792p8wnam43ws|         |                   |              |
|   2 |   SORT AGGREGATE        |                                           |      1 |                    |             |
|   3 |    MERGE JOIN CARTESIAN |                                        |  1000T|        41T  (1)|999:59:59 |
|   4 |     MERGE JOIN CARTESIAN|                                        |     10G|     412M  (1)|999:59:59 |
|   5 |      TABLE ACCESS FULL  | TEST_RC                            |    100K|     4124   (1)| 00:00:50 |
|   6 |      BUFFER SORT             |                                          |    100K|     412M  (1)|999:59:59 |
|   7 |       TABLE ACCESS FULL | TEST_RC                            |    100K|     4121   (1)| 00:00:50 |
|   8 |     BUFFER SORT              |                                          |    100K|       41T  (1)|999:59:59 |
|   9 |      TABLE ACCESS FULL  | TEST_RC                            |    100K|     4121   (1)| 00:00:50 |
---------------------------------------------------------------------------------------------

  위의 실행계획을 확인해 보면 세개의 테이블을 FULL SCAN 하면서 조인을 하게 되며 소요시간이 상당히 오래 걸리는 것으로 확인이 된다.

세 개의 세션에서 동일 쿼리 수행 후 다음 쿼리를 이용하여 조회를 해 보면 다음과 같은 결과가 나온다.

SELECT sid , event , total_waits , time_waited FROM v$session_event
WHERE event like '%RC%' or event like '%Result%' ;

       SID       EVENT                                               TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
       136     enq: RC - Result Cache: Contention                    10        9303
       153     enq: RC - Result Cache: Contention                    10        9304

SELECT * FROM v$lock where type ='RC';

ADDR        KADDR       SID  TYPE      ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- --------- ----- ---- -------- ---------- ---------- ---------- ------- ----------
2EC34A50  2EC34A7C   137   RC          1          3          6              0              164          1
2EC34B38  2EC34B64    153   RC          1          3          0             4                 3          0
2EC34BAC 2EC34BD8   136   RC          1          3          0             4                  1          0

위의 결과를 보면 Case#1과는 약간 차이가 남을 확인 할 수 있다. 현재 _result_cache_timeout 파라메터의 값은 10초로 설정이 되어 있다. 하지만 v$session_event 뷰를 보면 현재 대기 시간이 93초를 넘어 가고 있다.  93초에 enq : RC 대기가 끝난 것은 아니고 계속해서 대기 중이다.

최초 수행되는 쿼리에 의해서 Cache ID가 생성이 되며 최초 Cache ID 를 생성한 세션이 Fetch를 마칠때 까지 Lock을 Holding 한다. 이때 다른 세션들이 동일 SQL을 수행하게 되면 동일 Cache ID를 찾아 가게 되며 이때 세션들은 Lock를 4 모드로 획득하려고 한다.  만약 lock을 획득 하지 못할 경우 enq:RC 대기를 하게 된다.
최초 수행 세션이 fetch를 하면서 데이터를 Result Cache로 로드 중인 경우 오라클은 어느 정도 시간이 소요되면 실제 테이블 테이터에 엑세스 하지 않더라도 Result Cache에서 데이터를 가져오는 것이 더 낫겠다는 판단하에 그 기다리는 시간을 설정 하는 파라메터(_result_cache_timeout)를 제공해 준 것으로 생각이 된다.
이번 테스트에서는 아직 첫 번째 세션( SID 137 )에서 Lock을 6번 모드로 획득하고 있는 상태이며 아직 데이터를 fetch를 통하여 Result Cache로 로드 중이 아니므로 _result_cache_timeout 파라메터의 설정 값이 적용이 되질 않은 것이다.


- Case#5

   현재 Result Cache 영역안에  TEST_RC 테이블의 데이터가 33000개 들어가 있다.
   그 상태에서 DBA가 dbms_result_cache.memory_report를 이용하여 캐시 사용률을 조사한다고 가정한다

SELECT /*+ result_cache */ * FROM test_rc WHERE id < 33000 ;
exec 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          = 1K bytes
Maximum Cache Size  = 110M bytes (110K blocks)
Maximum Result Size = 110M bytes (110K blocks)
[Memory]
Total Memory = 33793244 bytes [10.743% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 33788104 bytes [10.741% of the Shared Pool]
....... Overhead = 102600 bytes
....... Cache Memory = 32896K bytes (32896 blocks)
........... Unused Memory = 3 blocks
........... Used Memory = 32893 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 32892 blocks
................... SQL     = 32892 blocks (1 count)

현재 32K 짜리 Extent가 1024개 할당이 되었으며 그 중 SELECT /*+ result_cache */ * FROM test_rc WHERE id < 33000 ; 쿼리에 의해서 저장되어진 블록의 개수는  32,892개 이다.

그럼 이제 세션1에서 dbms_result_cache.memory_report를 수행 하고 다른 세션2~세션4까지 에서 SELECT /*+ result_cache */ * FROM test_rc WHERE id < 33000 ; 쿼리를 수행하여 Cache된 데이터를 엑세스 하려고 할 때 어떤 문제가 발생되는지 확인해 보자

DBA

exec dbms_result_cache.memory_report;


USER Sessions

SELECT /*+ result_cache */ * FROM test_rc WHERE id < 33000 ;


SELECT sid , SCHEMANAME , event , p1 , p1raw , p2 , p2raw , p3 , p3raw FROM v$session
WHERE wait_class <> 'Idle' ;

SID SCHEMA  EVENT      P1         P1RAW              P2   P2RAW             P3  P3RAW
--- --------  --------- -------- ---------------- --- ---------------- -- -------
142 BSTAR     latch free 93943432 0000000005997688 373 0000000000000175   0       00

SELECT * FROM v$latchholder ;

PID SID    LADDR      NAME                 GETS
--- ----  -------- ------------------- -----
19   170   05997688 Result Cache: Latch 83680


dbms_result_cache.memory_report를 수행하면 내부적으로 v$result_cache_memory 뷰에 대해서 엑세스를 시도 한다. Result Cache의 크기가 커 지면 이 뷰에 대한 엑세스 자체도 무거워 지게 된다.
전체 Result Cache 영역에 대한 조회를 수행하는 작업이 완료 될 때 까지 Result Cache Latch에 대하여 독점적인 사용을 하게 된다. 즉 , dbms_result_cache.memory_report나 v$result_cache_memory 뷰에 대한 조회 작업이 끝날때 까지 Result cache 영역에 대한 다른 세션의 엑세스 자체가 안된다.
 Result Cache영역의 데이터가 많아서 Result Cache 영역이 커질 경우 dbms_result_cache.memory_report , v$result_cache_memory 등의 사용에 주의 기울여야 할 것이다.


요약

_result_cache_timeout 파라메터는 Result Cache 영역안의 데이터를 엑세스 하기 위해서 세션에서 대기 할 수 있는 최대한의 시간을 설정해 놓은 파라메터 이다. (단 , 다른 세션에서 6번 모드로 락을 획득하고 데이터를 Result Cache 영역으로 로드 하고 있는 중에만 해당이 됨)
 하지만 Case#1처럼 동시에 여러 세션에서 등록되지 않은 Result Set에 대해서 접근을 시도 하거나 기존에 등록되어 있는 데이터가 변경된 후 여러 세션에서 동시 접근을 할 경우 enq : RC 이벤트를 발생 하면서 최대 설정된 시간까지 대기를 하게 된다. 이럴 경우 Result Cache 초기 목적과는 다르게 그 자체 데이터를 엑세스 하기 위해서 세션들이 경합을 하게 된다.
그렇다고 _result_cache_timeout 파라메터를 줄이게 되면 Cache 데이터를 사용하지 못하는 결과를 초래 할 수 있게 된다.
 초기 Result Cache 컨셉에 맞게 대용량 데이터를 엑세스해서 Summary 결과를 가져오는 쿼리나 많은 테이블들을 엑세스 한 후 일정한 값을 리턴하는 펑션들이 아닌 데이터가 로드 될 경우 불 필요한 경합이 발생할 소지가 높다.
 또한 Result Cache 영역이 커지게 되면 메모리 조회 하는 쿼리 자체가 일반 세션의 Result Cache영역에 대한 엑세스를 방해 하게 되므로 이 또한 신중을 기하여야 할 부분이다.
 
반응형

+ Recent posts