Shulou(Shulou.com)06/01 Report--

Data blocks are stored in buffer cache as linked list. When a session wants to access / modify the block of buffer cache, it first needs to check whether the block exists in the buffer cache through the hash algorithm, and whether the same SQL statement exists in the library cache is also realized by the hash algorithm. To determine whether the block exists in the buffer cache, you need to scan the linked list (all serial here, not concurrent) to get the information about the block. The scan linked list must get a latch to prevent concurrency from destroying the linked list image. If the latch cannot be obtained, a latch: cache buffers chains wait event will be marked in the database. If the block exists in buffer cache, there is no need for physical reading, and if it does not exist, the block 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 concurrency from destroying the block, so if another session cannot get the pin, a buffer busy waits wait event is marked at the same time.

Generally, there are several reasons for CACHE BUFFERS CHAINS: 1, too little buffer cache (also shows that the efficiency of SQL statements is low); 2, hot blocks to earn. (starting with oracle9i, support for read-only shared access to latch:cache buffer chains reduces some contention, but does not eliminate contention completely.)

First, there is too little buffer cache (it also shows that the efficiency of SQL statements is low)

The application executes multiple concurrent sessions of the same inefficient SQL statements, all of which try to get the same dataset. More logical reads mean more latch get operations, which increases latch contention. Cache buffers chains latch contention can occur widely when multiple processes scan a wide range of indexes or tables at the same time. The main reason is that each execution has a high BUFFER_GETS (logical read) SQL statement.

1. View the current wait event (latch: cache buffers chains)

SQL > select event, count (*) from v$session

Where wait_class' Idle' group by event order by 2

2. View the session information related to the latch: cache buffers chains event

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 blocks can be used.

Hot blocks are generated when multiple sessions repeatedly access one or more blocks protected by the same sub-cache buffers chains latch. When multiple sessions compete for cache buffers chains latches, the best way to find out if there are hot blocks is to check the P1RAW parameter value of the latch free wait event.

Another way to determine the hot block to use is to obtain the latch address from the v$session_wait view and compare it. The P1RAW of v$session_wait is equivalent to the sublatch address. If too many latch addresses obtained from the v$session_wait view are repeated, it means that the corresponding latches occur too many times, which can be interpreted as contention caused by thermal fast. If the session is waiting at the same latch address, it is a 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 objects of the hot blocks:

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

Use P1RAW=00000300DA316800 as an example to associate thermal fast objects.

SQL > select a.hladdr from x$bh a. Filewriting recorda.dbablk from x$bh a.tchrea.obj 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 SQL statements, there is also a way to indirectly determine whether the problem is caused by hot blocks or inefficient SQL statements. In the v$latch_children view, compare the values of child#, gets and contacts corresponding to the sub-cache buffers chains latches to determine whether the number of times and contention on the specific sub-latches are concentrated or not, and use the following statement to obtain the sub-latches with high sleeps times.

SQL > select * from (select addr, child#, gets, sleeps from v$latch_children where name = 'cache buffers chains' order by sleeps desc)

Where rownum

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 ='& p1raw' order by hladdr,obj

= End===

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.