Latch: the reason for latch contention caused by cachebufferschains wait events and

Latch: cachebufferschains principle when a data block is read into sga, the block header (bufferheader) of this block will be placed in a hashchain of hashbucket. The memory structure is protected by a series of cachebufferschains sublatch (also known as hashlatch or cbclatch ). For blocks in Buffercache, sele is required.

Latch: cache buffers chains principle when a data block is read into sga, the block header (buffer header) of this block will be placed in a hash bucket linked list (hash chain. The memory structure is protected by a series of cache buffers chains sublatch (also known as hash latch or cbc latch ). For blocks in Buffer cache, sele is required.

Latch: Principle of cache buffers chains

When a data block is read into sga, the block header (buffer header) of this block will be placed in a hash bucket linked list (hash chain. The memory structure is protected by a series of cache buffers chains sublatch (also known as hash latch or cbc latch ). For blocks in the Buffer cache, to select, update, insert, delete, etc., you must first obtain the cache buffers chains sublatch to ensure exclusive access to the chain. If a competition occurs during the process, the system will wait for the latch: cache buffers chains event.

Cause: 1. inefficient SQL statements (mainly reflected in high read logic) in some environments, Applications open multiple concurrent sessions that execute the same inefficient SQL statements, these SQL statements try to get the same dataset. The main reason is that each execution has an SQL statement with a high BUFFER_GETS (logical read. On the contrary, smaller logical reads mean fewer latch get operations, reducing latch contention and improving performance. Note the BUFFER_GETS/EXECUTIONS statements in v $ SQL. 2. Hot block when multiple sessions repeatedly access one or more blocks protected by the same sub-cache buffers chains lock, Hot blocks will be generated. This wait event occurs when multiple sessions compete for the cache buffers chains sub-latch. Sometimes, even if the SQL is optimized, but multiple sessions execute this SQL statement at the same time, it is afraid that only a few specific blocks will be scanned, and HOT blocks will also appear.

Check to see the SQL statements that generate a relatively high buffer get in the current active session: select * from (select SQL _text, hash_value, buffer_gets/executions from v $ SQL where executions <> 0 and hash_value in (select SQL _hash_value from gv $ session where statu
S = 'active') order by buffer_gets/executions desc) where rownum <16;

Run v $ latch to check whether the cache buffers chains latch has been used since the instance was started: select round (misses/gets) * 100) | '% ', round (100 * (immediate_misses/(immediate_gets + immediate_misses) | '%' from v $ latch where name = 'cache buffers chains '; For willing-to-wait, the more important thing is misses/gets. If it is greater than 1%, competition should occur. If it is greater than 10%, there will be serious competition. In no-wait mode, the same applies to immediate_misses/(immediate_gets + immediate_misses.

Query the sub-latches view to see if there is a Hot Block and obtain the sub-latches addr select * from (select addr, child #, gets, misses, sleeps from v $ latch_children where name = 'cache buffers chains' order by sleeps desc) where rownum <= 20; if the skew is obvious, that is, the GETS and SLEEPS of some sub-latches are much higher than those of other sub-latches, so we can speculate that the chain under the corresponding latches has Hot blocks.

Another method for judging Hot block is to start from the session waiting for the latch: cache buffers chains event. Use the v $ session_wait view to obtain the address of the P1RAW sub-lock. By repeatedly observing the v $ session_wait view, we find that a sub-lock address appears more frequently, and the chain under the sub-lock may have hot blocks. Select p1, p1raw from v $ session_wait where event = 'latch: cache buffers chains ';

Therefore, p1raw of v $ session is the same as laddr of x $ bh and addr of v $ latch_children. The general idea is to find the objects under the jurisdiction and the heat of the objects through the sub-latches.

Obtain the file number and heat of the objects managed by the sub-latch_children through the sub-lock address, that is, the addr field of the v $ latch_children. Note that the tch field in the x $ bh dictionary table represents the touch count of the block. Generally, the higher the value, the hotter the block. We call this block a hotspot block. Select hladdr, obj, (select object_name from dba_objects where (data_object_id is null and object_id = x. obj) or data_object_id = x. obj and rownum = 1) as object_name, dbarfil, dbablk, tch from x $ bh x where hladdr in ('commandid da253c08 ', 'commandid da1_310') order by tch desc;
Find the corresponding object according to FILE # And dbablk. Select * from dba_extents where file_id = 10 and 36643122 between block_id and block_id + blocks-1;

You can use the v $ bh view to directly find the database hotspot block and find the hotspot object. Select * from (select hladdr, ts #, file #, dbarfil, dbablk, tch from x $ bh order by tch desc) where rownum <16; then use dba_extents to find the corresponding object. Select * from dba_extents where file_id = 10 and 36643122 between block_id and block_id + blocks-1; or use the following statement to find the database hotspot object: SELECT * FROM (select o. OWNER, O. OBJECT_NAME, O. OBJECT_TYPE, SUM (TCH) touchtime from x $ bh B, DBA_OBJECTS o where B. OBJ = O. DATA_OBJECT_ID and B. TS #> 0 group by o. OWNER, O. OBJECT_NAME, O. OBJECT_TYPE order by sum (TCH) DESC) where rownum <= 10;

View the SQL select * from (select count (*), SQL _id, nvl (o. object_name, ash. current_obj #) objn, substr (o. object_type,) otype, CURRENT_FILE # fn, CURRENT_BLOCK # blockn from v $ active_session_history ash, all_objects o where event like 'latch: cache buffers chains 'and o. object_id (+) = ash. CURRENT_OBJ # group by SQL _id, current_obj #, current_file #, current_block #, o. object_name, o. object_type order by count (*) desc) where rownum <= 10; view SQL full-text SQL> select SQL _fulltext from v $ sqlarea where SQL _id = '& sqlid' Based on the SQL _id information obtained above ';

Solution 1. Optimize SQL statements, such as optimizing nested loop join. If hash join is possible, replace nested loop join. 2. You can use the hash partition for the hot block index, or use the hash cluster to slow down the hot block phenomenon. 3. Adjust the pctfree value of the table and distribute the data to multiple blocks as much as possible, but scanning more blocks for the same query has a negative effect. 4. parallel query is to directly read data files without passing through SGA, that is, direct path read, so there is no contention for latches. However, it is generally used for reading a large amount of data and is not a general solution. 5. Wait until the problem disappears. Sometimes, when latch contention occurs, there is no better solution to the fault, and finding the cause is the key.

Appendix: Check the number of sub-latches in cache buffers chains Select count (*) from v $ latch_children where name = 'cache buffers chains ';
Find the top 10 hotspot block objects: select/* + rule */owner, object_name from dba_objects where data_object_id in (select obj from x $ bh order by tch desc) where rownum <11) or object_id in (select obj from x $ bh order by tch desc) where rownum <11)
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 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. blocked 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 heavy access to a single block. this wowould 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 .