11.17号------7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics的翻译

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 examineV$DB_CACHE_ADVICE data and the buffer cache hit ratio.

在考虑是否增加还是减少高速缓存的大小有许多因素需要检查.比如,你应该检查v$db_cache_advice视图的数据和高速缓存的命中率.

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.

一个很低的命中率增加缓存的的大小并不意味着能提高性能.一个很好的命中率可能会被错误的缓存有足够的工作量.

To interpret the buffer cache hit ratio, you should consider the following:

解释缓存的命中率,你应该解释如下:

• 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.

• 重复的扫描同样大表或索引可以人为的提高缓存的命中率,检查,重复的执行sql语句得到大量的缓存数量.确保执行sql性能是最理想.如果可能 ,避免重复扫描频繁访问的数据进行通过在一个单一的处理或优化SQL语句。

• If possible, avoid requerying the same data, by caching frequently accessed data in the client program or middle tier.

• 如果可能避免查询相同的数据,缓存经常访问的数据在客户端程序或中间层.

• Oracle 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.

• 在一个长的完整表的扫描是成块存取的是lru的最后面的而不是放在列表的头.因此,当索引执行查找或小表扫描,块不会很快的被老化比块读.当对高速缓存中的数据进行解释时,可怜的命中率,有效的对大表进行扫描也应该被考虑.

  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的应用程序的 大型的数据库中在任何时间,绝大部分的行被使用一次或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.

  一个通常 的错误是继续增加高速缓存的大小,如果你在执行一个全表的扫描或操作增加是没有影响的,因为没有使用高速缓存.

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 theDB_CACHE_ADVICEinitialization parameter toON, and let the cache statistics stabilize. Examine the advisory data in theV$DB_CACHE_ADVICEview 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 theDB_CACHE_SIZEinitialization parameter.

增大缓存的大小,首先设置db_cache_advice的初始参数为on ,并让缓存平稳,参考v$db_cache_advice视图中的数据来确认下一个内存增加量可以减少多少的I/O物理读,如果可能的话,而不会造成主机操作系统中的buffer cache所需的额外的内存大小,之后分配内存.想增大分配给buffer cache的内存大小,可以通过增加db_cache_size的初始的大小.

If required, resize the buffer pools dynamically, rather than shutting down the instance to perform this change.

如果需要,动态调整buffer pool的大小,而不是在关闭实例的时候执行该操作.

Note:

When the cache is resized significantly (greater than 20 percent), 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%),旧的缓存报告值将被丢弃,缓存将被设置成新的大小.否则,旧的缓存值被调成新的大小,通过插入现有的值.(不太明白,呵呵)

TheDB_CACHE_SIZEparameter 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. TheDB_nK_CACHE_SIZEparameter can be used to configure the nonstandard block size needed (wherenis 2, 4, 8, 16 or 32 andnis not the standard block size).

db_cache_size参数指定数据库默认标准块的大小.创建和使用表空间块的大小不同于数据库标准块的大小(比如可以传输的表空间),你必须配置一个单独的缓存为每个块大小的使用.db_nk_cache_size参数能够被配置不标准的块需要的大小(其中n 是2,4,8,16,和32,并且n不是一个标准的块大小 )

Note:

The process of choosing a cache size is the same, regardless of whether the cache is the default standard block size cache, theKEEP or RECYCLE cache, or a nonstandard block size cache.

注意:

改变缓存的大小是一样的.不管缓存是默认标准块大小的缓存,keep和recycle缓存,或不规则缓存块大小.

See Also:

Oracle Database Reference andOracle Database Administrator's Guide for more information on using theDB_nK_CACHE_SIZE parameters

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. CheckV$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 parameterDB_CACHE_SIZE.

如果缓存的命中率很高,很有可能缓存保存了最频繁访问的数据.检查V$db_cache_advice数据看否是否增加缓存的大小显著的增大了物理读的数量.如果没有,你可能要求内存为另外一个内存结服务,而仍然维持着很好的性能.改变高速缓存的大小,你可以通过改变db_cache_size的大小到减少缓存的大小.