附录：查看cache buffers chains有多少个子锁存器 Select count(*) from v$latch_children where name = 'cache buffers chains';
找出前10的热点块对象： select /*+rule*/ owner,object_name from dba_objects where data_object_id in (select obj from (select obj from x$bh order by tch desc) where rownum < 11) or object_id in (select obj from (select obj from x$bh order by tch desc) where rownum < 11)
Oracle11g联机文档中摘录： The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block). To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch. This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers: SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH FROM X$BH WHERE HLADDR = 'address of latch' ORDER BY TCH; X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block. Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query several times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment. After you have identified the hot block, you can identify the segment it belongs to with the following query: SELECT OBJECT_NAME, SUBOBJECT_NAME FROM DBA_OBJECTS WHERE DATA_OBJECT_ID = &obj; In the query, &obj is the value of the OBJ column in the previous query on X$BH.
Latch: cache buffers chains Description: Blocks in the buffer cache are placed on linked lists (cache buffer chains) which hang off a hash table. The hash chain that a block is placed on is based on the DBA and CLASS of the block. Each hash chain is protected by a single child latch. Processes need to get the relevant latch to allow them the scan a hash chain for a buffer so that the linked list does not change underneath them. Contention: Contention for these latches can be caused by: - Very long buffer chains. There is a known problem that can result in long buffer chains - - very very heavy access to a single block. This would require the application to be reviewed. - To identify the heavily accessed buffer chain look at the latch stats for this latch under and match this to .