首页 > 业界动态

我发觉，英文的一些技术文章在描述oracle原理非常的恰当到位，表达的非常的美，呵呵。英文不好，献丑翻译并学习了，认真看了，相信你受益匪浅。

Buffer Cache - Data Dictionary Views 数据字典视图
There are several Oracle fixed tables and views that provide valuable information about
the buffer cache and the new buffer pools.
这里将介绍几个很有价值的oracle静态视图并且提供很有价值的信息关于高速缓冲存储器和新的缓冲池；

The X$BH Fixed Table
Perhaps the most complete source of information about the Oracle buffer cache is the X$BH
fixed table. This table contains one row for every buffer in the buffer cache. There are
more than 50 columns in this table, and they are not officially documented. Much of the
information is relevant mostly to Oracle Parallel Server environments, where buffer cache
coordination between instances introduces additional complexity. The following table
includes interesting and useful information on some of the columns in X$BH.

或许大多的有关oracle高速缓冲缓存器的已经完成的资源信息都存储在X$BH表中；这个表为每个高速缓冲缓存
缓存器缓存块都记录了一行，有超过50列在这个表中，并且它们没有正式的文档说明，其中的许多信息和oracle
数据库的并行服务器环境有关；这样环境中的高速缓冲缓存器在实例之间协调引入了额外的复杂性事务。下面的表包含
了一些有趣和有用的信息在一些X$BH表的列上；
Column   Data Type   Description
ADDR   RAW      Address of buffer in SGA
BUF#   NUMBER    Sequential number of buffer in cache
OBJ   NUMBER    Object id of segment owning block in the buffer
TS#   NUMBER    Tablespace number owning block in the buffer
FILE#   NUMBER    File number owning block in the buffer
DBARFIL   NUMBER    Relative file number of the block in the buffer
DBABLK   NUMBER    Block number in file of block in the buffer
SET_DS   RAW      Joins to X$KCBWDS.ADDR to identify working set of buffer
CLASS   NUMBER    Block class (1 = data block, 4 = header block)
STATE   NUMBER    Used to determine status of block (e.g. FREE)
FLAG   NUMBER    Encodes various information about the block, including dirty status and whether read sequentially or random
NEXT_REPL  RAW      Address of next buffer on LRU list
PRV_REPL  RAW      Address of previous buffer on LRU list

As with the other internal fixed tables, X$BH can only be queried by the SYS user.
Oracle provides a view called V$BH which externalizes X$BH, however V$BH is designed
specifically for Oracle Parallel Server installations and will not display information
when queried from a non-OPS instance. This is unfortunate, since X$BH has such
interesting information. Another shortcoming of X$BH is that it does not tell directly
 which buffer pool a block currently belongs to, which can be very useful information
 when testing this new feature.
 
作为内部的静态数据表，X$BH表只能被sys用户查询；oracle数据库提供了一个额外的视图V$BH；
V$BH视图被设计为oracle的并发数据库并且当一个非OPS的实例查询时并不现实信息；这将是不辛的
一旦X$BH视图含有有趣的信息。另一个X$BH的缺点是不能直接的告知缓冲池块目前的归属，本身这些信息
对于这个新特性的测试是很有用的。

The Z$BUFFER_CACHE View
A view called Z$BUFFER_CACHE has been created to overcome some of the shortcomings of X$BH. This view
 externalizes some of the useful X$BH information noted above as well as joining with other X$ tables
 to determine the buffer pool of each block. One interesting fact is that these other X$ tables have
 changed somewhat between Oracle8.0 and Oracle8i, so two different versions of the view definition
  are required.The following code segments define the view Z$BUFFER_CACHE. They must be run as SYS
  and then SELECT privilege on the view granted out to other users requiring buffer cache information.
 
 有个视图叫Z$BUFFER_CACHE，这个视图弥补了一些X$BH的缺点，通过结合一些X$视图得到每个数据块的缓冲池
 使得X$BH视图的有用信息更具体化，一个有趣的事实是，在oracle8和oracle8i之间一些x$视图已经发生了变化；
 因此两个不同版本的视图定义要求是不同的。
 其中结合的X$视图：
  x$kcbwbpd
  x$bh
 x$kcbwds
*******************************************

