7.2 Configuring and Using the Buffer Cache
For many types of operations, Oracle Database uses the buffer cache to store blocks read from disk. Oracle Database bypasses the buffer cache for particular operations, such as sorting and parallel reads. For operations that use the buffer cache, this section explains the following:
对于许多类型的操作，oracle使用缓存存储从磁盘中读取的块。对于有些特殊的操作,oracle会绕过buffer cache，例如排序和并行读。对于需要使用buffer cache的操作，这节解释了以下内容：（buffer cache就是缓存，但是我在翻译的时候仍然写buffer cache的英文名字，以示强调；如果写中文这个字的重要性就会淹没在其他文字中）
- Using the Buffer Cache Effectively 有效地使用缓存
- Sizing the Buffer Cache 设置缓存的大小
- Interpreting and Using the Buffer Cache Advisory Statistics 解析和使用缓存建议统计
- Considering Multiple Buffer Pools 使用多缓存池
7.2.1 Using the Buffer Cache Effectively
To use the buffer cache effectively, tune SQL statements for the application to avoid unnecessary resource consumption. To meet this goal, verify that frequently executed SQL statements and SQL statements that perform many buffer gets have been tuned.
When using parallel query, you can configure the database to use the database buffer cache instead of performing direct reads into the PGA. This configuration may be appropriate when the database servers have a large amount of memory.
为了有效的使用buffer cache，调试你的应用程序中的SQL语句，以避免过多的消耗资源。为了达到这个目标，确认那些频繁执行的SQL语句，以及那些需要许多buffer gets的语句。
当调试并行查询时，你可以配置数据库去使用数据库的buffer cache而不是使用直接路径读到PGA中。这种配置适合有特大内存的服务器。
See Also:
- Chapter 16, "SQL Tuning Overview"
- Oracle Database VLDB and Partitioning Guide to learn more using parallel execution
When configuring a new instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured.
当配置一个实例时，知道如何正确的配置buffer cache是非常重要的。通常情况下，DBA先去评估一下大概需要多少cache大小，然后进行压力测试，最后检查一下相关的统计数据以了解这个缓存是大了还是小了。
7.2.2.1 Buffer Cache Advisory Statistics
You can use several statistics to examine buffer cache activity, including the following:
你可以使用许多的统计数据去检查buffer cache的活动，包括下面： v$db_cache_advice, hit ratio
7.2.2.2 Using V$DB_CACHE_ADVICE
This view is populated when the DB_CACHE_ADVICE initialization parameter is set to ON. This view shows the simulated miss rates for a range of potential buffer cache sizes.
Each cache size simulated has its own row in this view, with the predicted physical I/O activity that would take place for that size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be enabled and disabled dynamically to allow you to collect advisory data for a specific workload.
There is some overhead associated with this advisory. When the advisory is enabled, there is a small increase in CPU usage, because additional bookkeeping is required.
Oracle Database uses DBA-based sampling to gather cache advisory statistics. Sampling substantially reduces both CPU and memory overhead associated with bookkeeping. Sampling is not used for a buffer pool if the number of buffers in that buffer pool is small to begin with.
To use V$DB_CACHE_ADVICE, the parameter DB_CACHE_ADVICE should be set to ON, and a representative workload should be running on the instance. Allow the workload to stabilize before querying the V$DB_CACHE_ADVICE view.
当db_cache_advice设置成On的时候，这个视图就会被填充值了。这个视图显示了在各种buffer cache大小下，一种假想的未命中的比率。（就是根据目前的情况推测命中率有多高）
每个假想的缓存大小都是一行记录，它包含了预测的IO大小活动。db_cache_advice是一个动态参数，所以你可以动态的打开或关闭收集特定压力的顾问。
这个顾问对系统会有一些开销。当顾问打开时，CPU会有一些增加，因为有一些额外的记录处理。
oracle数据库使用基于DBA的采样去收集缓存顾问数据。
为了使用v$db_cache_advice， 参数db_cache_advice一定要打开，并且实例上要有一定的压力活动。当压力稳定以后再去查询这个 v$db_cache_advice 视图。
The following SQL statement returns the predicted I/O requirement for the default buffer pool for various cache sizes:
下面这个SQL语句是查看default buffer pool的默认block_size块，在advice_status打开时的统计情况（有3种buffer pool: default,keep,recycle）
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';
The following output shows that if the cache was 212 MB, rather than the current size of 304 MB, the estimated number of physical reads would increase by a factor of 1.74 or 74%. This means it would not be advisable to decrease the cache size to 212MB.
However, increasing the cache size to 334MB would potentially decrease reads by a factor of .93 or 7%. If an additional 30MB memory is available on the host computer and the SGA_MAX_SIZE setting allows the increment, it would be advisable to increase the default buffer cache pool size to 334MB.
下面的输出结果显示假如cache是212M，而不是现在的304M，那么评估下来的物理读将会增加1.74个读因子或者是增加74%的物理读。这就意味着将cache大小减到212M是不明智的。
然而，增加cache大小到334M却可能将物理读降低到0.93个因子或者降低7%的物理读。假如你的主机上有额外的30M内存的话，并且sga_max_size允许增加这么大，那么将内存增加到334M是明智的了。
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943 10% of Current Size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475 Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 200% of Current Size (304 * 2 = 608,所以这是在缓存开到当前2倍时的表现)
This view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size. The data also includes a physical read factor, which is a factor by which the current number of physical reads is estimated to change if the buffer cache is resized to a given value.
这个视图有助于帮助调整cache大小，它提供了预测的各种cache大小时的物理读信息。它同时提供了物理读因子，这个因子是根据当前的cache大小与改变后的cache大小计算的一个值。
Note: 在oracle数据库里，物理读并不意味着磁盘读；因为物理读可能是从操作系统是缓存中读取的（如果使用裸设备或者ASM的话就没有缓存了，操作系统没缓存是最好的）
With Oracle Database, physical reads do not necessarily indicate disk reads; physical reads may well be satisfied from the file system cache.
The relationship between successfully finding a block in the cache and the size of the cache is not always a smooth distribution. When sizing the buffer pool, avoid the use of additional buffers that contribute little or nothing to the cache hit ratio. In the example illustrated in Figure 7-1, only narrow bands of increments to the cache size may be worthy of consideration.
成功在cache中找到一个块和cache的大小之间并不是一个平滑的分布。调整buffer pool时，增加内存对于cache命中率只有很少的作用时，避免使用额外的buffer。
在下面的图中，只有在那个虚线窄处增加相应的内存才是值得考虑的
Figure 7-1 Physical I/O and Buffer Cache Size
Examining Figure 7-1 leads to the following observations: 通过查看这个图可以得出下面的结果：
通过增加Buffers（横坐标），从A点到B点的效果要好于B点到C点。
从A->B和从B->C阶段IO的降低并不是平滑的，就像图中的虚线表示的一样
- The benefit from increasing buffers from point A to point B is considerably higher than from point B to point C.
- The decrease in the physical I/O between points A and B and points B and C is not smooth, as indicated by the dotted line in the graph.
7.2.2.3 Calculating the Buffer Cache Hit Ratio 计算buffer cache命中率
The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the dynamic performance view V$SYSSTAT. You can use the buffer cache hit ratio to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.
buffer cache命中率是指多少块的请求可以在缓存中找到，而不用去访问硬盘。这个命中率可以通过v$sysstat中的相关数据计算出来。你可以使用这个buffer cache命中率去辩认v$db_cache_advice 中预测的物理IO。
（在DB2中，这个命中率是通过一个命令就可以看到的：db2pd -d dbnameXXX -bufferpools,在DB2中每个表空间都要关联一个buffer pool，通过这条命令就可以直到每个buffer pool的命中率；虽然看起来DB2看直观，实际上oracle将所有的都统一到SQL中，而在DB2中需要记忆许多这样的命令，很麻烦的）
The statistics in Table 7-1 are used to calculate the hit ratio.
Table 7-1 Statistics for Calculating the Hit Ratio
| Statistic
| Description
|
| Number of times a consistent read was requested for a block from the buffer cache. 从buffer cache中请求CR的次数 | |
| Number of times a CURRENT block was requested from the buffer cache. 从buffer cache中请求CURRENT块的次数 | |
| Total number of data blocks read from disk into buffer cache. 多少数据块被从磁盘中读取到buffer cache中了 |
Example 7-1 has been simplified by using values selected directly from the V$SYSSTAT table, rather than over an interval. It is best to calculate the delta of these statistics over an interval while your application is running, then use them to determine the hit ratio.
其实使用7-1例子中直接从v$sysstat 表计算是一个简化的方法，这不是通过周期计算的。最好的办法是在系统运行时，根据一个周期去计算增量值，然后用增量值计算这个命中率。
See Also:
Chapter 6, "Automatic Performance Diagnostics" for more information on collecting statistics over an interval
Example 7-1 Calculating the Buffer Cache Hit Ratio
FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
Using the values in the output of the query, calculate the hit ratio for the buffer cache with the following formula:
计算buffer cache的命中率公式：
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache'))
1 – 23824247873 / (503739429139 + 52882355546) = 95.71% (这是根据建设银行一个系统上的值计算出来的)
为什么要使用减 物理读/缓存读，而没有一个直接计算的公式呢？
那我们假设不使用1减去这种方式算，那我们应该知道命中的个数才能算出来，即 命中的块数/(CR块读+当前块读)，这样就直接得出了命中率。
可是命中的块数如何得到呢？ 当发现可以从缓存中找到需要的块时就给一个变量加1（假如我起个名字是“命中块”变量），一直这样累加不就可以了？是的，技术是可行的，但是物理读是相对比较少的动作，而且读的过程很慢，当读完之后再去更新这个物理读变量要比每访问一个内存块都去更新一个“命中块”要容易的多。因为在更新之前也要进行加锁操作，我也称它为latch操作吧，即latch锁就占用很高了。因此我觉得oracle在这里是经过深入思考的，设计是非常合理的，虽然有点麻烦，但是是可以接受的合理设计。
这个v$sysstat视图是非常值得好好研究的
7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics
There are many factors to examine before considering whether to increase or decrease the buffer cache size. For example, you should examine V$DB_CACHE_ADVICE data and the buffer cache hit ratio.
A low cache hit ratio does not imply that increasing the size of the cache would be beneficial for performance. A good cache hit ratio could wrongly indicate that the cache is adequately sized for the workload.
在考虑增加或减少buffer cache大小时有许多因素需要考虑。比如你要考虑 v$db_cache_advice 数据以及buffer cache命中率。
低命中率并不意味着增加buffer cache就可以提升性能。一个高命中率或许错误的引导你认为在现有的压力下cache大小是足够的。
To interpret the buffer cache hit ratio, you should consider the following: 为解释buffer cache命中率，你应该考虑以下：
- Repeated scanning of the same large table or index can artificially inflate a poor cache hit ratio. Examine frequently executed SQL statements with a large number of buffer gets, to ensure that the execution plan for such SQL statements is optimal. If possible, avoid repeated scanning of frequently accessed data by performing all of the processing in a single pass or by optimizing the SQL statement.
重复扫描相同的大表或者索引可能人为的造成低的缓存命中率。检查一下频繁执行的拥有很大的buffer gets的SQL语句，确保这些SQL的执行计划是最优的。通过将所有的过程放在一起去执行避免重复的扫描频繁访问的数据，或者通过优化SQL语句去提升。
- If possible, avoid requerying the same data, by caching frequently accessed data in the client program or middle tier.
如果可能的话，避免重新查询相同的数据，通过将频繁访问的数据缓存到客户端程序或者中间层。（一般我们都是缓存到自己的共享内存中，或者使用后面会介绍的result cache，也可以做到这一点）
- Database blocks accessed during a long full table scan are put on the tail end of the least recently used LRU list and not on the head of the list. Therefore, the blocks are aged out faster than blocks read when performing indexed lookups or small table scans. When interpreting the buffer cache data, poor hit ratios when valid large full table scans are occurring should also be considered.
Note:
Short table scans are scans performed on tables under a certain size threshold. The definition of a small table is the maximum of 2% of the buffer cache and 20, whichever is bigger.
在一个长的全表扫描的块会被放到LRU列表的最后，而不是链表的前面。因此这些块会比其他用index或者小表扫描的块更快地替换出去。当你查看buffer cache数据时，当大的全表扫描时造成的低命中率是应该被考虑进去的。
注意： 小表扫描是在一定的阈值下执行的表扫描。小表的定义是buffer cache的2%和20个块中的最大值，否则就算大表。（如果缓存的2%小于20个块，那么小表是20个块；如果缓存的2%大于20个块，那么小表就是按这个缓存的2%来算；在建行系统上，有25G的sga_target值，大约有22G的buffer_cache，所以22G*2% = 440M，如果一条记录是1K大小，那么440M/1K = 440,000即44万条记录都算小表了！因此我觉得这个是有问题的，也许这个文档是对9i以前的系统可能有帮助，因为当时内存还没这么便宜，而现在服务器已经不一样了，配置几百G内存的服务器很司空见惯了，对吧！我个人觉得1000条以内可以算小表，其他都应该按大表算；如果一条记录是1K，一个块中存7K，那么1000条占142个块，如果每次都全表扫描的话，也就扫描142个块，一次IO通常都是16个块，这个应该还差不多）
- In any large database running OLTP applications in any given unit of time, most rows are accessed either one or zero times. On this basis, there might be little purpose in keeping the block in memory for very long following its use.
在许多大的OLTP应用中，在给定的时间单元里，多数的数据行被访问1次或0次。在这种情况下，没有必要将这个数据块长时间保留在内存中。
- A common mistake is to continue increasing the buffer cache size. Such increases have no effect if you are doing full table scans or operations that do not use the buffer cache.
一个通常的错误是不断的增加buffer cache的大小。这样做对于全表扫描或者不使用buffer cache的操作是没有任何提升的。
7.2.3.1 Increasing Memory Allocated to the Buffer Cache
As a general rule, investigate increasing the size of the cache if the cache hit ratio is low and your application has been tuned to avoid performing full table scans.
To increase cache size, first set the DB_CACHE_ADVICE initialization parameter to ON, and let the cache statistics stabilize. Examine the advisory data in the V$DB_CACHE_ADVICE view to determine the next increment required to significantly decrease the amount of physical I/O performed. If it is possible to allocate the required extra memory to the buffer cache without causing the host operating system to page, then allocate this memory. To increase the amount of memory allocated to the buffer cache, increase the value of the DB_CACHE_SIZE initialization parameter.
If required, resize the buffer pools dynamically, rather than shutting down the instance to perform this change.
通常的做法，假如你的命中率比较低，并且你的应用程序已经优化过不会执行全表扫描时，试着去增加buffer cache的大小。
增加buffer cache大小时，先将db_cache_advice设置成ON，并且让缓存统计平稳下来。查看一下v$db_cache_advice 视图中的统计数据，是否增加一下buffer cache就可以明显的降低IO操作。假如你的操作系统有额外的内存时，你可以把它分配给buffer cache。通过 db_cache_size参数设置这个值的大小。
Note:
When the cache is resized significantly (greater than 20%), the old cache advisory value is discarded and the cache advisory is set to the new size. Otherwise, the old cache advisory value is adjusted to the new size by the interpolation of existing values.
注意： 当缓存已经设置足够大（大于20%），那么旧的顾问值就删除掉了，cache顾问就会设置新的值。另外旧的cache顾问值将被新的值替换掉。
The DB_CACHE_SIZE parameter specifies the size of the default cache for the database's standard block size. To create and use tablespaces with block sizes different than the database's standard block sizes (such as to support transportable tablespaces), you must configure a separate cache for each block size used. You can use the DB_nK_CACHE_SIZE parameter to configure the nonstandard block size needed (where n is 2, 4, 8, 16 or 32 and n is not the standard block size).
db_cache_size参数是设置标准数据块的cache的。假如你的数据库中包含了非标准块，比如你表空间迁移来的表空间，你必须为你的不同大小的数据库块各自分配缓存大小。使用db_nK_cache_size参数来配置非标准数据块。
Note:
The process of choosing a cache size is the same, regardless of whether the cache is the default standard block size cache, the KEEP or RECYCLE cache, or a nonstandard block size cache.
注意选择cache大小的方法是一样，不管你是标准块大小cache，还是keep,recycle cache,或者是非标准块大小cache。
7.2.3.2 Reducing Memory Allocated to the Buffer Cache
If the cache hit ratio is high, then the cache is probably large enough to hold the most frequently accessed data. Check V$DB_CACHE_ADVICE data to see whether decreasing the cache size significantly causes the number of physical I/Os to increase. If not, and if you require memory for another memory structure, then you might be able to reduce the cache size and still maintain good performance. To make the buffer cache smaller, reduce the size of the cache by changing the value for the parameter DB_CACHE_SIZE.
假如命中率非常的高，那么cache可能是足够大的，它保留了多数频繁访问的数据。检查一下 v$db_cache_advice 视图数据去看看如果减少buffer的大小能否达到不增加物理IO读。假如不会增加IO的负担，而同时你其他的内存部件需要内存，你就可以把内存降低，同时维持一个高的性能。通过改变db_cache_size就可以达到减少buffer cache的大小。
7.2.4 Considering Multiple Buffer Pools
A single default buffer pool is generally adequate for most systems. However, users with detailed knowledge of an application's buffer pool might benefit from configuring multiple buffer pools.
With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be atypical if it is constantly accessed (that is, hot) or infrequently accessed (for example, a large segment accessed by a batch job only once a day).
Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is associated with a cache, all blocks from that object are placed in that cache. Oracle Database maintains a DEFAULT buffer pool for objects that have not been assigned to a specific buffer pool. The default buffer pool is of size DB_CACHE_SIZE. Each buffer pool uses the same Least Recently Used (LRU) replacement policy (for example, if the KEEP pool is not large enough to store all of the segments allocated to it, then the oldest blocks age out of the cache).
一个默认的buffer pool对于多数的系统而言已经是足够的了。当然用户如果有足够的知识也可能受益于多个buffer pool的。
有些表是无规则的访问的，将这些表的块存储在这两种不同的buffer pool中： keep pool,recycle pool。一种不规则的访问模式是经常访问（热）或者是不经常访问（例如每天批量时只访问一次）。
多个buffer pool让你可以定位这些差异。你可以将经常要访问的表放到keep池里，将不经常访问的放到recycle池中以防止它消耗不必要的default buffer空间。当一个对象与一个cache关联起来时，所有的块都放在那里。oracle维护着一个default buffer pool，这样所有的对象默认都在那里。default buffer pool的大小是由db_cache_size决定的。每个BUFFER POOL都使用LRU的算法（例如假如KEEP POOL不够时，老的块将也将被交换出去）
By allocating objects to appropriate buffer pools, you can: 通过将对象关联到不同的buffer pool，你可以：
- Reduce or eliminate I/Os 降低或消耗IO问题
- Isolate or limit an object to a separate cache 隔离或者限制一个对象到一个单独的缓存中
7.2.4.1 Random Access to Large Segments
A problem can occur with an LRU aging method when a very large segment is accessed with a large or unbounded index range scan. Here, very large means large compared to the size of the cache. Any single segment that accounts for a substantial portion (more than 10%) of nonsequential physical reads can be considered very large. Random reads to a large segment can cause buffers that contain data for other segments to be aged out of the cache. The large segment ends up consuming a large percentage of the cache, but it does not benefit from the cache.
Very frequently accessed segments are not affected by large segment reads because their buffers are warmed frequently enough that they do not age out of the cache. However, the problem affects warm segments that are not accessed frequently enough to survive the buffer aging caused by the large segment reads. There are three options for solving this problem:
当一个非常大的表使用非常大的索引范围扫描访问时，这种LRU算法是有问题的。这里的大是和buffer的大小相对比的。当一个表以随机方式读时，它占据了10%以上的非大量物理读就是大表。对一个大表进行随机读时就可能造成其他表被交换出缓存。大表消耗了大量的cache，但是却没有从cache中得到任何好处（因为他们通常只访问一次）。
对于非常频繁访问的表不会被这种大表读操作影响，因为它们是那么的温暖，以至于oracle不会把它们交换出去。但是对于那些不是非常频繁访问的温表却会带来麻烦，它们在大表读操作时无法幸存于buffer中。有3种办法解决这个问题：
- If the object accessed is an index, find out whether the index is selective. If not, tune the SQL statement to use a more selective index.
假如被访问的对象是一个索引，那么这个索引是否是selective的。假如不是，那就把它调试成selective的（selective在前面几章介绍过，简单说就是主键就是最selective的，不唯一的selective性一定要主键要差）。这里有一个要说明的：我前面介绍大segment时，我把它翻译成大表，主要是为了方便理解，但是segment是表，索引，分区表，集聚表等等的统称，所以这里说访问一个索引，也就是说访问一个大segment。
- If the SQL statement is tuned, you can move the large segment into a separate RECYCLE cache so that it does not affect the other segments. The RECYCLE cache should be smaller than the DEFAULT buffer pool, and it should reuse buffers more quickly than the DEFAULT buffer pool.
假如SQL语句已经调优过了，你可以将大表移到recycle池中，这样它就不会影响其他的表了。recycle池应该比default小一些，它的交换要比DEFAULT池快，所以重用也要比DEFAUTL池快些。
- Alternatively, you can move the small warm segments into a separate KEEP cache that is not used at all for large segments. The KEEP cache can be sized to minimize misses in the cache. You can make the response times for specific queries more predictable by putting the segments accessed by the queries in the KEEP cache to ensure that they do not age out.
另外，你可以将小的温表移到keep池中，这个池不要放那些大表。这个池的对象应该保证不轻易被转换出去。这样你在访问那些保存在keep池中的对象时响应时间就是可预测的了，因为他们不会被交换出去。
7.2.4.2 Oracle Real Application Clusters Instances
You can create multiple buffer pools for each database instance. The same set of buffer pools need not be defined for each instance of the database. Among instances, the buffer pools can be different sizes or not defined at all. Tune each instance according to the application requirements for that instance.
在RAC中，你可以为每个数据库实例创建多个buffer pool。 相同的buffer pool没必要在每个实例上都去定义。在这个实例中，缓冲池可以是不同大小的，或者不定义也行。调试每个实例以满足应用程序的需要。
7.2.4.3 Using Multiple Buffer Pools
To define a default buffer pool for an object, use the BUFFER_POOL keyword of the STORAGE clause. This clause is valid for CREATE and ALTER TABLE, CLUSTER, and INDEX SQL statements. After a buffer pool has been specified, all subsequent blocks read for the object are placed in that pool.
If a buffer pool is defined for a partitioned table or index, then each partition of the object inherits the buffer pool from the table or index definition, unless you override it with a specific buffer pool.
When the buffer pool of an object is changed using the ALTER statement, all buffers currently containing blocks of the altered segment remain in the buffer pool they were in before the ALTER statement. Newly loaded blocks and any blocks that have aged out and are reloaded go into the new buffer pool.
将一个对象关联到一个default的buffer pool时，使用storage的buffer_pool关键字创建。在create/alter table,cluster,index SQL语句中都可以使用。在一个buffer pool被定义后，所有随后的读取此对象的块都被放入此池中了。
假如是分区表或者索引定义一个buffer pool时每个分区对象都继承着创建表或索引的buffer pool设置，除非你单独为分区定义。（我通常都是单独去定义，这样我永远知道我在干什么，因为这代表了我对数据的掌控能力）。
当一个表的buffer pool被alter语句改变时，那么这个被改变的表的块如果以前存在于某一块中，那么现在仍然在那个buffer pool中。当这个表中新的块从磁盘读取出来时，以及原有池中的块被交换出来时就把它们放到新的buffer pool中了。（这里我仍然是将segment翻译成表，但不代表只是表，看完之后再把其理解成所有的对象就是了）（同样地：从这里我们可以看出，其实oracle本不用设置这个KEEP,RECYCLE池的，因为他们都是按HASH去访问的，在哪里oracle都是一样访问的。区别仅仅是当空间不够时要采取什么机制去交换出去不常访问的块。最好是保证KEEP池中的块不被其他交换出去）
7.2.5 Buffer Pool Data in V$DB_CACHE_ADVICE
You can use V$DB_CACHE_ADVICE to size all pools configured on a database instance. Make the initial cache size estimate, run the representative workload, then simply query the V$DB_CACHE_ADVICE view for the pool you want to use.
你可以使用 v$db_cache_advice 去调整实例中的所有pool。先做一个简单的评估配置，然后做一次有代表性的压力测试，然后再查询这个 v$db_cache_advice 视图去看你想设置的pool大小。
For example, to query data from the KEEP pool:
SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS
FROM V$DB_CACHE_ADVICE
WHERE NAME = 'KEEP'
AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size')
AND ADVICE_STATUS = 'ON';
The data in V$SYSSTAT reflects the logical and physical reads for all buffer pools within one set of statistics. To determine the hit ratio for the buffer pools individually, query the V$BUFFER_POOL_STATISTICS view. This view maintains statistics for each pool on the number of logical reads and writes.
v$sysstat 中反映了所有buffer pool中的逻辑和物理读统计。为了反映buffer pool的命中率，查询 v$buffer_pool_statistics 视图。这个视图维护着每个pool的逻辑读写信息。
The buffer pool hit ratio can be determined using the following formula:
1 - (physical_reads/(db_block_gets + consistent_gets))
The ratio can be calculated with the following query:
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;
7.2.7 Determining Which Segments Have Many Buffers in the Pool
The V$BH view shows the data object ID of all blocks that currently reside in the SGA. To determine which segments have many buffers in the pool, you can use one of the two methods described in this section. You can either look at the buffer cache usage pattern for all segments (Method 1) or examine the usage pattern of a specific segment, (Method 2).
v$bh 视图显示所有目前所有块缓存在SGA中的对象ID。为了判断哪些段有许多缓存，你可以使用下面的两种方法之一去判断。你可以用第一种方法去查看buffer cache中所有段的使用情况，或者使用第二种方法查看某些段的情况。
The following query counts the number of blocks for all segments that reside in the buffer cache at that point in time. Depending on buffer cache size, this might require a lot of sort space.
下面的方法查询出目前在buffer cache中的所有段他们的块数。buffer cache的大小不同，可能也需要一部分的排序空间。
COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999
SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);
OBJECT_NAME NUMBER_OF_BLOCKS
---------------------------------------- ----------------
OA_PREF_UNIQ_KEY 1
SYS_C002651 1
..
DS_PERSON 78
OM_EXT_HEADER 701
OM_SHELL 1,765
OM_HEADER 5,826
OM_INSTANCE 12,644
Use the following steps to determine the percentage of the cache used by an individual object at a given point in time:
使用下面的方法去判断一些对象在目前情况下占用cache的百分比：
SELECT DATA_OBJECT_ID, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_NAME = UPPER('segment_name这里换成要找的对象名');
Because two objects can have the same name (if they are different types of objects), use the OBJECT_TYPE column to identify the object of interest.
因为两个对象可能是相同的名称，所以需要这个object_type去判断一下，其实最好再加一个 owner = ‘xxx’，定位的更加准确些。不过我们一般起的名称都是不相同的，除非是在测试环境中才需要这个owner再区分一下。
- Find the number of buffers in the buffer cache for SEGMENT_NAME:
SELECT COUNT(*) BUFFERS_step2
FROM V$BH
WHERE OBJD = data_object_id_value;
where data_object_id_value is from step 1.
- Find the number of buffers in the instance:
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) buffers_step3
FROM V$BUFFER_POOL
GROUP BY NAME, BLOCK_SIZE
HAVING SUM(BUFFERS) > 0;
- Calculate the ratio of buffers to total buffers to obtain the percentage of the cache currently used by SEGMENT_NAME:
% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]
select buffers_step2 / buffers_step3 from dual; 通过这样就可以计算出一个对象占整个buffer cache的比率了
Note: 注意这种方法仅仅是针对未分区的表。如果是分区表的话就需要计算每一个分区，然后再汇总一下。
This technique works only for a single segment. You must run the query for each partition for a partitioned object.
If there are certain segments in your application that are referenced frequently, then store the blocks from those segments in a separate cache called the KEEP buffer pool. Memory is allocated to the KEEP buffer pool by setting the parameter DB_KEEP_CACHE_SIZE to the required size. The memory for the KEEP pool is not a subset of the default pool. Typical segments that can be kept are small reference tables that are used frequently. Application developers and DBAs can determine which tables are candidates.
You can check the number of blocks from candidate tables by querying V$BH, as described in "Determining Which Segments Have Many Buffers in the Pool".
假如有一个对象是频繁地被访问，那么将这些段的块存储到KEEP池中。通过设置DB_KEEP_CACHE_SIZE来分配KEEP的池大小。这个keep池不是default池的一部分。通常将那些频繁访问的小表放到这里。应用开发人员和DBA去查查哪些表可以放进来。（建议小表全放进来，以现在的内存容量，拿出几十M的空间给他们足够了）
你可以通过查询v$bh 视图去看看哪些表可以放到KEEP池中。
Note: 注意：这个 nocache 的参数对于keep池中的表是无效的
The NOCACHE clause has no effect on a table in the KEEP cache.
The goal of the KEEP buffer pool is to retain objects in memory, thus avoiding I/O operations. The size of the KEEP buffer pool, therefore, depends on the objects to be kept in the buffer cache. You can compute an approximate size for the KEEP buffer pool by adding the blocks used by all objects assigned to this pool. If you gather statistics on the segments, you can query DBA_TABLES.BLOCKS and DBA_TABLES.EMPTY_BLOCKS to determine the number of blocks used.
Calculate the hit ratio by taking two snapshots of system performance at different times, using the previous query. Subtract the more recent values for physical reads, block gets, and consistent gets from the older values, and use the results to compute the hit ratio.
A buffer pool hit ratio of 100% might not be optimal. Often, you can decrease the size of your KEEP buffer pool and still maintain a sufficiently high hit ratio. Allocate blocks removed from the KEEP buffer pool to other buffer pools.
这个KEEP池的目的就是将对象保留在内存中，避免IO的操作。KEEP池的大小因此是由池中的对象大小决定的。你可以通过一个复杂的计算将所有对象的块大小总和作为KEEP池大小。假如你收集了段的统计数据，你可以查询 dba_tables.blocks 和 dba_tables.empty_blocks 去计算块的使用数量。
使用前面的查询语句，在不同时间对两个快照进行分析，计算其命中率。老的值减去最新的值（物理读，block gets, 一致性读）,这样就可以算出命中率来。
一个命中率为100%的buffer pool不一定是最优的。通常你可以降低KEEP池的大小，同时仍然保持足够高的命中率。将一些块从KEEP中移到其他的buffer pool中。
Note: 假如一个对象数据增加了，那它可能不太适合放到KEEP池了。你将开始丢失缓冲池中的数据。
If an object grows in size, then it might no longer fit in the KEEP buffer pool. You will begin to lose blocks out of the cache.
Each object kept in memory results in a trade-off. It is beneficial to keep frequently-accessed blocks in the cache, but retaining infrequently-used blocks results in less space for other, more active blocks.
要不要将对象KEEP到内存是一个折衷的方案。最好是将频繁访问的块放到缓冲池中，如果留下不常访问的对象将导致其他对象没有足够的内存。
It is possible to configure a RECYCLE buffer pool for blocks belonging to those segments that you do not want to remain in memory. The RECYCLE pool is good for segments that are scanned rarely or are not referenced frequently. If an application accesses the blocks of a very large object in a random fashion, then there is little chance of reusing a block stored in the buffer pool before it is aged out. This is true regardless of the size of the buffer pool (given the constraint of the amount of available physical memory). Consequently, the object's blocks need not be cached; those cache buffers can be allocated to other objects.
Memory is allocated to the RECYCLE buffer pool by setting the parameter DB_RECYCLE_CACHE_SIZE to the required size. This memory for the RECYCLE buffer pool is not a subset of the default pool.
Do not discard blocks from memory too quickly. If the buffer pool is too small, then blocks can age out of the cache before the transaction or SQL statement has completed execution. For example, an application might select a value from a table, use the value to process some data, and then update the record. If the block is removed from the cache after the SELECT statement, then it must be read from disk again to perform the update. The block should be retained for the duration of the user transaction.
将一些不想放到内存的对象配置到RECYCLE 缓冲池中是可以的。RECYCLE池中最好放一些那些很少访问或者很少扫描的对象。假如一个应用程序以随机的方式访问一个大对象数据，那么它在被交换出去前只有很小的机率会被再次访问。这个无关缓冲池的大小（根据可用的物理内存）。因此，当对象不需要去缓存时，这些缓冲对象就可以被交换出去，将内存让给其他对象。
通过设置DB_RECYCLE_CACHE_SIZE去配置RECYCLE池的大小。它与KEEP池一样不属于DEFAULT池的一部分。即KEEP,RECYCLE,DEFAULT三个是平级的。
不要将内存中的块过快地清除出去。假如buffer pool太小了，那么在事务或者SQL还没完成执行前就已经被交换出去了。例如一个应用程序从一个表中查询了一个值，然后使用这个值去处理其他一些数据，然后要更新这个记录。假如查询完之后这个记录就被交换出去了，那么它必须从磁盘中再读取一次以便完成更新。因此数据块应该保留一段时间以便用户的事务可以正常完成。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1696240/viewspace-1182690/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/1696240/viewspace-1182690/
2368
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
