반응형

오라클 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관련 정보를 조회하기 위해서도 획득해야 함을 확인 할 수 있었다.

반응형

+ Recent posts