In this Document
Purpose |
Troubleshooting Steps |
Brief Definition: |
Problem Confirmation: |
"Latch Free" Waits |
Tuning the "latch free" Waits |
Shared Pool Latch |
Library Cache Latch |
Cache buffers chains |
Known Issues |
References |
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
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:
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
References
NOTE:9732503.8 - Bug 9732503 - latch free waits for SQL Memory Manager latch / extra child cursorsNOTE: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
![](https://i-blog.csdnimg.cn/blog_migrate/200491d1ae6203cbdf7e02dca39a75b1.gif)
|
|
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Personal Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)
![](https://i-blog.csdnimg.cn/blog_migrate/200491d1ae6203cbdf7e02dca39a75b1.gif)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-747105/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17252115/viewspace-747105/