Shulou(Shulou.com)05/31 Report--

This article mainly introduces the SQL latch:cache buffers chains error report how to solve the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that after reading this SQL latch:cache buffers chains error report how to solve the article will have a harvest, let's take a look.

1. Production principle

When a data block is read into sga, the block head (buffer header) is placed in a linked list (hash chain) of hash bucket. This memory structure is protected by a series of cache buffers chains sub-latch (aka hash latch or cbc latch). For blocks in Buffer cache, if you want to select or update, insert,delete, etc., you must first obtain the cache buffers chains sub-latch to ensure exclusive access to chain. If contention occurs during the process, it waits for the latch:cache buffers chains event.

two。 Cause:

(1)。 Inefficient SQL statements (mainly reflected in logical reading too high) in some environments, applications open multiple concurrent sessions that execute the same inefficient SQL statements, and these SQL statements try to get the same data set, and the main reason is that each execution has a high BUFFER_GETS (logical read) SQL statement. In contrast, smaller logical reads mean less latch get operations, reducing latch contention and improving performance. Notice the large BUFFER_GETS/EXECUTIONS statement in v$sql.

Hot block hot blocks occur when multiple sessions repeatedly access one or more blocks protected by the same sub-cache buffers chains latch. This wait event occurs when multiple sessions compete for cache buffers chains child latches. Sometimes even if SQL is tuned, HOT BLOCK can occur when multiple sessions execute this SQL at the same time, even if only a few blocks are scanned.

3. Solution method

(1)。 Optimize SQL, such as nested loop join, and use hash join instead of nested loop join if possible.

(2)。 You can use hash partitioning the hot block index, or use hash clusters to slow down the hot block phenomenon.

(3)。 Adjust the pctfree value of the table to distribute the data into as many blocks as possible, but the same query has to scan more blocks, which has a negative effect.

(4)。 Parallel queries read data files directly without going through SGA, that is, direct path read, so there is no latch contention. But it is generally used for reading a large number of data, not as a general solution.

(5)。 Wait for the problem to disappear. Sometimes when there is latch contention, there is really no better way to solve the fault moment, and finding the cause is the key.

4. Find hot fast objects

Starts from the current session waiting for the latch:cache buffers chains event. From the v$session_wait view, get the address of the P1RAW, the sublatch. By repeatedly observing the v$session_wait view, it is found that a sublatch address appears more frequently, then the chain governed by that sublatch may have hot blocks.

Select p1,p1raw from v$session_wait where event='latch: cache buffers chains'

So the p1raw of v$session is the same thing as the laddr of x$bh and the addr of v$latch_children, both are the addresses of sublatches. The general idea is to find the object under control and the heat of the object through the heat of the sublatch.

The file number, block number and heat of the objects managed by these sub-latches are obtained through the address of the child latches, that is, the addr field of the v$latch_children. Notice that the tch field in the x$bh dictionary table represents the touch count of block. Generally speaking, the higher the value, the hotter the block. We call such a 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 ('00000000DA253C08signals,' 00000000DA380310')

ORDER BY tch DESC

Find out the corresponding object according to FILE#,dbablk.

Select * from dba_extents where file_id=10 and 36643122 between block_id and block_id + blocks-1

Select * from dba_extents where file_id=10 and 36643122 between block_id and block_id + blocks-1

This is the end of the article on "how to solve latch:cache buffers chains errors in SQL". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to solve latch:cache buffers chains errors in SQL". If you want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.