In this Document

Applies to:

Purpose

Troubleshooting Steps

Brief Definition:

For versions prior to 10g, waits for latches including shared pool, library cache lock, buffer cache latch, buffer free latch, etc. were covered by a single wait event called 'latch free'.

Problem Confirmation:

• The time spent actively in the local database is significant
• Waits for 'latch free' are significant
• Overall performance of the database may be degraded

"Latch Free" Waits

Some sessions are waiting for latch free wait but cannot obtain the latch. The latch free wait protects memory structure such as buffer cache or the library cache in the shared pool. They were designed to be held for very short period of time. If the latch is not available, the session will go to sleep and retry the operation later. Waiting for the latch can be expensive, as the session can spin on CPU while waiting for the latch.

Tuning the "latch free" Waits

1. First, the type of latch must be first identified before the tuning process can begin. To find the type of latch, run 10046 or statspack/AWR:

2. After finding the type of latch, then start tuning the specific latch. Some examples of latches are following:

Shared Pool Latch

Contention on the shared pool latch is due to either the shared pool being undersized, SQL not being shared or heavy usage of data dictionary. Make sure that the shared pool is large enough for the application load and that SQL statements are shared as much as possible. For more information, see the following:

Library Cache Latch

Contention for the library cache latch may be related to contention for the shared pool latch, since the library cache is located within the shared pool. Library cache latch contention is usually due to high parsing or an undersized shared pool. If the cause is excessive hard parsing then you need to look at why cursors are being parsed so much. Since Oracle attempts to reuse previously parsed application code it will not need to re-parse it assuming it is sharable. If there is no parsed representation of the sql in the library cache, then Oracle will need to hard parse the sql. For further information, see the following note:

Cache buffers chains

This latch is used to protect a buffer list in the buffer cache. The process which pinned first will acquire a latch, so that no other process changes the data. Once the block is unpinned, it would be available for second user and so on. Multiple sessions may need the same block, causing contention. For detailed information on how to troubleshoot this wait, review the following wait:

Measuring Success

Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re-evaluate those and address them according to the specific symptom.

Known Issues

References