闩是一种内部串行机制,可以理解为一种轻量级的锁,用来保护SGA中的共享数据。
关于闩等待,最重要的两种闩是 latch:shared pool latch 和 latch:cache buffers LRU chain
latch: shared pool等待事件
#http://www.askmaclean.com/archives/latch-shared-pool.htmlorcale shared pool只有一个共享池闩,它保护库高速缓存中内存的分配。库高速缓存闩控制出现在库高速缓存中的对象的访问。任何的 sql、pl/sql,过程、函数、和程序在执行前都必须要获得这个闩。
The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. Typically a miss rate for this latch less than 95% results in poor performance. There is only one latch of this type in the shared pool, so when there is contention for this latch, significant degradation in performance can occur.
Problem
ORA-4031 errors are seen in the alert log if a trace file is generated (such as when a background process receives this error instead of when a user proc may get it. This means the session cannot allocate contiguous memory in shared pool for incoming SQL statement.
Solutions
Since the number of shared pool latch gets is influenced by the volume of shared pool activity like parse operations, anything that can reduce the this activity will improve the availability of this latch and overall database performance (see Reducing the Number of SQL Statements).
latch:cache buffers LRU chain latch等待事件
This latch is needed when user processes try to scan the LRU (least recently used) chain containing blocks in the buffer cache. This latch must be acquired before the block can be inserted into (when the block is read in from the disk) or taken out of the LRU chain (when the block is written down to the disk).
cache buffers LRU chain 闩空闲等待是由高缓冲区高速缓存吞吐导致的,也可能是由去全表扫描或者使用了无选择性的索引引起,后者导致较大的索引扫描。无选择索引可能会导致另外一种类型的闩等待:cache buffers chain 空闲等待。这些等待是由于出现热点块。
Solutions
- Consider implementing multiple buffer pools to reduce contention on this latch.
- Increase the number of LRU latches with the parameter DB_BLOCK_LRU_LATCHES. Generally the default value works.
- Reduce data blocks visited by a query and there by reduce LRU latch requests in the buffer pool by tuning the SQL.