首页 > Linux操作系统 > Linux操作系统 > Oracle Buffer Cache原理

Oracle Buffer Cache原理

Linux操作系统 作者:keeptrying 时间:2013-11-19 10:29:27 0 删除 编辑


The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.

This section includes the following topics:

1Organization of the Database Buffer Cache

The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list.  The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.

When an Oracle Database process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.

The first time an Oracle Database user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list.  The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.

If an Oracle Database user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

2The LRU Algorithm and Full Table Scans

When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.

You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.

二、Buffer Cache说明

 Buffer cache is to minimize physical io. When a block is read by Oracle, it places this block into the buffer cache, because there is a chance that this block is needed again. Reading a block from the buffer cache is less costly (in terms of time) than reading it from the disk.

1MRU and LRU blocks

Blocks within the buffer cache are ordered from MRU (most recently used) blocks to LRU (least recently used) blocks. Whenever a block is accessed, the block goes to the MRU end of the list, thereby shifting the other blocks down towards the LRU end. When a block is read from disk and when there is no buffer available in the db buffer cache, one block in the buffer cache has to "leave". It will be the block on the LRU end in the list.

However, blocks read during a full table (multi block reads are placed on the LRU side of the list instead of on the MRU side.

The v$bh dynamic view has an entry for each block in the buffer cache. The time a block has been touched most recently is recorded in tim of x$bh.

2Touch count

Each buffer has an associated touch count. This touch count might be increased if a buffer is accessed (although it needs not always be). It is valid to claim that the higher the touch count, the more important (more used) the buffer.  Therefore, buffers with a high touch count should stay in the buffer cache while buffers with a low touch count should age out in order to make room for other buffers.

A touch time can only be increased once within a time period controlled by the parameter  _db_aging_touch_time (default: 3 seconds).

The touch count is recorded in the  tch column of x$bh.

 By the way, it looks like Oracle doesn't protect manipulations of the touch count in a buffer with a latch. This is interesting because all other manipulations on the LRU list are protected by latches. A side effect of the lack of latch-protection is that the touch count is not incremented if another process updates the buffer header.


Information on buffer headers. Contains a record (the buffer header) for each block in the buffer cache.  

This select statement lists how many blocks are Available, Free and Being Used.





                      1,DECODE(lrba_seq,0,'Available','Being Used'),

                      3,'Being Used',




         FROM x$bh


GROUP BY state;






no valid block image



a current mode block, exclusive to this instance



a current mode block, shared with other instances



a consistent read (stale) block image



buffer is reserved for a block being read from disk



a block in media recovery mode



a block in instance (crash) recovery mode


tch is the touch count. A high touch count indicates that the buffer is used often. Therefore, it will probably be at the head of the MRU list.


touch time.


represents a value designated for the use of the block.



maps to addr on x$kcbwds.


can be outer joined on x$le.le_addr.

(8)flag: is a bit array.


if set


Block is dirty


temporary block

9 or 10






524288 (=0x80000)

Block was read in a  full table scan.

4Different pools within the cache

The cache consists actually of three buffer pools for different purposes.

1)、Keep pool

The keep pool's purpose is to take small objects that should always  be cached, for example Look Up Tables. See db_keep_cache_size.

2)、Recycle pool

The recycle pool is for larger objects.  See db_recycle_cache_size.

3)、Default pool

 The default pool is for everything else. See also x$kcbwbpd.


Buffer pool descriptor, the base table for v$buffer_pool.

 How is the buffer cache split between the default, the recycle and the keep buffer pool.

5Cold and hot area

Each pool's LRU is divided into a hot area and a cold area. Accordingly, buffers with in the hot area are hot buffers (and buffers in the cold are are called cold buffers).

By default, 50% of the buffers belong to the cold area and the other 50% belong to the hot area. This behaviour can be changed with_db_percent_hot_default (for the default pool) _db_percent_hot_recycle (for the recycle pool) and _db_percent_hot_keep (for the keep pool).

A newly read db block will be inserted between the cold and the hot area such that it belongs to the hot area. This is called midpoint insertion. However, this is only true for single block readsmulti block reads will be placed at the LRU end.

