AWR TOP 5 Timed Event Analysis - Latch Free

Top 5 Times Events

latch free , latch: library cache , latch: library cache lock

latch free – System is waiting for a latch to become free


Basically it means some session needed a latch (on the library cache for example to parse some SQL). it tried to get the latch, but failed (because someone else had it). So, it goes to sleep (waits on a latch free) and wakes up and tries again. The time it was asleep - that is the wait time for "latch free"

A latch is a low-level internal lock used by Oracle to protect memory structures. The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.

There is a dedicated latch-related wait event for the more popular latches that often generate significant contention. For those events, the name of the latch appears in the name of the wait event, such as latch: library cache or latch: cache buffers chains. This enables you to quickly figure out if a particular type of latch is responsible for most of the latch-related contention. Waits for all other latches are grouped in the generic latch free wait event.

Actions

This event should only be a concern if latch waits are a significant portion of the wait time on the system as a whole, or for individual users experiencing problems.

• Examine the resource usage for related resources. For example, if the library cache latch is heavily contended for, then examine the hard and soft parse rates.

• Examine the SQL statements for the sessions experiencing latch contention to see if there is any commonality.

Once you see this event as TOP, you need to further drill down the AWR report to Latch Statistics section to see Latch Activity and Latch Sleep Breakdown to determine which latch is doing more no of misses, sleeps and spin gets. Based on which one has to look for data what is causing these.

If you see library cache latch, then possible causes might be lack of statement reuse, statements not using bind variables, cursors closed explicitly after each execution.

If one see cache buffers chains as top event with more misses and sleeps then the possible cause might be repeated access to a block known as hot block which is caused due to Sequence number generation code that updates a row in a table to generate the number, rather than using a sequence number generator.

We have found that in our case a poorly written package was hitting us hard where huge hard parses were found. There are lot many queries to find out the queries which are performing the hard parses like

SELECT SQL_TEXT FROM V$SQLSTATS WHERE EXECUTIONS < 4 ORDER BY SQL_TEXT;

SELECT SUBSTR(SQL_TEXT, 1, 60), COUNT(*) FROM V$SQLSTATS WHERE EXECUTIONS < 4 GROUP BY SUBSTR(SQL_TEXT, 1, 60)HAVING COUNT(*) > 1;

Check the V$SQLSTATS view. Enter the following query:

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLSTATS ORDER BY PARSE_CALLS;

When the PARSE_CALLS value is close to the EXECUTIONS value for a given statement, you might be continually reparsing that statement. Tune the statements with the higher numbers of parse calls.