Optimization of Latch:cache buffers chains

Data blocks stored in buffer cache are stored in linked list mode. When a session wants to access/modify the block of buffer cache, it first needs to check whether the block exists in buffer cache with the hash algorithm and check if the same SQL statement exists in the library The cache is also implemented by the hash algorithm. To determine if a block exists in buffer cache, you need to scan the linked list (which is serial, not concurrent) to get information about the block. The scan linked list must obtain a latch to prevent concurrent damage to the linked list, and if the latch is not available, a latch:cache buffers chains will be flagged in the database for this wait event. If the block exists in buffer cache, it does not require a physical read, and if it does not exist, it needs to be read from disk to buffer cache. In order to be able to read and modify the block, we need to pin the block to prevent the block from being corrupted, so if the other session does not get a pin, a buffer busy waits wait event is flagged.

There are several reasons for the general cache buffers chains: 1, the buffer cache is too small (it also indicates that the SQL statement is inefficient), 2, the hot block to earn. (Starting with oracle9i, read-only shared access is supported for Latch:cache buffer chains, which reduces partial contention, but does not completely eliminate contention.) )

One, the buffer cache is too small (also indicates that the SQL statement is inefficient)

The application executes multiple concurrent sessions with the same inefficient SQL statements, all of which try to get the same data set. More logical reads mean more latch get operations, which increases latch contention. Cache buffers Chains Latch contention can occur extensively when multiple processes scan a large range of indexes or tables at the same time. SQL statements with high buffer_gets (logical reads) are the main reasons for each execution.

1. View current wait events (Latch:cache buffers chains)

Sql> Select Event, COUNT (*) from v$session

where Wait_class <> ' Idle ' Group by event order by 2;

2. View Latch:cache buffers Chains Event-related session information

Sql> Select Sid,username,machine,program,p1raw,sql_id,logon_time,last_call_et from v$session where event= ' latch: cache buffers Chains ';

Second, the hot block to earn

Hot block earning is generated when multiple sessions repeatedly access one or more blocks protected by the same sub-cache buffers chains latch. When multiple sessions contend with the cache buffers chains latch, the best way to find out if there is a hot block is to check the P1raw parameter value of the latch free wait event.

Another way to judge the use of hot blocks is to obtain a latch address from the v$session_wait view and compare it. The P1raw of the v$session_wait is equivalent to the sub-latch address, if the latch address obtained from the v$session_wait view is repeated too many times, it means that there are more occurrences of the corresponding latch, which can be interpreted as the contention caused by hot-fast. If the session is waiting on the same latch address, it is the hot block.

Sql> Select Sid,p1raw,p2,p3,seconds_in_wait,wait_time,state from v$session_wait

where event= ' Latch:cache buffers chains ' ORDER by 3, 2;

To view the objects of a hot block:

Confirm the hot block according to the TCH value. Note that when the block moves from the cold end of the LRU list to the hot end, the tch value is reset to 0, so when judging, be aware that the block tch 0 is not necessarily a cold block.

Use p1raw=00000300da316800 as an example to correlate hot-fast objects.

Sql> Select A.hladdr,a.file#,a.dbablk,a.tch,a.obj,b.object_name from X$bh A, dba_objects b

WHERE (a.obj = b.object_id or a.obj = b.data_object_id) and a.hladdr = ' 00000300da316800 '

Union Select Hladdr,file#,dbablk,tch,obj,null from X$BH

where obj in (select obj from x$bh where hladdr = ' 00000300da316800 ' minus select object_id from Dba_objects minus Select data_object_id from dba_objects) and hladdr = ' 00000300da316800 ' ORDER by 4;

If there is no information about the SQL statement, there are ways to indirectly determine whether the problem is caused by a hot block or a problem caused by an inefficient SQL statement. V$latch_children view, compare the child#, gets, and sleeps values of the sub-cache buffers chains latch to determine the number of times and contention used on a particular child latch, using the following statement, Gets the sub-latch with a high number of sleeps.