6Flushing the cache

With Oracle 10g it is possible to flush the buffer cache with:


SQL> alter system flush buffer_cache;

9i had an undocumented command to flush the buffer cache:

SQL>alter session set events = 'immediate trace name flush_cache';

7Optimal Size

Some common wisdom says that the larger the buffer cache is, the better the performance of the database becomes. However, this claim is not always true.

To begin with, the cache needs to be managed. The bigger the cache, the larger the LRU and dirty list becomes. That results in longer search times for a free buffer (buffer busy waits).

 Also, the bigger the cache, the greater the burden on the DBWn process.

DB Cache不是越大越好,如果DB Cache过大,会造成大的LRU列表和dirty list。这会增加list的扫描时间。同时大的cache也会增加DBWn进程的负担。

8Buffer Cache中的管理结构

Buffer CacheSGA的一部分,Oracle利用Buffer Cache来管理data blockBuffer Cache的最终目的就是尽可能的减少磁盘I/O

Buffer Cache中主要有3大结构用来管理Buffer Cache

(1).Hash Bucket  Hash Chain ListHash BucketHash Chain List用来实现data block的快速定位。

(2). LRU List挂载有指向具体的free buffer, pinned buffer以及还没有被移动到 write listdirty buffer 等信息。所谓的free buffer就是指没有包含任何数据的buffer,所谓的pinned buffer,就是指当前正在被访问的buffer

(3). Write(Dirty)List挂载有指向具体的 dirty block的信息。所谓的dirty block,就是指在 buffer cache中被修改过但是还没有被写入到磁盘的block

8.1 Hash BucketHash Chain List

Oraclebuffer cache中所有的buffer通过一个内部的Hash算法运算之后,将这些buffer放到不同的 Hash Bucket中。每一个Hash Bucket中都有一个Hash Chain List,通过这个list,将这个Bucket中的block串联起来

要查看Hash Chain List组成可以通过x$bh字典.

SQL> desc x$bh

 Name               Null?    Type

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

ADDR               RAW(8)    ---blockbuffer cache中的address


HLADDR          RAW(8)   --latch:cache buffers chains address


NXT_HASH      RAW(8) ---指向同一个Hash Chain List的下一个block

PRV_HASH      RAW(8) ---指向同一个Hash Chain List的上一个block

NXT_REPL       RAW(8)  ---指向LRU list中的下一个block

PRV_REPL       RAW(8)  ---指向LRU list中的上一个block


Hash Chain List就是由x$bh中的NXT_HASHPRV_HASH 2个指针构成了一个双向链表,其示意图如下:

通过NXT_HASHPRV_HASH2个指针,那么在同一个Hash Chain Listblock就串联起来了。

理解了Hash Bucket  Hash Chain List,我们现在来看看Hash Bucket  Hash Chain List管理Buffer Cache 的结构示意图

这个图和Shared Pool 有点类似。从图中我们可以看到,一个latch: cache buffers chains(x$bh.hladdr) 可以保护多个Hash Bucket,也就是说,如果我要访问某个block,我首先要获得这个latch,一个Hash Bucket对应一个Hash Chain List,而这个Hash Chain List挂载了一个或者多个Buffer Header

Hash Bucket的数量受隐含参数_db_block_hash_buckets的影响;

Latch:cache buffers chains的数量受隐含参数_db_block_hash_latches的影响



SELECT x.ksppinm NAME,y.ksppstvl VALUE,x.ksppdesc DESCRIB

FROM x$ksppi x, x$ksppcv y

WHERE x.inst_id = USERENV ('Instance')

      AND y.inst_id = USERENV ('Instance')

      AND x.indx = y.indx

      AND x.ksppinm LIKE '%_db_block_hash%'


NAME                     VALUE      DESCRIB

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

_db_block_hash_buckets   8192  Number of database block hash buckets

_db_block_hash_latches   1024   Number of database block hash latches


_db_block_hash_buckets 该隐含参数在8i以前等于db_block_buffers/4的下一个素数,到了8i 该参数等于 db_block_buffers*2 ,到了9i 以后,该参数取的是小于且最接近db_block_buffers*2 的一个素数。

