Oracle Database 11gR2 Performance Tuning Cookbook

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 ...