Earlier, I blogged about resolving cache buffers chains latch contention in my earlier entry , in which, root cause was excessive index access due to Nested Loops join. Recently, we resolved another similar issue.
Problem
CPU usage was very high in production database server in user% mode. Dynamic performance view v$session_wait indicated excessive waits for latch contention. Output from a script wait_details.sql (scripts available at the end of this post) shows that many sessions were waiting for ‘latch free’ event. Also, address for these latch children are the same, meaning all these sessions are trying to access one latch children.
SQL> @wait_details
SID PID EVENT USERNAME STATE WAIT_TIME WIS P1_P2_P3_TEXT
------ ------- ------------- --------- ---------- ------------------- --------- ----- -----------------------------------
91 24242 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 0
101 4884 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 0
116 23899 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 0
187 19499 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 0
108 23498 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 3
194 23701 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 0
202 26254 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 4
220 23274 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 0
227 23643 latch free CSTMOP WAITED KNOWN TIME 2 0 address 69476807024-number 98-tries 0
331 26519 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 0
297 23934 latch free CSTMOP WAITING 0 0 address 69476807024-number 98-tries 3
....
We can identify SQL causing latch contention querying v$session_wait. From the output below, SQL with hash_value 1509082258 is suspicious since there are many sessions executing that SQL and waiting / waited recently for ‘latch free’ event.
select substr(w.event, 1, 28 ) event, sql_hash_value, count(*)
from v$session_wait w, v$session s, v$process p
where s.sid=w.sid
and p.addr = s.paddr
and s.username is not null
and event not like '%pipe%'
and event not like 'SQL*%'
group by substr(w.event, 1, 28), sql_hash_value
;
EVENT SQL_HASH_VALUE COUNT(*)
------------------------------ -------------- ----------
enqueue 3740270 1
enqueue 747790152 1
enqueue 1192921796 1
latch free 622474477 3
latch free 1509082258 58 <---
latch free 1807800540 1
global cache null to x 3740270 1
global cache null to x 1473456670 1
global cache null to x 3094935671 1
db file sequential read 109444956 1
Mapping to object_name
Keep Reading