_db_block_hash_latches 该隐含参数表示的是 cache buffers chains latch的数量。

可以用下面查询cache buffers chains latch的数量:

SQL> select count(*) from v$latch_children a,v$latchname b where a.latch#=b.latch# and'cache buffers chains';




也可以用下面查询cache buffers chains latch的数量:

SQL> select count(distinct hladdr) from x$bh;






一个cache buffers chains latch平均下来要管理32Hash Bucket,现在找一个latch,来验证一下前面提到的结构图。

SQL> select * from (select hladdr,count(*) from x$bh group by hladdr) where rownum<=5;


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

25989DA0          5

25989E60          1

25989F20          5

25989FE0          1

2598A0A0          4

我们查询latch address25989DA0所保护的data block

SQL> select hladdr,obj,dbarfil,dbablk,nxt_hash,prv_hash from x$bh where hladdr='25989DA0' order by obj;


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

25989DA0     2       1       7744  117E6A54  25989E18

25989DA0    18       1      29667  25989E18  10FF45E8

25989DA0    73       1       8443  10BE62FC  25989E40

25989DA0   517       1       4105  25989E40  113F43B4

25989DA0  8969       3      15255  25989E48  25989E48

注意DBA(3,15255),它的NXT_HASHPRV_HASH相同,也就是说DBA(3,15255)挂载在只包含有1data blockHash Chain




(2)获得cache buffers chains latch

(3)到相应的Hash Bucket中搜寻相应的Buffer Header

(4)如果找到相应的Buffer Header,然后判断该Buffer的状态,看是否需要构造CR Block,或者Buffer处于pin的状态,最后读取。

(5)如果找不到,就从磁盘读入到Buffer Cache中。

Oracle 9i以前,如果其他用户进程已经获得了这个latch,那么新的进程就必须等待,直到该用户进程搜索完毕(搜索完毕之后就会释放该latch)。

Oracle 9i开始,cache buffers chains latch可以只读共享,也就是说用户进程A以只读(select)的方式访问Block(1,7744),这个时候获得了该latch,同时用户进程B也以只读的方访问Block(1,7744),那么这个时候由于是只读的访问,用户进程B也可以获得该latch。但是,如果用户进程B要以独占的方式访问Block(1,7744),那么用户进程B就会等待用户进程A释放该latch这个时候Oracle就会对用户进程B标记一个latch:cache buffers chains的等待时间

一般来说,导致latch:cache buffers chains的原因有如下三种:


大量逻辑读的SQL语句就有可能产生非常严重的latch:cache buffers chains等待,因为每次要访问一个block,就需要获得该latch,由于有大量的逻辑读,那么就增加了latch:cache buffers chains争用的机率。

(1)对于正在运行的SQL语句,产生非常严重的latch:cache buffers chains争用,可以利用下面SQL查看执行计划,并设法优化SQL语句。

SQL> select * from table(dbms_xplan.display_cursor(‘sql_id’,sql_child_number));

(2)如果SQL已经运行完毕,我们就看AWR报表里面的SQL Statistics->SQL orderd by Gets->Gets per Exec,试图优化这些SQL


(1)下面查询出Top 5的争用的latch address







       FROM  v$latch_children

       where name='cache buffers chains'

             AND misses>0

             AND sleeps>0

       ORDER BY 5 DESC,






(2)然后利用下面查询找出Hot block

SELECT  e.owner || ',' || e.segment_name segment_name,

         e.extent_id extent#,

         x.dbablk - e.block_id + 1 block#,




FROM   v$latch_children l,x$bh x,dba_extents e

WHERE  x.hladdr = '&ADDR'

     AND e.file_id=x.file#

     AND x.hladdr=l.addr

     AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks -1



3.Hash Bucket太少

需要更改_db_block_hash_buckets隐含参数。其实在Oracle 9i之后,我们基本上就不会遇到这个问题了,除非遇到Bug,所以这个是不推荐的,记住,在对Oracle的隐含参数做修改之前一定要咨询Oracle Support

8.2 LRU ListWrite List

前面已经提到过了,如果一个用户进程发现某个block不在Buffer Cache中,那么用户进程就会从磁盘上将这个block读入Buffer Cache

