当db_cache_advice初始化参数设为on, 大大降低了cpu的使用

7.2.2.1 Buffer Cache Advisory Statistics

A number of statistics can be used to examine buffer cache activity. These include the following:


V$DB_CACHE_ADVICE


Buffer cache hit ratio

buffer cache 报告的统计

统计的数据能被用于检查buffer cache的活动。
7.2.2.2 Using V$DB_CACHE_ADVICE

This view is populated when the DB_CACHE_ADVICE initialization parameter is set toON. This view shows the simulated miss rates for a range of potential buffer cache sizes.

当db_cache_advice初始化参数设为on，这个视图会一直增加。这个视图显示了一整套的关于buffer cache大小的模拟的缺失率。

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

在视图中每个缓存的模拟大小有他自己的一行数据。设置不同的大小，预测物理i/o的活动。db_cache_advice参数是动态的。所以报告能被开启和关闭允许你收集一个具体的工作量的报告数据。

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.

有一些与这个报告相关联的开销,当这个报告被开启,将有少量增加cpu的使用.因为增加额外的记录是必须的.

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

oracle使用dba为基础取样收集缓存的统计报告,取样大大降低了cpu的使用和内存开销和额外的记录.

To use V$DB_CACHE_ADVICE, the parameter DB_CACHE_ADVICE should be set toON, and a representative workload should be running on the instance. Allow the workload to stabilize before querying theV$DB_CACHE_ADVICE view.

想要使用v$db_cache_advice视图.db_cache_advice参数一定要设置成on状态.和一个典型的工作量应该被运行在实例上.在查询v$db_cache_advice视图之前使工作量稳定.

The following SQL statement returns the predicted I/O requirement for the default buffer pool for various cache sizes:

如下的sql语句各种不同的缓存大小跟不同的I/o关连.

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.

下面输出显示如果缓存是212M,而不是当前的304M,估计物理读的数量将增加1.74,这就意味着降低到212M是不可取的,oracle视频教程下载.