Result Cache 기능은 Oracle 11g에서 내 놓은 획기적인 기능 중에 한 가지이다.
많은 문서들에서 그 기능을 이야기 해 놓았겠지만 다시 한번 간략하게 설명을 하자면 자주 사용되는 SQL문 및 펑션 결과를 Result Cahe에 등록시켜 놓음으로 동일 SQL 문 이나 펑션 수행 시 그 결과를 바로 메모리에서 가져 올 수 있게 하는 기능이다
앞으로 몇 개의 글을 통해서 Result Cache에 대해서 알아 볼 생각이다.
메모리 상에 어디에 존재 하는지 ? 어떻게 관리 되는지 ? 이런 부분에 대한 궁금증을 가지고 많은 문서 및 웹사이트에 대한 서핑을 진행 하였는데 아직까지는 이렇다 할 궁금증을 해결해줄 문서나 웹사이트를 찾지를 못하였다.
그래서 무대포 테스트를 진행해 볼 생각이다. 테스트 자체도 기초 관련문서가 없어서 어느 정도 시도는 해 보겠지만 수박 겉 핡기 정도에 그치지 않을까 하는 불안감을 감출 수 없다.
그럼 하나하나 확인해 보도록 하자
2007년 4월 작성된 오라클 문서 (It's All in the Cache An Oracle white paper April 2007) 에 의하면 Result Cache는 RDBMS가 아닌 다른 프로그램을 이용 하여서 자동으로 쿼리 결과를 확인해서 DBMS에게 전달해 준다고 한다. 어떤 프로세스가 어떻게 사용되고 있는지는 좀 더 테스트를 진행해 보아야겠지만 아직까지는 알아내지 못했다 --;
Result Cache관련 파라메터가 생김으로써 Shared Pool안에서 크기를 지정해 줄 수 있고, 관리 할 수 있는 부분이 하나 생기게 되었다. 물론 이 부분은 SGA_TARGET이나 MEMORY_TARGET을 사용하더라도 그 크기가 보장이 되고, 자체 Result Cache 안에서 LRU 알고리즘에 의해 캐시 되거나 Age Out이 된다.
v$sgastat뷰를 이용하여 Shared Pool안에서 사용되는 Result Cache 영역에 대해서 확인 해 보도록 하자
|
SQL>SELECT * FROM v$sgastat WHERE pool = 'shared pool' AND name like 'Result%' ;
POOL NAME BYTES
------------ ----------------------------- ---------
shared pool Result Cache: State Objs 2852
shared pool Result Cache 149548
shared pool Result Cache: Memory Mgr 124
shared pool Result Cache: Bloom Fltr 2048
shared pool Result Cache: Cache Mgr 108
5 rows
|
위의 쿼리 수행결과에서 알 수 있듯이 Shared Pool 영역 안에서 Result Cache관련 부분이 5군데 목격이 된다. 각각이 어떤 역할을 담당하는지는 아직 알 수가 없다. 단지 위의 결과를 통해서 Shared Pool 영역 안에 Result Cache가 존재 한다는 사실 정도만 확인 가능 할 뿐이다.
Result Cache가 Shared Pool영역에 존재를 하게 되면 이를 관리 할 latch도 있을 것이다 . 11g 에 추가된 latch를 확인해 봄으로써 어떤 Latch들이 Result Cache 와 연관이 있는지 확인 해 보자.
|
SQL>SELECT * FROM v$latchname WHERE name LIKE 'Result%' ;
LATCH# NAME HASH
------- -------------------- --------------
373 Result Cache: Latch 1545889529
374 Result Cache: SO Latch 986859868
2 rows
|
위의 쿼리 결과를 보면 두 개의 Result Cache관련 Latch가 확인이 된다. v$latch_parent , v$latch_children을 조회 해 보면 Result Cache관련 latch들은 Child 래치가 없다는 것을 추가적으로 확인 할 수 있다.
|
SQL>SELECT latch#, name FROM v$latch_parent WHERE name like 'Result%';
LATCH# NAME
--------- ------------------------------
373 Result Cache: Latch
374 Result Cache: SO Latch
2 rows
SQL>SELECT * FROM v$latch_children WHERE name like 'Result%' ;
0 rows
|
실제 해당 Latch들이 사용이 되는지 확인 해 보도록 하자
테스트 환경 노트북이며 자세한 내용은 다음과 같다 .
|
O/S : ASIANUX 2.0
DB : Oracle 11.0.1.6
Test Schema : HR
- Parameter
memory_max_target 314572800
memory_target 314572800
sga_max_size 314572800
sga_target 4194304
result_cache_mode MANUAL
result_cache_max_size 3375104
result_cache_max_result 5
|
result_cache 힌트를 사용하여 쿼리 결과를 등록 시킨 후에 쿼리를 재 수행 했을 때 latch가 사용되는 것을 확인해 보자.
|
sys user |
SQL>SELECT NAME , SCAN_COUNT FROM v$result_cache_objects WHERE name = 'SELECT /*+ result_cache */ count(*) FROM employees ' ;
NAME SCAN_COUNT
--------------------------------------- ----------------
SELECT /*+ result_cache */ count(*) FROM 1
1 row
SQL>SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
NAME GETS
-------------------- --------------
Result Cache: Latch 265
Result Cache: SO Latch 5
2 rows |
|
HR user |
SQL>SELECT /*+ result_cache */ count(*) FROM employees ; |
|
sys user |
SQL>SELECT NAME , SCAN_COUNT FROM v$result_cache_objects WHERE name = 'SELECT /*+ result_cache */ count(*) FROM employees ' ;
NAME SCAN_COUNT
--------------------------------------- ----------------
SELECT /*+ result_cache */ count(*) FROM 2
1 row
SQL>SELECT NAME , GETS FROM v$latch WHERE name like 'Result%' ;
NAME GETS
-------------------- --------------
Result Cache: Latch 269
Result Cache: SO Latch 5
2 rows |
위의 테스트 결과 한 번 재 수행 하였음에도 Result Cache : Latch 는 4번 사용이 되었으며 Result Cache : SO Latch는 사용되지 않았다. Result Cache:SO Latch는 세션을 바꾸어서 동일 SQL 처음 수행시에 2가 증가 하는 것을 할 수 있었다.
이번에는 메모리를 어떻게 사용하는지 확인해 보자.
hr 유저로 SELECT /*+ result_cache */ job_id , job_title FROM jobs ; 문을 수행하여 쿼리 결과를 Result Cache에 저장 한 후 SGA에 대한 덤프를 수행하고 해당 메모리 주소에 대하여 덤프를 수행해 보았다.
|
SQL>SELECT /*+ result_cache */ job_id , job_title FROM jobs ;
JOB_ID JOB_TITLE
---------- -----------------------------------
AD_PRES President
AD_VP Administration Vice President
AD_ASST Administration Assistant
FI_MGR Finance Manager
FI_ACCOUNT Accountant
AC_MGR Accounting Manager
AC_ACCOUNT Public Accountant
SA_MAN Sales Manager
SA_REP Sales Representative
PU_MAN Purchasing Manager
PU_CLERK Purchasing Clerk
ST_MAN Stock Manager
ST_CLERK Stock Clerk
SH_CLERK Shipping Clerk
IT_PROG Programmer
MK_MAN Marketing Manager
MK_REP Marketing Representative
HR_REP Human Resources Representative
PR_REP Public Relations Representative
19 rows
|
|
SQL> connect / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump heapdump 7
|
|
*** 2008-02-01 16:44:42.925
Processing Oradebug command 'dump heapdump 7'
KGH Latch Directory Information
ldir state: 2 last allocated slot: 87
Slot [ 1] Latch: 0x2000830c Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 2] Latch: 0x31c7b228 Index: 1 Flags: 3 State: 2 next: (nil)
…중간 생략
******************************************************
HEAP DUMP heap name="sga heap(1,1)" desc=0x20024590
extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x400000 heap=(nil)
fl2=0x20, nex=(nil)
EXTENT 0 addr=0x2d000000
Chunk 2d000038 sz= 24 R-freeable "reserved stoppe"
Chunk 2d000050 sz= 212888 R-free " "
Chunk 2d033fe8 sz= 24 R-freeable "reserved stoppe"
Chunk 2d034000 sz= 786360 free " "
Chunk 2d0f3fb8 sz= 32816 freeable "Result Cache " ds=0x2fac276c
Chunk 2d0fbfe8 sz= 796 recreate "KGL handles " latch=(nil)
… 중간생략
Chunk 2d234734 sz= 540 recreate "KQR PO " latch=0x2f136280
Chunk 2d234950 sz= 540 recreate "KQR SO " latch=(nil)
Chunk 2d234b6c sz= 32816 freeable "Result Cache " ds=0x2fac276c
Chunk 2d23cb9c sz= 32816 freeable "Result Cache " ds=0x2fac276c
Chunk 2d244bcc sz= 32816 freeable "Result Cache " ds=0x2fac276c
Chunk 2d24cbfc sz= 2060 freeable "modification "
Chunk 2d24d408 sz= 2060 freeable "modification "
... 생략
|
ds=0x2fac276c 주소값에 대해서 다시 heapdump를 수행해 보자 .
|
SQL> connect / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump heapdump_addr 10 799811436
799811436 는 0x2fac276c 를 10진수로 변환 한 값이다
|
|
*** 2008-02-01 16:55:44.914 Processing Oradebug command 'dump
heapdump_addr 10 799811436'
******************************************************
HEAP DUMP heap name="Result Cache" desc=0x2fac276c
extent sz=0x8024 alt=32767 het=32767 rec=0 flg=2 opc=2
parent=0x200010b4 owner=(nil) nex=(nil) xsz=0x8024 heap=(nil)
fl2=0x20, nex=(nil)
EXTENT 0 addr=0x2d0f3fc4
Chunk 2d0f3fcc sz= 32796 perm "perm " alo=32784
Dump of memory from 0x2D0F3FCC to 0x2D0FBFE8
2D0F3FC0 5000801D [...P]
…중간 생략
Repeat 57 times
2D235790 00000003 2D235790 00000000 00000002 [.....W#-........]
2D2357A0 2D244340 2D244340 2D235FA8 2D234FA8 [@C$-@C$-._#-.O#-]
2D2357B0 00000001 41935EF0 2EB66363 0137FD7B [.....^.Acc..{.7.]
2D2357C0 00000000 00000000 00000052 01026C78 [........R...xl..]
2D2357D0 0035350F 00000000 00000000 00000000 [.55.............]
2D2357E0 00000000 00000000 00E00002 00000002 [................]
2D2357F0 00000001 00000013 0000002A 00000015 [........*.......]
2D235800 0000001D 00000001 00000000 00000001 [................]
2D235810 180E4797 611AE523 856DF5D2 A7F2920E [.G..#..a..m.....]
2D235820 C9DA6921 F7B759DB 1E0022B3 FF8113BF [!i...Y..."......]
2D235830 00000000 2D235844 00000038 0001127E [....DX#-8...~...]
2D235840 2D235390 454C4553 2F205443 72202B2A [.S#-SELECT /*+ r]
2D235850 6C757365 61635F74 20656863 6A202F2A [esult_cache */ j]
2D235860 695F626F 202C2064 5F626F6A 6C746974 [ob_id , job_titl]
2D235870 52462065 6A204D4F 2073626F 44410007 [e FROM jobs ..AD]
2D235880 4552505F 50000953 69736572 746E6564 [_PRES..President]
2D235890 41000500 50565F44 6441001D 696E696D [...AD_VP..Admini]
2D2358A0 61727473 6E6F6974 63695620 72502065 [stration Vice Pr]
2D2358B0 64697365 00746E65 44410007 5353415F [esident...AD_ASS]
2D2358C0 41001854 6E696D64 72747369 6F697461 [T..Administratio]
2D2358D0 7341206E 74736973 00746E61 49460006 [n Assistant...FI]
2D2358E0 52474D5F 6946000F 636E616E 614D2065 [_MGR..Finance Ma]
2D2358F0 6567616E 000A0072 415F4946 554F4343 [nager...FI_ACCOU]
2D235900 000A544E 6F636341 61746E75 0600746E [NT..Accountant..]
2D235910 5F434100 1252474D 63634100 746E756F [.AC_MGR..Account]
2D235920 20676E69 616E614D 00726567 4341000A [ing Manager...AC]
2D235930 4343415F 544E554F 75500011 63696C62 [_ACCOUNT..Public]
2D235940 63634120 746E756F 00746E61 41530006 [ Accountant...SA]
2D235950 4E414D5F 6153000D 2073656C 616E614D [_MAN..Sales Mana]
2D235960 00726567 41530006 5045525F 61530014 [ger...SA_REP..Sa]
2D235970 2073656C 72706552 6E657365 69746174 [les Representati]
2D235980 06006576 5F555000 124E414D 72755000 [ve...PU_MAN..Pur]
2D235990 73616863 20676E69 616E614D 00726567 [chasing Manager.]
2D2359A0 55500008 454C435F 00104B52 63727550 [..PU_CLERK..Purc]
2D2359B0 69736168 4320676E 6B72656C 53000600 [hasing Clerk...S]
2D2359C0 414D5F54 53000D4E 6B636F74 6E614D20 [T_MAN..Stock Man]
… 생략
|
위의 결과를 보면 메모리 상에 수행 SQL문과 실제 데이터가 같이 저장이 됨을 확인 할 수 있다.
수행 결과가 메모리에 저장이 되므로 DB를 Restart하게 되면 메모리가 초기화 됨과 동시에 Result Cache는 비어 지게 된다.