Oracle buffer cache flush 数量没变原因

What is the buffer cache ?

Oracle keeps copies of database blocks in an area of the SGA known as the buffer cache. The cache may hold more than one copy of a block from different points in time, and may contain 'dirty' blocks - ie: blocks which have been updated but not yet flushed back to disk. The database writer/s (DBWR or DBWn processes) are responsible for writing dirty blocks to disk while any user session can read blocks into the cache.

All blocks in the buffer cache are on an LRU (least recently used) list - when a process needs a free buffer it scans from the LRU end of this list for a non-dirty buffer that it can use. The 'cache buffers lru chain' latch/es serialize operations on the LRU list/s.
for a description

 

Most common  Buffer Cache Waits and Latches

Latches:

    Please refers to Note:22908.1 for a complete discussion on detecting and resolving latch contention.

• Cache buffer chain latch:---块在buffer中，不停的读，这个增加chain没有用，因为某个块经过hash后固定在某个链了。

• • This latch is acquired  when searching for data blocks cached in the SGA. Since the Buffer cache is a chain of blocks, each of this chains is protected by a child of this latch when needs to be scanned. Contention in this latch can be caused by very heavy access to a single block. This would require the application to be reviewed. As of Oracle8i there are many hash buckets to each latch and so there will be lots of buffers under each latch.

如果访问的数据块不在buffer cache中，就需要扫描LRU链表(寻找free buffer)，当达到扫描块数限制后( 40%)还没有找到空闲buffer，就需要通知DBW0将脏缓存回写到磁盘。(如果checkpoint queue达到了 25%,也会出发DBWn进程去写脏数据).在这种情况下，用户进程访问一个数据块的过程是最长的，也就是效率最低的。如果一个系统中存在大量的脏缓冲，那么就可能导致用户进程访问数据性能下降。

以上提到的两个百分比可以通过以下两条命令查到:

sys@RPTTEST> select kvittag,kvitval,kvitdsc from x$kvit  where kvittag='kcbldq';

KVITTAG   KVITVAL  KVITDSC
----------------------------------------------------------------------------------------------------
kcbldq  25  large dirty queue if kcbclw reaches this

sys@RPTTEST> select kvittag,kvitval,kvitdsc from x$kvit where kvittag='kcbfsp';

KVITTAG KVITVAL KVITDSC
----------------------------------------------------------------------------------------------------
kcbfsp  40 Max percentage of LRU list foreground can scan for free

---------alter system flush buffer_cache;  数据量没变少因为没有加state状态  0为已经释放的

X$BH 的state字段

The possible values of X$BH.STATE are:

        0, FREE, no valid block image
        1, XCUR, a current mode block, exclusive to this instance
        2, SCUR, a current mode block, shared with other instances
        3, CR,   a consistent read (stale) block image
        4, READ, buffer is reserved for a block being read from disk
        5, MREC, a block in media recovery mode
        6, IREC, a block in instance (crash) recovery mode

 select object_name, object_id, data_object_id   from dba_objects    where object_NAME='IDX_TEST' ;

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID

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

IDX_TEST        59545          59545 ---------Used to join X$BH table(从x$bh查询缓存blocks，要用DATA_OBJECT_ID)
 

SQL> alter system flush buffer_cache;

SQL> select count(*) from x$bh where obj= DATA_OBJECT_ID and STATE=1 

flush buffer_cache后，数据量没变少因为没有加state状态  0为已经释放的

  

 

• Cache buffers LRU chain latch:----块不在buffer中，需要read时，free不足

• • Processes need to get this latch when they need to move buffers based on the LRU block replacement policy in the buffer cache. Contention in this latch can be avoided implementing multiple buffer pools or increasing the number of LRU latches with the parameter DB_BLOCK_LRU_LATCHES (The default value is generally sufficient for most systems). SQL tuning can affect this as well by reducing data blocks visited by a query.

The behavior of this latch can be affected when extended statistics are enabled using the parameters DB_BLOCK_LRU_EXTENDED_STATISTICS and DB_BLOCK_LRU_STATISTICS (These parameters were removed in Oracle8i)

Wait events:

• Buffer busy wait:

• • This event is commonly caused when multiple session are trying to read the same block or multiple session waiting for a change to complete in the same block. Block contention corrective actions depends on the type of block involved. Query on V$WAITSTAT and X$KCBFWAIT to detect the hottest blocks breaking down by the type of block.  To reduce buffer busy waits on: 

data blocks:

• • • • Reduce number of rows per block whether changing pctfree/pctused or reducing the DB_BLOCK_SIZE.
      • Check for 'right-hand-indexes' (indexes that get inserted into at the same point by many processes). You can use reverse key indexes to distribute the different information.

See Note:155971.1 for a detailed case-study on how to diagnose and resolve intensive random access performance problems.

segment header:

• • • • Use freelists or increase of number of freelists.
      • Extent size too small can cause contention on the header when the table grows regularly. Consider increasing the extent size for the table.

        undo header:
 

• • • • Add more rollback segments to reduce the number of transaction per rollback segment.
      • Reduce the value of the parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT

        undo block:
 

• • • • Consider making rollback segments larger in exclusive mode

• Free buffer wait:    ----LRU latches

• • This will mostly occur because of DBWR not writing out buffers fast enough. Please refers to the section "

Increase DBWR throughput

• " to improve the speed of this process.

Oracle Bugs 

The first diagnostic step to resolve this behavior is to apply the latest patchset available in your platform. Most of the buffer cache issues related to BUGs can be avoided by applying these patchsets. The following table summarize the most common BUGs related to buffer cache problems, possible workarounds and the patchset that fixes the problem.