在将block读入到Buffer Cache之前,首先要在LRU list上寻找Freebuffer,在寻找过程中,如果发现了Dirty Buffer就将其移动到 LRU Write List上。

如果Dirty Queue超过了阀值25%(如下面查询所示),那么DBWn就会将Dirty Buffer写入到磁盘中。

SYS@anqing2(rac2)> select kvittag,kvitval,kvitdsc from x$kvit where kvittag in('kcbldq','kcbfsp');



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

kcbldq      25 large dirty queue if kcbclw reaches this

kcbfsp     40 Max percentage of LRU list foreground can scan for free

根据上面的查询我们还知道,当某个用户进程扫描LRU list超过40%都还没找到Free Buffer,那么这个时候用户进程将停止扫描LRU list,同时通知DBWnDirty Buffer写入磁盘,用户进程也将记录一个free buffer wait等待事件。如果我们经常看到free buffer wait等待事件,那么我们就应该考虑加大Buffer Cache了。

Oracle8i开始,LRU ListDirty List都增加了辅助List(Aux List)OracleLRU ListLRU Write List统称为Working Set(WS)。每个WS中都包含了几个功能不同的List每个WS都会有一个Cache Buffers LRU chain Latch的保护(知识来源于DSI405)如果数据库设置了多个DBWR,数据库会存在多个WS,如果Buffer Cache中启用了多缓存池(default,keep,recycle)时,每个独立的缓冲池都会有自己的WS。那么下面我们来查询一下,以验证上述理论:

SYS@anqing2(rac2)> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

  2  FROM x$ksppi x, x$ksppcv y

  3  WHERE x.inst_id = USERENV ('Instance')

  4  AND y.inst_id = USERENV ('Instance')

  5  AND x.indx = y.indx

  6  AND x.ksppinm LIKE '%db_block_lru_latches%'

  7  /


NAME                     VALUE      DESCRIB

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

_db_block_lru_latches  32   number of lru latches


SYS@anqing2(rac2)> show parameter db_writer

NAME                   TYPE        VALUE

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

db_writer_processes         integer     2


SYS@anqing2(rac2)> show parameter cpu_count


NAME                     TYPE        VALUE

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

cpu_count                    integer       8

我们查到有32Cache Buffers LRU chain Latch,从Oracle9i开始,_db_block_lru_latchesCPU_COUNT4,如果DB_WITER_PROCESS小于4,置于DB_WITER_PROCESS大于四这个不知道,另外也没见过哪个数据库参数的DB_WITER_PROCESS大于4

查询一下有多少个Working Set:

SYS@anqing2(rac2)>select count(*) from x$kcbwds;




我们查询到有32WS,并不代表数据库就一定使用了这32WS有些WS 是数据库预分配的,这样在我们启用Keep pool, recycle pool的时候就不用重启数据库了。


SYS@anqing2(rac2)>select count(*) from x$kcbwds where CNUM_REPL>0;





ADDR       RAW(4)  --address

INST_ID      NUMBER  --instance number

SET_ID      NUMBER  --work set id

DBWR_NUM     NUMBER  --dbwr编号

BLK_SIZE     NUMBER  --worksetblock size

CKPT_LATCH    RAW(4)  --checkpoint latch

SET_LATCH     RAW(4)  --next replacement chain

NXT_REPL     RAW(4)  --prv  replacement chain

PRV_REPL     RAW(4)  --replacement aux chain



CNUM_REPL     NUMBER  --replacement chian上的block

ANUM_REPL     NUMBER  --aux chain上的block 

COLD_HD      RAW(4)  --cold head的地址

HBMAX       NUMBER  --hot端的最大buffer数量

HBUFS       NUMBER  --hot端的当前buffer数量

NXT_WRITE     RAW(4)  --lru-w

PRV_WRITE     RAW(4)  --lru-w

NXT_WRITEAX    RAW(4)  --lru-w aux

PRV_WRITEAX    RAW(4)  --lru-w aux

CNUM_WRITE     NUMBER  --lru-wbuffer

ANUM_WRITE     NUMBER  --lru-w auxbuffer

