Latch Free Wait Contention Causes Performance Degradation-1476736.1

最新推荐文章于 2024-09-06 23:32:02 发布

cuilian2013

最新推荐文章于 2024-09-06 23:32:02 发布

阅读量93

点赞数

文章标签：数据库

Resolving Issues Where "Latch Free" Wait Contention Causes Performance Degradation [ID 1476736.1]

In this Document

Purpose

Troubleshooting Steps

Brief Definition:

Problem Confirmation:

"Latch Free" Waits

Tuning the "latch free" Waits

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.0 to 10.2.0.5.0 [Release 8.1.7 to 10.2]
Oracle Server - Standard Edition - Version 8.1.7.0 to 10.2.0.5.0 [Release 8.1.7 to 10.2]
Oracle Server - Personal Edition - Version 8.1.7.0 to 10.2.0.5 [Release 8.1.7 to 10.2]
Information in this document applies to any platform.

Purpose

Disclaimer: This note is written in the context that the Performance Guided Resolution tool was used and recommended this article. It may not make as much sense if read standalone or by other audiences.

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:

Document 413942.1 How to Identify Which Latch is Associated with a "latch free" wait

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:

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

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:

Document 444560.1 Troubleshooting Library Cache: Lock, Pin and Load Lock

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:

Note:62172.1 Understanding and Tuning Buffer Cache and DBWR

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

Document 9732503.8 latch free waits for SQL Memory Manager latch / extra child cursors

References

NOTE:9732503.8 - Bug 9732503 - latch free waits for SQL Memory Manager latch / extra child cursors
NOTE:444560.1 - Troubleshooting Library Cache: Lock, Pin and Load Lock
NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
NOTE:413942.1 - How to Identify Which Latch is Associated with a "latch free" wait