V$BUFFER_POOL and V$BUFFER_POOL_STATISTICS
The V$BUFFER_POOL view supplies basic information about how many
LRU working sets and buffers are assigned to each pool, and the range of buffers assigned
 to each pool. The underlying fixed table is X$KCBWBPD, which is joined to X$BH in the
 V$BUFFER_POOL_STATISTICS view to associate each buffer with the pool it belongs to. Normally V$BUFFER_POOL
  will not be very useful except to verify the buffer pool configuration.
The V$BUFFER_POOL_STATISTICS view contains lots of information about buffer cache performance.
It is created by the CATPERF.SQL script located in the <ORACLE_HOME>/rdbms/admin directory.
CATPERF.SQL is not executed automatically when the database is created so it must be explicitly
 executed (as SYS) to create the view. The following table lists the columns in V$BUFFER_POOL_STATISTICS.
 
 V$BUFFER_POOL视图提供了基本的信息关于有多少的LRU工作组和多少个缓存块被放到每一个池中；也包括被分配给每个
 池的缓存块的范围；最根本的静态表是X$KCBWBPD，这个表被结合X$BH表得到的信息形成了V$BUFFER_POOL_STATISTICS视图
 ；这个视图体现了每个缓存块以及所属的池。通常来说V$BUFFER_POOL不是非常有用的，除了验证缓冲池的配置；
 V$BUFFER_POOL_STATISTICS视图包含许多高速缓冲缓存器的性能信息；这个视图被 CATPERF.SQL脚本所创建，这个脚本在
 <ORACLE_HOME>/rdbms/admin目录下。CATPERF.SQL不是自动执行的，所以你需要以sys用户登录并且明确来创建这个视图。
 oracle10g中已经是自动就执行建立的。
 下面这些表的咧就是视图V$BUFFER_POOL_STATISTICS的：
 *********************************************************************************
 Column      Data Type   Description
ID    NUMBER    Buffer pool number
NAME    VARCHAR2(20) Buffer pool name
SET_MSIZE   NUMBER    Maximum set size
CNUM_REPL   NUMBER    Total buffers on LRU lists
CNUM_WRITE   NUMBER    Total buffers on dirty lists
BUF_GOT    NUMBER    Total buffers gotten
SUM_WRITE   NUMBER    Total buffers written
SUM_SCAN   NUMBER    Total buffers scanned
FREE_BUFFER_WAIT  NUMBER    Total free buffer wait events (V$SYSTEM_EVENT)
WRITE_COMPLETE_WAIT  NUMBER   Total write complete wait events (V$SYSTEM_EVENT)
BUFFER_BUSY_WAIT  NUMBER    Total buffer busy wait events (V$SYSTEM_EVENT)
FREE_BUFFER_INSPECTED  NUMBER    Total free buffer inspected (V$SYSSTAT)
DIRTY_BUFFERS_INSPECTED  NUMBER    Total dirty buffers inspected (V$SYSSTAT)
DB_BLOCK_CHANGE   NUMBER    Total block changes (V$SYSSTAT)
DB_BLOCK_GETS   NUMBER    Total block gets (V$SYSSTAT)
CONSISTENT_GETS   NUMBER    Total consistent gets (V$SYSSTAT)
PHYSICAL_READS   NUMBER    Total physical reads (V$SYSSTAT)
PHYSICAL_WRITES   NUMBER    Total physical writes (V$SYSSTAT)
 *******************************************************************************
 The statistics in V$BUFFER_POOL_STATISTICS represent totals across all working sets for each pool.
  Many of the columns give performance statistics at the buffer pool level that were previously only
  available at the instance level in either V$SYSSTAT or V$SYSTEM_EVENT.
                                                                                                                                                                                                                                                                           
 The V$BUFFER_POOL_STATISTICS view is key to analyzing the performance of multiple Oracle buffer pools.
  For instance, we can calculate the individual buffer pool hit ratios using the following SQL:    
  视图 V$BUFFER_POOL_STATISTICS记录了每个池的所有交叉的工作组。许多的列给出了性能统计把缓存池层面，先前这些只是在实例
  层面可用,及不是V$SYSSTAT或者V$SYSTEM_EVENT。
   V$BUFFER_POOL_STATISTICS视图是关键的用来分析多个oracle缓冲池的性能；对于实例来说，我们能计算单独的缓冲池命中率使用下面的sql语句
   SELECT  name   buffer_pool
       ,100 * (1-(physical_reads/(db_block_gets+consistent_gets)))
               hit_ratio
  FROM  v$buffer_pool_statistics;