Know more about Buffer Cache and Latch

We can examine X$BH table to obtain buffer header information,The BH stands for buffer header. Structures that maintain a list of data buffer headers are called:Hash buckets. Hash buckets are grouped by relative DBA and class number. Hash chains list data buffer headers in one hash bucket. Cache Recycle Pool For randomly accessed large tables Default Pool For normally accessed tables Keep Pool for frequently updated small tables Set DB_BLOCK_LRU_LATCHES parameter to create multiple LRU lists.This parameter sets the number of latches, and it is best used on a multi-CPU system. DB_BLOCK_BUFFERS sets the number of buffers to allocate in the buffer cache. DB_BLOCK_LRU_EXTENDED_STATISTICS is obsolete in Oracle8i. DB_WRITER_PROCESSES sets the number of database writer processes. Cache buffers LRU chain protects LRU lists. The total number of working sets per instance is defined by DB_BLOCK_LRU_LATCHES. There is one latch per set. The number of DBWR processes cannot be greater than the number of working sets. Oracle get buffers as below steps:

1. get a buffer descriptor
2. specify a mode in which a buffer should be obtained
3. scan the appropriate hash chain
4. find the appropriate buffer in the chain or read from disk
5. attach a state object to the buffer header

Buffers are initially hashed to LRU_AUX.This contains buffers that are candidates for reuse. LRU_MAIN List houses buffers that are in use (pinned or dirty). LRU_W List is the write list for dirty buffers. LRU_XR List is the reuse range list for buffers that are to be written for reuse. DBWR processes operate on working sets that are assigned to them in a cyclical manner. There are three reasons may cause DBWR write buffers to disk:

• To provide checkpoints
• To do free requests
• To do ping writes(obsolete)

The write batch size controls the number of asynchronous writes slots allocated to each DBWR.The write batch size controls the number of asynchronous writes slots allocated to each DBWR. Two Most important DBWR statistics: DBWR make free request,DBWR lru scans 。 Two circumstances where DBWR purges dirty buffers whose DBA falls between the lowest and highest DBA of the datafiles:

• ALTER TABLESPACE...BEGIN BACKUP
• Make a tablespace read only

Of the nine latches that protect the buffer cache,the three that are most important:

• Cache buffers lru chain
• Cache buffers chains
• Checkpoint queue latch

each of the fixed tables below with its purpose:

• X$KCBWAIT         Wait statistics by block class
• X$KCBFWAIT      Wait statistics by file id
• X$KCBBHS              DBWR histogram statistics
• X$KCBWBPD         Buffer pool descriptors

A target buffer cache hit ratio is above 90%. But 60% could be a valid hit ratio for a DSS or Data Warehouse application. Two primary events that relate to the buffer cache:

• Buffer busy waits
• Free buffer waits

We can use the views and table:V$SYSTEM_EVENT, V$WAITSTAT, and X$KCBFWAIT to determine the reason and block class for buffer busy waits.