Databases Reference
In-Depth Information
Tuning resources to minimize latch
contention
In this recipe we will see how to identify the resources that need to be tuned to minimize
latch contention.
Getting ready
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
.
How to do it...
To identify the resources to be tuned to avoid latch contention we need to:
1.
Execute a Statspack report.
2.
See in the Instance Efficiency Indicators section the value for Latch Hit %.
3.
See in the Top 5 Timed Events section if there is a latch free wait event.
4.
Verify the Latch Activity section to identify the kind of latches that are eventually
experiencing misses (and sleeps for willing-to-wait requests).
5.
Verify the Latch Sleep breakdown section to identify the type of latches with the
highest misses.
6.
Verify the Latch Miss Sources section to identify, among the latch requests
experiencing sleeps, where the latch misses occur.
How it works...
After executing a statspack report, we can go to the Instance Efficiency Indicators section
to see the value for the Latch Hit %, to see the Hit percentage of latches, as in step 2. The
following screenshot shows the report for a database with small Latch Misses (the value for
Latch Hit % is 100.00):
