动态调整buffer pool的大小与如何解决缓存的命中率太低

通常,如果缓存的命中率太低可以增大缓存的大小和避免你的应用程序执行全表扫描.

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.

增大缓存的大小,首先设置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%),旧的缓存报告值将被丢弃,缓存将被设置成新的大小.否则,旧的缓存值被调成新的大小,通过插入现有的值.(不太明白,呵呵)

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. The DB_nK_CACHE_SIZE parameter can be used to configure the nonstandard block size needed (where n is 2, 4, 8, 16 or 32 and n is not the standard block size).本文转自cuug网站。