Oracle Latch Free Wait Event

The Oracle latch free wait event occurs when a session needs a latch, tries to get the latch, but fails because someone else has it. So, it sleeps with a wait on latch free, wakes up and tries again. The time it was asleep is the wait time for “latch free.” There is no ordered queue for the waiters on a latch so the first to grab the latch gets it.

A latch is a fast, inexpensive and non-sophisticated lock. The latch is used when you need to serialize access to operations, functions and data structures in Oracle. They are meant to be held for a very short time. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later.

Analyzing Oracle latch free wait events

When performance problems are related to the latch free wait event, this event will appear in the top timed events. We will go through a sample of the latch free event occurring and demonstrate how it was resolved.

                                                                      % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free                                      2,040,581      20,221    61.33
CPU time                                                       10,368    34.05
db file sequential read                           113,044         624     2.09
log file sync                                     211,925         400     1.31
log file parallel write                           212,146         178      .54
                       

In statspack or awrrpt, you can move further down the report to check the latches that had the most sleeps. In this example, we see that the top latch sleeps are for the library cache.

Latch Miss Sources for DB: PROD  Instance: prod
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

                                                     NoWait              Waiter
Latch Name               Where                       Misses     Sleeps   Sleeps
------------------------ -------------------------- ------- ---------- --------
library cache            kglpin: child: heap proces       0     58,005    8,824
library cache            kgllkdl: child: cleanup          0     29,547   18,938
library cache            kglpndl: child: before pro       0     27,980   29,455
library cache            kglhdgn: child:                  0     14,733   43,657
library cache            kglupc: child                    0     14,396   14,415
library cache            kglhdgc: child:                  0     13,710    9,658
                       

Solving an Oracle latch free wait event

Since the library cache is indicated, we checked the instance efficiency percentages to see how the library cache was doing. In this case, the execute to parse ratio was also very low at 23.01% indicating that bind variables were not being used. The SQL was not reusable in the shared pool causing an excessive number of hits to the library cache (which is contained in the shared pool).

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:    100.00
            Buffer  Hit   %:   99.97    In-memory Sort %:    100.00
            Library Hit   %:   99.99        Soft Parse %:     78.81
         Execute to Parse %:   23.01         Latch Hit %:     99.57
Parse CPU to Parse Elapsd %:  100.51     % Non-Parse CPU:     82.93
                       

The parameters for governing the library cache were examined. Here we see that we are not using cursor sharing unless they are the exact same SQL. And we have no session_cached cursors.

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ---------
cursor_sharing                       string                           EXACT
open_cursors                         integer                          500
session_cached_cursors               integer                          0
shared_pool_size                     big integer                      524288000
                       

We could check the shared pool tuning to determine whether that might be a valid suggestion to increase the size of the shared pool. Cursor sharing can be implemented to prevent thrashing within the shared pool and the session_cached_cursors can be used to reduce the soft parses. The following Oracle initialization parameter changes resolved the performance degradation that resulted from the latch free wait event.

alter system set session_cached_cursors=150 scope=spfile;
alter system set shared_pool_size=900M scope=spfile;
alter system set cursor_sharing='FORCE' scope=spfile;
                       

Final thoughts

The Oracle latch free wait event can cause significant performance issues. These can be examined by reviewing the latches that are getting the most sleeps. If it turns out to be enqueues, you may also see enqueue waits which could be a result of locks being held too long or an unindexed foreign key causing a full table lock. Depending on the type of latch causing the wait event, the resolution will vary.