Slow Performance with High 'latch free' on 'ges value block free list'
(Doc ID 1664425.1)
Last updated on MARCH 29, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
The database performance is slow with high "latch free" waits on "ges value block free list", per AWR report:
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
latch free 34,061 17,374 510 54.59 Other <===
resmgr:cpu quantum 252,146 5,743 23 18.04 Scheduler
latch: shared pool 16,118 2,226 138 6.99 Concurrency
rdbms ipc reply 134,092 1,576 12 4.95 Other
DB CPU 1,283 4.03
Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss
ges caches resource lists 505,253 0.26 0.78 221 267,069 0.49
ges resource hash list 9,817,950 0.21 0.10 337 122,156 0.35
ges resource scan list 5,235 0.00 0 0
ges resource table freelist 213,866 0.03 0.62 7 0
ges timeout list 89,602 0.04 0.06 0 7,828 0.01
ges value block free list 2,729,070 1.79 0.65 16134 0 <====
Latch Miss Sources
Latch Name Where NoWait Misses Sleeps Waiter Sleeps
ges value block free list kjvblkfr: free 0 18,209 10,641 <====
ges value block free list kjvblkgrow: allocation 0 12,772 20,339 <====
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
latch free 34,061 17,374 510 54.59 Other <===
resmgr:cpu quantum 252,146 5,743 23 18.04 Scheduler
latch: shared pool 16,118 2,226 138 6.99 Concurrency
rdbms ipc reply 134,092 1,576 12 4.95 Other
DB CPU 1,283 4.03
Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss
ges caches resource lists 505,253 0.26 0.78 221 267,069 0.49
ges resource hash list 9,817,950 0.21 0.10 337 122,156 0.35
ges resource scan list 5,235 0.00 0 0
ges resource table freelist 213,866 0.03 0.62 7 0
ges timeout list 89,602 0.04 0.06 0 7,828 0.01
ges value block free list 2,729,070 1.79 0.65 16134 0 <====
Latch Miss Sources
Latch Name Where NoWait Misses Sleeps Waiter Sleeps
ges value block free list kjvblkfr: free 0 18,209 10,641 <====
ges value block free list kjvblkgrow: allocation 0 12,772 20,339 <====
The application is doing a lot of distributed transactions via dblink. lmd trace file shows heavy DX lock requests:
grep DX <sid>_lmd0_2320.trc
----------------------
resname : [0x7ec6c466][0x0],[DX][ext 0x0,0x0]
resname : [0x7ec6c466][0x0],[DX][ext 0x0,0x0]
resname : [0x883aba56][0x0],[DX][ext 0x0,0x0]
resname : [0x883aba56][0x0],[DX][ext 0x0,0x0]
resname : [0x9cc8d348][0x0],[DX][ext 0x0,0x0]
resname : [0x9cc8d348][0x0],[DX][ext 0x0,0x0]
resname : [0xf9e8a07e][0x0],[DX][ext 0x0,0x0]
resname : [0xf9e8a07e][0x0],[DX][ext 0x0,0x0]
resname : [0x75f90a52][0x0],[DX][ext 0x0,0x0]
resname : [0x75f90a52][0x0],[DX][ext 0x0,0x0]
....
----------------------
resname : [0x7ec6c466][0x0],[DX][ext 0x0,0x0]
resname : [0x7ec6c466][0x0],[DX][ext 0x0,0x0]
resname : [0x883aba56][0x0],[DX][ext 0x0,0x0]
resname : [0x883aba56][0x0],[DX][ext 0x0,0x0]
resname : [0x9cc8d348][0x0],[DX][ext 0x0,0x0]
resname : [0x9cc8d348][0x0],[DX][ext 0x0,0x0]
resname : [0xf9e8a07e][0x0],[DX][ext 0x0,0x0]
resname : [0xf9e8a07e][0x0],[DX][ext 0x0,0x0]
resname : [0x75f90a52][0x0],[DX][ext 0x0,0x0]
resname : [0x75f90a52][0x0],[DX][ext 0x0,0x0]
....
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |