Result Cache를 사용할 때와 사용하지 않을 때 사용하는 래치는 어떤 차이가 있는지 확인해 보자
시나리오는 간단하다 . DB를 재 기동 후 v$latch 의 정보를 저장해 놓은 후 쿼리 수행 전 후의 래치 사용량을 비교 하는 것이다.
- Test Case#1
일반 쿼리 수행 시 쿼리 수행 전/후 의 래치 사용량 비교
|
- Normal.sql
-- Before
SELECT name ||','|| to_char(gets) FROM v$latch order by name ;
-- Execute Query
SELECT count(*) FROM test_rc ;
-- After
SELECT name ||','|| to_char(gets) FROM v$latch order by name ;
exit;
sqlplus hr/hr @Normal.sql >> Normal.txt
|
다음 결과는 쿼리 수행 전/후의 래치 사용량 변화가 있는 래치를 나타낸 것이다.
|
ASM map operation hash table
DML lock allocation
Event Group Locks
FOB s.o list latch
File State Object Pool Parent Latch
IPC stats buffer allocation latch
In memory undo latch
JS Sh mem access
JS queue access latch
JS slv state obj latch
KFC FX Hash Latch
KFC Hash Latch
KFCL LE Freelist
KFR redo allocation latch
KGNFS-NFS:SHM structure
KGNFS-NFS:SVR LIST
KJC message pool free list
KJCT flow control latch
Locator state objects pool parent latch
Memory Management Latch
Memory Queue
Memory Queue Message Subscriber #1
Memory Queue Message Subscriber #2
Memory Queue Message Subscriber #3
Memory Queue Message Subscriber #4
Memory Queue Subscriber
MinActiveScn Latch
Mutex
Mutex Stats
OS process
QMT
SGA blob parent
SGA bucket locks
SGA heap locks
SGA pool locks
SQL memory manager workarea list latch
Shared B-Tree
Streams Generic
Testing
Token Manager
Write State Object Pool Parent Latch
XDB NFS Security Latch
XDB unused session pool
XDB used session pool
buffer pool
business card
cache buffers chains
cache buffers lru chain
cache table scan latch
cas latch
change notification client cache latch
channel handle pool latch
channel operations parent latch
checkpoint queue latch
client/application info
commit callback allocation
cp cmon/server latch
cp pool latch
cp server hash latch
cv apply list lock
cv free list lock
done queue latch
dummy allocation
enqueue hash chains
enqueues
fifth spare latch
flashback copy
gc element
gcs commit scn state
gcs partitioned table hash
gcs pcm hashed value bucket hash
gcs resource freelist
gcs resource hash
gcs resource scan list
gcs shadows freelist
ges domain table
ges enqueue table freelist
ges group table
ges process hash list
ges process parent latch
ges resource hash list
ges resource scan list
ges resource table freelist
ges value block free list
global tx hash mapping
granule operation
intra txn parallel recovery
io pool granule metadata list
job workq parent latch
k2q lock allocation
kdlx hb parent latch
kgb parent
kks stats
ksfv messages
kss move lock
ksv class latch
ksv msg queue latch
library cache load lock
lob segment dispenser latch
lob segment hash table latch
lob segment query latch
lock DBA buffer during media recovery
logical standby cache
logminer context allocation
logminer work area
longop free list parent
mapped buffers lru chain
message pool operations parent latch
msg queue latch
multiblock read objects
name-service namespace bucket
object queue header heap
object queue header operation
object stats modification
parallel query alloc buffer
parallel query stats
peshm
pesom_free_list
pesom_hash_node
pesom_heap_alloc
post/wait queue
process queue
process queue reference
recovery domain hash list
redo allocation
redo copy
resmgr:actses change group
resmgr:actses change state
resmgr:plan CPU method
resmgr:resource group CPU method
resmgr:session queuing
rm cas latch
row cache objects
second spare latch
session allocation
session idle bit
session queue latch
session switching
shared pool
shared pool sim alloc
shared pool simulator
sim partition latch
simulator hash latch
simulator lru latch
tablespace key chain
test excl. parent l0
test excl. parent2 l0
third spare latch
transaction allocation
undo global data
virtual circuit buffers
virtual circuit holder
virtual circuit queues
총 153개의 latch가 사용됨
|
- Test Case#2
최초 Result Cache 등록 전/후의 래치 사용량 비교
|
- Regist_Result.sql
SELECT name ||','|| to_char(gets) FROM v$latch order by name ;
-- Execute Query
SELECT /*+ result_cache */ count(*) FROM test_rc ;
-- After
SELECT name ||','|| to_char(gets) FROM v$latch order by name ;
exit;
sqlplus hr/hr @Regist_Result.sql >> Regist_Result.txt
|
다음은 전/후의 변화가 있는 래치 목록이다.
|
ASM map operation hash table
DML lock allocation
Event Group Locks
FOB s.o list latch
File State Object Pool Parent Latch
IPC stats buffer allocation latch
In memory undo latch
JS Sh mem access
JS queue access latch
JS slv state obj latch
KFC FX Hash Latch
KFC Hash Latch
KFCL LE Freelist
KFR redo allocation latch
KGNFS-NFS:SHM structure
KGNFS-NFS:SVR LIST
KJC message pool free list
KJCT flow control latch
Locator state objects pool parent latch
Memory Management Latch
Memory Queue
Memory Queue Message Subscriber #1
Memory Queue Message Subscriber #2
Memory Queue Message Subscriber #3
Memory Queue Message Subscriber #4
Memory Queue Subscriber
MinActiveScn Latch
Mutex
Mutex Stats
OS process
QMT
Result Cache: Latch
Result Cache: SO Latch
SGA blob parent
SGA bucket locks
SGA heap locks
SGA pool locks
SQL memory manager workarea list latch
Shared B-Tree
Streams Generic
Testing
Token Manager
Write State Object Pool Parent Latch
XDB NFS Security Latch
XDB unused session pool
XDB used session pool
buffer pool
business card
cache buffers chains
cache buffers lru chain
cache table scan latch
cas latch
change notification client cache latch
channel handle pool latch
channel operations parent latch
checkpoint queue latch
client/application info
commit callback allocation
cp cmon/server latch
cp pool latch
cp server hash latch
cv apply list lock
cv free list lock
done queue latch
dummy allocation
enqueue hash chains
enqueues
fifth spare latch
flashback copy
gc element
gcs commit scn state
gcs partitioned table hash
gcs pcm hashed value bucket hash
gcs resource freelist
gcs resource hash
gcs resource scan list
gcs shadows freelist
ges domain table
ges enqueue table freelist
ges group table
ges process hash list
ges process parent latch
ges resource hash list
ges resource scan list
ges resource table freelist
ges value block free list
global tx hash mapping
granule operation
intra txn parallel recovery
io pool granule metadata list
job workq parent latch
k2q lock allocation
kdlx hb parent latch
kgb parent
kks stats
ksfv messages
kss move lock
ksv class latch
ksv msg queue latch
library cache load lock
lob segment dispenser latch
lob segment hash table latch
lob segment query latch
lock DBA buffer during media recovery
logical standby cache
logminer context allocation
logminer work area
longop free list parent
mapped buffers lru chain
message pool operations parent latch
msg queue latch
multiblock read objects
name-service namespace bucket
object queue header heap
object queue header operation
object stats modification
parallel query alloc buffer
parallel query stats
peshm
pesom_free_list
pesom_hash_node
pesom_heap_alloc
post/wait queue
process queue
process queue reference
recovery domain hash list
redo allocation
redo copy
resmgr:actses change group
resmgr:actses change state
resmgr:plan CPU method
resmgr:resource group CPU method
resmgr:session queuing
rm cas latch
row cache objects
second spare latch
session allocation
session idle bit
session queue latch
session switching
shared pool
shared pool sim alloc
shared pool simulator
sim partition latch
simulator hash latch
simulator lru latch
tablespace key chain
test excl. parent l0
test excl. parent2 l0
third spare latch
transaction allocation
undo global data
virtual circuit buffers
virtual circuit holder
virtual circuit queues
155개의 래치가 사용 되었다.
|
- Test Case#3
등록된 Result Cache를 이용한 경우의 전/후 래치 사용량 비교
|
- Reuse_Result.sql
-- Before
SELECT name ||','|| to_char(gets) FROM v$latch order by name ;
-- Execute Query
SELECT /*+ result_cache */ count(*) FROM test_rc ;
-- After
SELECT name ||','|| to_char(gets) FROM v$latch order by name ;
exit;
sqlplus hr/hr @ Reuse_Result.sql >> Reuse_Result.txt
|
다음은 수행 전/후의 래치 사용량 변화가 있는 부분이다.
|
ASM map operation hash table
buffer pool
business card
cache buffers chains
cache buffers lru chain
cas latch
change notification client cache latch
channel handle pool latch
channel operations parent latch
checkpoint queue latch
client/application info
commit callback allocation
cp cmon/server latch
cp pool latch
cp server hash latch
cv apply list lock
cv free list lock
DML lock allocation
done queue latch
dummy allocation
enqueue hash chains
Event Group Locks
fifth spare latch
File State Object Pool Parent Latch
flashback copy
gc element
gcs commit scn state
gcs partitioned table hash
gcs pcm hashed value bucket hash
gcs resource freelist
gcs resource hash
gcs resource scan list
gcs shadows freelist
ges domain table
ges enqueue table freelist
ges group table
ges process hash list
ges process parent latch
ges resource hash list
ges resource scan list
ges resource table freelist
ges value block free list
global tx hash mapping
granule operation
In memory undo latch
intra txn parallel recovery
io pool granule metadata list
IPC stats buffer allocation latch
job workq parent latch
JS queue access latch
JS Sh mem access
JS slv state obj latch
k2q lock allocation
kdlx hb parent latch
KFC FX Hash Latch
KFC Hash Latch
KFCL LE Freelist
KFR redo allocation latch
kgb parent
KGNFS-NFS:SHM structure
KGNFS-NFS:SVR LIST
KJC message pool free list
KJCT flow control latch
kks stats
ksfv messages
kss move lock
ksv class latch
ksv msg queue latch
lob segment dispenser latch
lob segment hash table latch
lob segment query latch
Locator state objects pool parent latch
lock DBA buffer during media recovery
logical standby cache
logminer context allocation
logminer work area
longop free list parent
mapped buffers lru chain
Memory Management Latch
Memory Queue
Memory Queue Message Subscriber #1
Memory Queue Message Subscriber #2
Memory Queue Message Subscriber #3
Memory Queue Message Subscriber #4
Memory Queue Subscriber
message pool operations parent latch
MinActiveScn Latch
msg queue latch
Mutex
Mutex Stats
name-service namespace bucket
object queue header heap
object queue header operation
OS process
parallel query alloc buffer
parallel query stats
peshm
pesom_free_list
pesom_hash_node
pesom_heap_alloc
post/wait queue
process queue
process queue reference
QMT
recovery domain hash list
redo allocation
redo copy
resmgr:actses change group
resmgr:actses change state
resmgr:plan CPU method
resmgr:resource group CPU method
resmgr:session queuing
Result Cache: Latch
Result Cache: SO Latch
rm cas latch
row cache objects
second spare latch
session idle bit
session queue latch
session switching
SGA blob parent
SGA bucket locks
SGA heap locks
SGA pool locks
Shared B-Tree
shared pool
shared pool sim alloc
shared pool simulator
sim partition latch
simulator hash latch
simulator lru latch
SQL memory manager workarea list latch
Streams Generic
tablespace key chain
test excl. parent l0
test excl. parent2 l0
testing
third spare latch
Token Manager
transaction allocation
undo global data
virtual circuit buffers
virtual circuit holder
virtual circuit queues
Write State Object Pool Parent Latch
XDB NFS Security Latch
XDB unused session pool
XDB used session pool
총 148개의 래치가 사용되었다.
|
- 비교#1
|
- 일반 쿼리 수행과 Result Cache에 최초 등록 시 추가적으로 사용된 래치
Result Cache : Latch , Result Cache : SO Latch
|
비교#1의 결과를 보면 일반 쿼리 수행 시와 동일한 래치들이 사용이 되지만 추가적인 Result 관련 래치가 사용이 되고 있다.
이는 SQL문 수행 결과를 메모리에 캐싱 하기 위해 추가적으로 래치가 사용되었음을 확인 할 수 있다.
- 비교#2
|
- 일반 쿼리 수행과 Result Cache에 등록 된 Result 사용시 변화가 있는 래치
- 추가적으로 사용된 래치
Result Cache : Latch , Result Cache : SO Latch , Messages
|
Result Cache를 사용할 경우 library cache 래치의 사용유무를 확인해 보면 확인이 될 것이라는 생각에 시작했던 테스트였다. 단순 latch 사용 유,무 만 가지고서는 확인이 힘든 부분이 확인 되었다.
이 부분에 대해서는 좀더 많은 테스트를 해 보아야 할 것 같다.
단, 다시 한번 확인 한 것은 Result Cache를 사용하기 위해서는 Result Cache : Latch 를 획득 해야 한다는 사실이다.