In this recipe we will see how to identify the resources that need to be tuned to minimize latch contention.
In this recipe we will present how to diagnose a latch contention problem using a Statspack report, obtained with the procedures used in Chapter 1.
To identify the resources to be tuned to avoid latch contention we need to:
Execute a Statspack report.
See in the Instance Efficiency Indicators section the value for Latch Hit %.
See in the Top 5 Timed Events section if there is a latch free wait event.
Verify the Latch Activity section to identify the kind of latches that are eventually experiencing misses (and sleeps for willing-to-wait requests).
Verify the Latch Sleep breakdown section to identify the type of latches with the highest misses.
Verify the Latch Miss Sources section to identify, among the latch requests experiencing sleeps, where the latch misses occur.