NXT_XOBJ      RAW(4)  --reuse obj链(当truncatedrop等操作时使用)

PRV_XOBJ      RAW(4)  --reuse obj

NXT_XOBJAX    RAW(4)  --reuse obj aux

NXT_XRNG   RAW(4)  --reuse range(tablespace offline等操作的时候使用)

NXT_XRNGAX    RAW(4)  --reuse range aux

注意红色字段,正是由于红色字段,以及前面提到过的x$bh中的NXT_REPLPRV_REPL 字段形成了LRU List 以及LRU Write List

下图就是LRU List的结构示意图:

新增的辅助listAUX List)作用:

在数据库启动之后,Buffer首先被存放在LRU AUX List上,用户进程搜索Free Buffer就会从LRU AUX List 的末/冷端进行。当这些块被修改后或者是用户进程要构造CR块的时候(要构造CR块也就表明这个块不满足读一致性,是Dirty),在LRU AUX List上的Buffer就会被移动到LRU Main List的中间,记住是中间不是头部也不是末尾,那么DBWR来搜索Dirty Buffer就可以从LRU Main List开始(注意:DBWR 来搜索LRU Main List 是由于增量检查点导致的)DBWR在搜索LRU Main List的时候如果发现冷的可以被重复使用的Buffer,就会将其移动到LRU AUX List上,这样搜索LRU Main List上的Buffer基本都是Dirty Buffer,提高了搜索效率。

DBWR将搜索到的Dirty Buffer移动到LRUW Main List,当需要将这个Dirty Buffer写出的时候,就把这个Dirty Buffer移动到LRUW AUX List,这样,当DBWR要执行写出可以从LRUW AUX List写出,这其实是一个异步的写出机制。(From Metalink: 157868.1)

根据上面的讲解,当用户进程要将Block从磁盘读入到Buffer Cache中需要获得Cache Buffers LRU chain Latch,或者是DBWR扫描LRU Main List的时候要获得Cache Buffers LRU chain Latch

所以,当我们发现AWR报表上面Cache Buffers LRU chain Latch排名很靠前,那么我们可以采取如下方法:

1)加大Buffer Cache,过小的Buffer Cache导致大量的磁盘I/O,必然引发Cache Buffers LRU chain Latch竞争。


3)使用多缓冲池技术,把Hot Segments Keep起来,Hot Segments的信息可以从AWR 报表中的Segments Statistics中得到。


三、Tuning Oracle’s Buffer Cache


Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.

 If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.

1Checking The Cache Hit Ratio

Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instancesince it was started:


FROM  v$sysstat P1, v$sysstat P2, v$sysstat P3

WHERE = 'db block gets'

      AND = 'consistent gets'

      AND = 'physical reads'

You can also see the buffer cache hit ratio for one specific session since that session started:


FROM  v$sesstat P1,

       v$statname N1,

       v$sesstat P2,

       v$statname N2,

       v$sesstat P3,

       v$statname N3

WHERE = 'db block gets'


      AND P1.SID = <enter SID of session here>

      AND N2.NAME = 'consistent gets'


      AND P2.SID = P1.SID

      AND N3.NAME = 'physical reads'


      AND P3.SID = P1.SID

You can also measure the buffer cache hit ratio between time X and time Y by collecting statistics at times X and Y and computing the deltas.

2Adjusting The Size Of The Buffer Cache

