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

1. When will free buffer waits happen?

In a nutshell, this wait occurs when you need to look for available blocks in buffer cache but cannot find them. Reasons for finding available blocks include reading blocks into memory or constructing CR blocks.

SQL > select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name = 'free buffer waits'

NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS

--

Free buffer waits file# block# set-id# Configuration

The process of reading data blocks by 2.Oracle

1) for example, to read object 1, file 2, and block 3, Oracle first calculates the hash value according to the object number, file number and block number, and then searches the hash table to determine whether the target data block already exists in memory. The hash table here is a collection of header of the CBC chain.

2) get the memory of the bucket according to the calculated hash value, and then scan the CBC chain of the bucket. Under the protection of CBC latch, you will compare the results in turn. If there is a match, then get the BA address and read the buffer.

3) if it does not exist, then the reusable blocks need to be found on the LRU chain. During the scanning process, the blocks occupied by pin, the blocks and dirty blocks with TCH greater than 2 will not be reused, the blocks occupied by pin will be skipped, the blocks with TCH greater than 2 will be moved to the hot end of the LRU linked list, and the dirty blocks with TCH less than 2 will be moved to the LRUW chain. When scanning to 40% of the LRU chain, 40% is controlled by the implicit parameter _ db_block_max_scan_pct. If no overlay buffer has been found at this time, the scan will be stopped, the DBWR process will be awakened to write dirty blocks, and the foreground process will go to sleep, and the waiting event will be free buffer waits.

4) if a buffer that can be covered is found, it will be decided whether to actively wake up the DBWR process to write dirty blocks according to the total number of dirty blocks before the next step. If the number of dirty blocks accounts for 25% of the checkpoint queue, then the DBWR process will not wake up for 3 seconds, but actively wake up. 25% is determined by the implied parameter _ db_large_dirty_queue. There is no free buffer waits waiting for active wake-up.

5) after the DBWR process wakes up or wakes up for 3 seconds to write dirty blocks, it hangs the reusable dirty blocks on the secondary LRU linked list.

As can be seen from the above process, the implicit parameter _ db_block_max_scan_pct actively wakes up DBWR to write dirty blocks by controlling the number of dirty blocks, which helps to alleviate free buffer waits events.

What is the cause of free buffer waits?

1) the db_cache_size value is too small

2) check the db_writer_processes parameter. Generally speaking, this value will not be actively modified. By default, there will be one dbwr process for every 8 CPU.

3) there is a problem with storage performance. It may be slow to write dirty data and slow to write redo.

4) delay block cleanup, which may take up a lot of buffer

5) sql is inefficient, too large physical reading or too many CR blocks to be constructed, such as large table select *, Cartesian product, etc.

In the above process of reading data blocks, it is mentioned that the blocks skipped when scanning the LRU chain can be checked as follows:

SQL > select * from v$sysstat where name in ('free buffer inspected','free buffer requested')

STATISTIC# NAME CLASS VALUE STAT_ID CON_ID

206 free buffer requested 8 1134972512 3411924934 0

210 free buffer inspected 8 366218879 941676439 0

SQL > select 366218879 from dual 1134972512

366218879/1134972512

-

. 32266762

Free buffer inspected: the number of blocks skipped when scanning the LRU chain for reusable blocks

Free buffer requested: the number of times a block has been requested for reuse

The above results show that an average of 0.32 blocks are scanned per request, which means that reusable blocks are generally available immediately.

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.