Buffer Cache Experiment 8:free buffer waits-complete

When the server process scans the LRU primary column, it moves the dirty block to the Lruw column. When scanned to a threshold (10g/11g is the 40%-_db_block_max_scan_pct parameter decision) will stop the scan and notify DBWR write dirty block-write dirty block after the block back to the auxiliary LRU column, get free buffer.

DBWR Process write dirty block before completion, the server process waits for free buffer when the Waits wait event

About the physical reading time to find free Buffer,lruw and write dirty block detailed process, see:

The default value for the _db_block_max_scan_pct parameter--11.2.0.4

P_name p_description P_value IsDefault ismodified Isadj

---------------------------------------- -------------------------------------------------- --------------------- --------- --------- ---------- -----

_db_block_max_scan_pct percentage of buffers to inspect when looking for REE 40 TRUE false

Contentious Scenarios and solutions:

Inefficient sql-need more independent data blocks, need more buffer, easy to appear free buffer waits wait event

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/storage/

Buffer cache small enough to use-is the memory of small buffer cache to small space, is really not enough to consider increasing buffer cache.

DBWR write poor performance-may be the database's host CPU resources tight, DBWR process slow, or I/o slow, affect write speed, or CPU and I/O are no problem, you can consider increasing the number of DBWR process.

Because the DBWR process writes dirty blocks to the data file, it occupies the dirty buffer in exclusive mode, and there are other processes to read or modify the dirty buffer, waiting for the DBWR to write the data, and the write complete waits wait.

To determine how dbwr/storage performance causes this type of problem:

Disk Busy level

Disk response time--large i/0 no more than 20MS, small I/O no more than 5MS

IOPS is normal--about 150 per plate

Storage cache is normal-this happens occasionally

is the storage-related hardware running correctly

-------If the disk is normal, consider increasing the number of DBWR processes--generally not exceeding the number of CPUs.

Free buffer waits wait is often caused by application-inefficient SQL

Write complete waits wait is often caused by storage performance. --This section comes from Liang Zhou ORACLE DBAs ' combat strategy