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.