If the sessions remain connected for a long time, then the output from the query of the following would be helpful in determining the sessions that have high waits for the latch free event
SQL> SELECT sid, substr(event,1,12) "Event",
2 time_waited, round(average_wait,2) "Average"
3 FROM v$session_event
4 WHERE event ='latch free'
5 AND time_waited != 0
6 ORDER BY time_waited DESC;
Determine which latch has the greatest impact
SQL> SELECT latch#, substr(name,1,25) "Latch", sleeps
2 FROM v$latch
3 WHERE sleeps!=0
4 ORDER BY sleeps desc;
Collect information for the corresponding child latches.
SQL> SELECT addr, gets, misses, sleeps, spin_gets, sleep1,
2 sleep2, sleep3
3 FROM v$latch_children
4 WHERE latch#=97
5 ORDER BY sleeps DESC;
Find out which buffer makes use of the “hot” latch
SQL> SELECT hladdr, dbarfil, dbablk, class, state, TCH
2 FROM x$bh
3 WHERE hladdr IN
4 ('8025A108','802514C4','80259CA0')
5 ORDER BY TCH DESC;
Note: x$bh has a column called obj that can be mapped to the object_id column of the dba_objects view.
When issuing the above query, consider capturing the same query into multiple tables at different time intervals and then comparing the results. This will identify the truly “hot” blocks over that time interval, and not just at a particular instant.
Identify the segments that are associated with these blocks:
SQL> SELECT owner, segment_name "Name", segment_type "Type"
2 FROM dba_extents
3 WHERE file_id = 2
4 AND block_id <= 293
5 AND block_id + blocks > 293;
总结:
•
• Monitor latches at higher levels first and focus on the latches with the biggest impact.
• If it is a solitary latch, then reduce the load on it.
• If contention is on a child latch, then:
– Identify the hot resource
– Eliminate or reduce the load on the hot resource
• Advanced Monitoring
– Tune spin count
– Tune latch wait posting
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9375/viewspace-607687/
，如需转载，请注明出处，否则将追究法律责任。