Sql> SELECT * FROM (select addr, child#, gets, sleeps from V$latch_children where name = ' Cache buffers chains ' order B Y sleeps desc)

where rownum < = 20;

When the value of the sleeps in the result is tilted large, it means that the hot block is earned.

Determine which blocks are hot blocks according to the sleeps higher addr.

Sql> Select Hladdr,obj, (select object_name from Dba_objects where (data_object_id is null and object_id = x.obj) or dat a_object_id = x.obj and rownum = 1) as Object_name,dbarfil,dbablk,tch from X$bh x where hladdr = ' &p1raw ' ORDER by Hlad Dr, obj;

The   data block is stored in the buffer cache in linked list mode. When a session wants to access/modify the block of buffer cache, it first needs to check whether the block exists in buffer cache with the hash algorithm and check if the same SQL statement exists in the library The cache is also implemented by the hash algorithm. To determine if a block exists in buffer cache, you need to scan the linked list (which is serial, not concurrent) to get information about the block. The scan linked list must obtain a latch to prevent concurrent damage to the linked list, and if the latch is not available, a latch:cache buffers chains will be flagged in the database for this wait event. If the block exists in buffer cache, it does not require a physical read, and if it does not exist, it needs to be read from disk to buffer cache. In order to be able to read and modify the block, we need to pin the block to prevent the block from being corrupted, so if the other session does not get a pin, a buffer busy waits wait event is flagged. There are several reasons for the general cache buffers chains: 1, the buffer cache is too small (it also indicates that the SQL statement is inefficient), 2, the hot block to earn. (Starting with oracle9i, read-only shared access is supported for Latch:cache buffer chains, which reduces partial contention, but does not completely eliminate contention.) One, the buffer cache is too small (and the SQL statement is inefficient) the application executes multiple concurrent sessions of the same inefficient SQL statements, all of which try to get the same data set. More logical reads mean more latch get operations, which increases latch contention. Cache buffers Chains Latch contention can occur extensively when multiple processes scan a large range of indexes or tables at the same time. SQL statements with high buffer_gets (logical reads) are the main reasons for each execution. 1. View current wait events (Latch:cache buffers chains) sql> Select event, COUNT (*) from v$session  where wait_class <> ' Idle ' GROUP BY event order by 2; 2, view Latch:cache buffers chains Event-related session information sql> Select Sid,username,machine,program,p1raw,sql_id,logon_time,last_ Call_et from v$session where event= ' latch:cache buffers chains '; Second, hot block earned when multiple sessions repeatedly access one or more blocks protected by the same sub-cache buffers chains Latch, a hot block is generated. When multiple sessions contend with the cache buffers chains latch, the best way to find out if there is a hot block is to check the P1raw parameter value of the latch free wait event. Another way to judge the use of hot blocks is to obtain a latch address from the v$session_wait view and compare it. The P1raw of the v$session_wait is equivalent to the sub-latch address, if the latch address obtained from the v$session_wait view is repeated too many times, it means that there are more occurrences of the corresponding latch, which can be interpreted as the contention caused by hot-fast. If the session is waiting on the same latch address, it is the hot block. Sql> Select Sid,p1raw,p2,p3,seconds_in_wait,wait_time,state from v$session_wait where event= ' Latch:cache buffers Chains ' ORDER by 3, 2; View the object of the hot block: Confirm the hot block based on the tch value. Note that when the block moves from the cold end of the LRU list to the hot end, the tch value is reset to 0, so when judging, be aware that the block tch 0 is not necessarily a cold block. Use p1raw=00000300da316800 as an example to correlate hot-fast objects. Sql> Select A.hladdr,a.file#,a.dbablk,a.tch,a.obj,b.object_name from X$bh A, dba_objects b where (a.obj = b.object_id o R a.obj = b.data_object_id) and a.hladdr = ' 00000300da316800 ' union select Hladdr,file#,dbablk,tch,obj,null from X$BH wher e obj in (select obj fRom x$bh where hladdr = ' 00000300da316800 ' minus select object_id from dba_objects minus select data_object_id from Dba_ob jects) and hladdr = ' 00000300da316800 ' ORDER by 4; If there is no information about the SQL statement, there are ways to indirectly determine whether the problem is caused by a hot block or a problem caused by an inefficient SQL statement. V$latch_children view, compare the child#, gets, and sleeps values of the sub-cache buffers chains latch to determine the number of times and contention used on a particular child latch, using the following statement, Gets the sub-latch with a high number of sleeps. Sql> SELECT * FROM (select addr, child#, gets, sleeps from V$latch_children where name = ' Cache buffers chains ' order B Y sleeps desc) where RowNum < = 20; When the value of the sleeps in the result is tilted large, it means that the hot block is earned. Determine which blocks are hot blocks according to the sleeps higher addr. Sql> Select Hladdr,obj, (select object_name from Dba_objects where (data_object_id is null and object_id = x.obj) or dat a_object_id = x.obj and rownum = 1) as Object_name,dbarfil,dbablk,tch from X$bh x where hladdr = ' &p1raw ' ORDER by Hlad Dr, obj;

Latch:cache buffers chains optimization ideas