What Does the Oracle Latch Free Wait Event Tell You?

By: Richmond Shee, Kirtikumar Deshpande, K Gopalakrishnan


When you see a latch free wait event in the V$SESSION_WAIT view, it means the process failed to obtain the latch in the willing-to-wait mode after spinning _SPIN_COUNT times and went to sleep. When processes compete heavily for latches, they will also consume more CPU resources because of spinning. The result is a higher response time. In this case, the high CPU utilization is a secondary symptom. This false CPU utilization level can falsely impress your capacity planner that the server needs faster or more CPUs.

The TOTAL_WAITS statistic of the latch free wait event in the V$SYSTEM_EVENT view tracks the number of times processes fail to get a latch in the willing-to-wait mode. The SLEEPS statistic of a particular latch in the V$LATCH view tracks the number of times processes sleep on that latch. Because a process has nothing else to do but sleep when it fails to get the latch after spinning for _SPIN_COUNT times, the TOTAL_WAITS should equal the sum of SLEEPS as shown in the following code. However, there are times where TOTAL_WAITS is greater than the sum of SLEEPS. This is because SLEEPS statistics are only updated when the latch GET operation succeeds, and not on each attempt.

Fig 6-1

 

 

 

 

Because latch free waits are typically quite short, it is possible to see a large number of waits (TOTAL_WAITS) that only
account for a small amount of time. You should only be concerned when the TIME_WAITED is significant.

 

Leave a Reply