The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache (in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.

You can change the size of the buffer cache by editing the db_block_buffers parameter in the parameter file and restarting the instance.

3Determing If The Buffer Cache Should Be Enlarged

If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.

For example, if you set db_block_lru_extended_statistics to 1000 and restart the instance, you can see how the buffer cache hit ratio would have improved if the buffer cache were one buffer bigger, two buffers bigger, and so on up to 1000 buffers bigger than its current size. Following is a query you can use, along with a sample result:

 SELECT   250 * TRUNC (ROWNUM / 250)

         + 1

         || ' to '

         || 250 * (TRUNC (ROWNUM / 250) + 1)


         SUM (COUNT) "Buffer Cache Hits"

    FROM v$recent_bucket



Interval           Buffer Cache Hits

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

     1 to 250                16083

     251 to 500               11422

     501 to 750                683

     751 to 1000               177

 This result set shows that enlarging the buffer cache by 250 buffers would have resulted in 16,083 more hits. If there were about 30,000 hits in the buffer cache at the time this query was performed, then it would appear that adding 500 buffers to the buffer cache might be worthwhile. Adding more than 500 buffers might lead to under-utilized buffers and therefore wasted memory.

There is overhead involved in collecting extended LRU statistics. Therefore you should set the db_block_lru_extended_ statistics parameter back to zero as soon as your analysis is complete.

In Oracle7, the v$recent_bucket view was named X$KCBRBH. Only the SYS user can query X$KCBRBH. Also note that in X$KCBRBH the columns are called indx and count, instead of rownum and count.

4Determining If The Buffer Cache Is Bigger Than Necessary

If you set the db_block_lru_statistics parameter to true in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$current_bucket. This view will contain one row for each buffer in the buffer cache, and each row will indicate how many of the overall cache hits have been attributable to that particular buffer.

By querying v$current_bucket with a GROUP BY clause, you can get an idea of how well the buffer cache would perform if it were smaller. Following is a query you can use, along with a sample result:

 SELECT   1000 * TRUNC (rownum / 1000) + 1 || ' to ' ||

              1000 * (TRUNC (rownum / 1000) + 1) "Interval",

              SUM (count) "Buffer Cache Hits"

     FROM     v$current_bucket

     WHERE    rownum > 0

     GROUP BY TRUNC (rownum / 1000)


     Interval     Buffer Cache Hits

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

     1 to 1000               668415  

     1001 to 2000            281760  

     2001 to 3000            166940  

     3001 to 4000             14770   

     4001 to 5000              7030    

     5001 to 6000               959


This result set shows that the first 3000 buffers are responsible for over 98% of the hits in the buffer cache. This suggests that the buffer cache would be almost as effective if it were half the size; memory is being wasted on an oversized buffer cache.

There is overhead involved in collecting LRU statistics. Therefore you should set the db_block_lru_statistics parameter back to false as soon as your analysis is complete.

In Oracle7, the v$current_bucket view was named X$KCBCBH. Only the SYS user can query X$KCBCBH. Also note that in X$KCBCBH the columns are called indx and count, instead of rownum and count.

5Full Table Scans

When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.

Full table scans of large tables usually result in physical disk reads and a lower buffer cache hit ratio. You can get an idea of full table scan activity at the data file level by querying v$filestat and joining to SYS.dba_data_files. Following is a query you can use and sample results:

SELECT A.file_name, B.phyrds, B.phyblkrd

FROM SYS.dba_data_files A, v$filestat B

WHERE B.file# = A.file_id

ORDER BY A.file_id



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

/u01/oradata/PROD/system01.dbf    92832     130721

/u02/oradata/PROD/temp01.dbf     1136      7825

/u01/oradata/PROD/tools01.dbf     7994      8002

/u01/oradata/PROD/users01.dbf     214       214

/u03/oradata/PROD/rbs01.dbf      20518     20518

/u04/oradata/PROD/data01.dbf     593336    9441037

/u05/oradata/PROD/data02.dbf     4638037    4703454

/u06/oradata/PROD/index01.dbf     1007638    1007638

/u07/oradata/PROD/index02.dbf     1408270    1408270

PHYRDS shows the number of reads from the data file since the instance was started.

PHYBLKRD shows the actual number of data blocks read. Usually blocks are requested one at a time. However, Oracle requests blocks in batches when performing full table scans. (The db_file_multiblock_read_count parameter controls this batch size.)

In the sample result set above, there appears to be quite a bit of full table scan activity in the data01.dbf data file, since 593,336 read requests have resulted in 9,441,037 actual blocks read.

6Spotting I/O Intensive SQL Statements

The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:

SELECT executions,





FROM v$sqlarea

ORDER BY disk_reads

EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area.

 BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement.

DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)

 You can review the results of this query to find SQL statements that perform lots of reads, both logical and physical. Consider how many times a SQL statement has been executed when evaluating the number of reads.


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量