오라클 11g에 새로이 추가된 기능 중 Result Cache관련 latch가 두 개 있다는 것은 전 포스트에서 확인 할 수 있었다.
이번 포스트에서는 result cache: latch에 대하여 테스트를 진행해 볼 생각이다. 전 포스트에서 실제 사용하는 SQL은 하나임에도 실제 latch의 gets값을 보면 1이 아닌 좀 더 많은 수치가 증가 된 것을 확인 할 수 있었다.
왜 1이 아닌 수치가 증가를 하게 되는지 확인 해 보기로 하자. 테스트를 위하여 HR유저 및 SCOTT 유저가 v$latch 뷰에 대한 접근이 가능하도록 DBA롤을 주었으며 ,테스트를 초기화 하기 위해서 DB를 Restart 하였다. 현재의 result_cache_mode 는 MANUAL이다.
- Test Case #1
HR유저로 하나의 쿼리 결과를 Cache에 저장시키며 전,후 latch 사용 변화량 체크
사용한 스크립트 ( hr1.sql)
|
- hr1.sql
SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
SELECT /*+ result_cache */ department_id , MAX( salary ) FROM employees GROUP BY department_id ;
SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
|
|
13:42:58 SQL> connect hr/hr
Connected.
13:43:04 SQL> @hr1
NAME GETS
------------------------------ ----------
Result Cache: Latch 0
Result Cache: SO Latch 0
Elapsed: 00:00:00.01
DEPARTMENT_ID MAX(SALARY)
------------- -----------
100 12000
30 11000
7000
90 24000
20 13000
70 10000
110 12000
50 8200
80 14000
40 6500
60 9000
10 4400
12 rows selected.
Elapsed: 00:00:00.01
NAME GETS
---------------------- ----------
Result Cache: Latch 6
Result Cache: SO Latch 1
Elapsed: 00:00:00.00
|
- Test Case#2
HR 유저로 여러 개의 쿼리 결과를 Cache에 저장시키며 전,후 latch 사용 변화량 체크
사용한 스크립트(hr2.sql)
|
- hr2.sql
SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
SELECT /*+ result_cache */ department_id , MAX( salary ) FROM employees GROUP BY department_id ;
SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
SELECT /*+ result_cache */ department_id , count(*) FROM employees GROUP BY department_id ;
SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
SELECT /*+ result_cache */ count(*) FROM jobs;
SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
|
|
13:47:02 SQL> @hr2.sql
NAME GETS
--------------------- ----------
Result Cache: Latch 6
Result Cache: SO Latch 1
Elapsed: 00:00:00.02
결과생략..
12 rows selected.
Elapsed: 00:00:00.01
NAME GETS
------------------- ----------
Result Cache: Latch 9
Result Cache: SO Latch
Elapsed: 00:00:00.00
결과생략..
12 rows selected.
Elapsed: 00:00:00.00
NAME GETS
----------------- ----------
Result Cache: Latch 14
Result Cache: SO Latch 1
Elapsed: 00:00:00.00
COUNT(*)
----------
19
Elapsed: 00:00:00.01
NAME GETS
--------------------- ----------
Result Cache: Latch 20
Result Cache: SO Latch 1
Elapsed: 00:00:00.00
|
위의 두 개의 테스트 결과를 확인해 보면 없던 상태(참조 Object가 result cache에 등록되지 않은 상태)에서 최초 Result를 등록할 경우 Result Cache Latch를 6번 획득 하였다. 하지만 참조 Object가 Result cache에 등록이 되어 있는 상태에서 쿼리만 틀리게 다시 등록하는 경우 latch를 5번 획득하며 기존에 있던 result 를 사용할 경우 3번 획득 함을 확인 할 수 있다. 위의 테스트는 두번을 하였지만 hr1.sql을 수행 후 약간의 시간이 흐른 후 hr2.sql에 대해서 수행을 한 결과이다.
- Test Case#3
위의 수행 쿼리 두 개를 붙여서 수행 (hr1.sql 수행 후 바로 hr2.sql을 수행)
|
17:28:49 SQL> connect hr/hr
Connected.
17:28:59 SQL> @hr1
NAME GETS MISSES
------------------ ---------- ----------
Result Cache: Latch 0 0
Result Cache: SO Latch 0 0
Elapsed: 00:00:00.01
결과 생략..
12 rows selected.
Elapsed: 00:00:00.02
NAME GETS MISSES
------------------ ---------- ----------
Result Cache: Latch 6 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.00
17:29:01 SQL> @hr2
NAME GETS MISSES
-------------------- ---------- ----------
Result Cache: Latch 6 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.00
결과생략..
12 rows selected.
Elapsed: 00:00:00.00
NAME GETS MISSES
-------------------- ---------- ----------
Result Cache: Latch 8 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.01
결과생략..
12 rows selected.
Elapsed: 00:00:00.00
NAME GETS MISSES
------------------- ---------- ----------
Result Cache: Latch 13 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.00
COUNT(*)
----------
19
Elapsed: 00:00:00.01
NAME GETS MISSES
------------------- ---------- ----------
Result Cache: Latch 19 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.00
|
같은 작업이지만 Result Cache Latch의 획득 수치가 1 줄어 들었다. 등록관련 작업의 latch 획득은 5번 또는 6번 변화가 없지만 Result Cache안에 있는 Result를 사용하는 경우 획득 수치가 1 줄어 들은 것을 확인 할 수 있다.
정확한 내부 문서가 존재 하질 않아서 확인은 불가능 하지만 일정시간(짧은 시간)동안 Result Cache latch에 대해서 획득을 하고 있는 것 같다. 이 부분은 어디까지나 추측일 뿐이다. 아마도 result cache에 올라온 데이터는 자주 사용되는 데이터 이기 때문에 사용하던 세션이 계속 사용할 것이라는, 그래서 재 사용할 경우 latch 경합을 피하게 하기 위해서 그러지 않았을까 하는 추측만을 해 볼 수 있을 뿐이다.
- Test Case#4
Result Cache 관련 정보 확인을 위한 뷰 조회 와 latch의 관계
사용한 스크립트 (use_view_hr.sql)
|
- use_view_hr.sql
SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;
SELECT type , name , status , creation_timestamp , SCAN_COUNT FROM V$RESULT_CACHE_OBJECTS ;
SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;
SELECT /*+ result_cache */ department_id , MAX( salary ) FROM employees GROUP BY department_id ;
SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;
SELECT type , name , status , creation_timestamp , SCAN_COUNT FROM V$RESULT_CACHE_OBJECTS ;
SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;
SELECT type , name , status , creation_timestamp , SCAN_COUNT FROM V$RESULT_CACHE_OBJECTS ;
SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;
|
|
17:50:39 SQL> connect hr/hr
Connected.
17:51:44 SQL> @use_view_hr
NAME GETS MISSES
---------------------- ---------- ----------
Result Cache: Latch 0 0
Result Cache: SO Latch 0 0
Elapsed: 00:00:00.01
no rows selected
Elapsed: 00:00:00.05
NAME GETS MISSES
---------------------- ---------- ----------
Result Cache: Latch 0 0
Result Cache: SO Latch 0 0
Elapsed: 00:00:00.01
결과생략..
12 rows selected.
Elapsed: 00:00:00.01
NAME GETS MISSES
---------------------- ---------- ----------
Result Cache: Latch 6 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.00
TYPE NAME STATUS CREATION_TIM SCAN_COUNT
-------------- ----------------------- -------------- --------------- ---------------
Dependency HR.EMPLOYEES Published 04-FEB-08 0
Result SELECT /*+ result_cache */ dep Published 04-FEB-08 0
artment_id , MAX( salary ) FRO
M employees GROUP BY depart
ment_id
Elapsed: 00:00:00.00
NAME GETS MISSES
---------------------- ---------- ----------
Result Cache: Latch 7 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.00
TYPE NAME STATUS CREATION_TIM SCAN_COUNT
-------------- ----------------------- -------------- --------------- ---------------
Dependency HR.EMPLOYEES Published 04-FEB-08 0
Result SELECT /*+ result_cache */ dep Published 04-FEB-08 0
artment_id , MAX( salary ) FRO
M employees GROUP BY depart
ment_id
Elapsed: 00:00:00.00
NAME GETS MISSES
---------------------- ---------- ----------
Result Cache: Latch 8 0
Result Cache: SO Latch 1 0
Elapsed: 00:00:00.00
|
최초에 Result Cache에 아무 데이터도 없는 상태에서 관련 뷰에 대한 엑세스를 할 경우 latch에 대한 사용이 없으나 실제 Result cache에 데이터가 있는 경우 관련 정보를 확인 하기 위하여 view를 엑세스 하여도 latch에 대한 gets값이 증가 함을 확인 할 수 있다.
관련 뷰에 대한 정보를 확인하는데도 result cache latch에 대한 획득이 필요하다면 latch 경합이 의외로 많을 수 있다고 생각이 되는데 왜 latch에 대한 경합이 있어야 관련 뷰를 조회 할 수 있는지에 대해서도 알려진 바가 없다.
-Test Case#5
여러 세션에서 동시에 같은 Result Cache에 있는 데이터를 엑세스 할 경우 v$session_wait뷰에서는 어떤 이벤트 들을 대기 하는지 확인
사용한 스크립트 (pl_sql.sql)
|
- pl_sql.sql
DECLARE
v_cnt number(3) := 0 ;
BEGIN
FOR i IN 1..10000000 LOOP
SELECT /*+ result_cache */ count(*)
INTO v_cnt
FROM jobs;
END LOOP ;
END;
/
|
세 개의 세션에서 각각 pl_sql.sql을 수행 후 다음 쿼리를 이용하여 session_wait을 모니터링 함.
|
SQL>SELECT sid , EVENT , P1 , P1RAW , P2 ,P2RAW,P3 ,P3RAW FROM v$session_wait WHERE wait_class <> 'Idle' ;
SID EVENT P1 P1RAW P2 P2RAW P3 P3RAW
---- ----------- --------- -------------------- ----- ---------------- ---- ----------------
124 latch free 536975932 0000000020019A3C 373 0000000000000175 0 00
128 latch free 536975932 0000000020019A3C 373 0000000000000175 0 00
136 latch free 536975932 0000000020019A3C 373 0000000000000175 0 00
3 rows
|
위의 쿼리 결과를 확인 해 보면 세 개의 세션이 latch free 이벤트를 대기 하고 있다. v$event_name뷰를 조회해 보면 특별히 Result Cache 관련 이벤트가 나누어져 있지는 않다. 즉, 기존에 사용하던 latch free 이벤트를 같이 사용하고 있는 것이다.
실제 wait하고 있는 latch가 result cache 관련 latch인지 확인 해 보기 위해서는 P1RAW값을 가지고 v$latch 뷰를 조회 하면 된다.
|
SQL>SELECT NAME , gets , misses , sleeps , spin_gets FROM v$latch where addr = '20019A3C' ;
NAME GETS MISSES SLEEPS SPIN_GETS
------------------------------ ----------- ---------- ------------- -----------------
Result Cache: Latch 80000062 21124 1083 20127
1 row
|
위의 다섯 가지 테스트를 종합해 보면 result cache관련 latch가 두 개 존재 하며 , SO latch는 세션이 result cache에 접근을 시도 할 경우 1이 증가 한다는 것을 확인 할 수 있으며, So latch가 아닌 일반 latch는 result cache에 접근하고자 하는 세션에서 획득 하여야 하며 result cache관련 정보를 조회하기 위해서도 획득해야 함을 확인 할 수 있었다.