oracle 如何控制日志切换和检查点频率？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3593749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I control log switches and checkpoint frequencies?
提问by Gangu
What are the differences between LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT? I need a clear picture of volume based intervals and time based interval. What are the relations among LOG_CHECKPOINT_TIMEOUT,LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET?
LOG_CHECKPOINT_INTERVAL 和 LOG_CHECKPOINT_TIMEOUT 之间有什么区别？我需要清楚地了解基于音量的间隔和基于时间的间隔。LOG_CHECKPOINT_TIMEOUT、LOG_CHECKPOINT_INTERVAL和FAST_START_IO_TARGET之间的关系是什么？
回答by APC
A checkpoint is when the database synchronizes the dirty blocks in the buffer cache with the datafiles. That is, it writes changed data to disk. The two LOG_CHECKPOINT parameters you mention govern how often this activity occurs.
检查点是数据库将缓冲区缓存中的脏块与数据文件同步的时间。也就是说，它将更改的数据写入磁盘。您提到的两个 LOG_CHECKPOINT 参数控制此活动发生的频率。
The heart of the matter is: if the checkpoint occurs infrequently it will take longer to recover the database in the event of a crash, because it has to apply lots of data from the redo logs. On the other hand, if the checkpoint occurs too often the database can be tied up as various background processes become a bottleneck.
问题的核心是：如果检查点不经常发生，则在发生崩溃时恢复数据库将需要更长的时间，因为它必须应用重做日志中的大量数据。另一方面，如果检查点发生得太频繁，数据库可能会因为各种后台进程成为瓶颈而被占用。
The difference between the two is that the INTERVAL specifies the maximum amount of redo blockswhich can exist between checkpoints and the TIMEOUT specifies the maximum number of secondsbetween checkpoints. We need to set both parameters to cater for spikes of heavy activity. Note that LOG_CHECKPOINT_INTERVAL is measured in OS blocks not database blocks.
两者之间的区别在于 INTERVAL 指定了检查点之间可以存在的最大重做块数量，而 TIMEOUT 指定了检查点之间的最大秒数。我们需要设置这两个参数以满足大量活动的峰值。请注意，LOG_CHECKPOINT_INTERVAL 是以操作系统块而不是数据库块来衡量的。
FAST_START_IO_TARGET is a different proposition. It specifies a target for the number of I/Os required to recover the database. The database then manages its checkpoints intelligently to achieve this target. Again, this is a trade-off between recovery times and the amount of background activity, although the impact on normal processing should be less than badly set LOG_CHECKPOINT paremeters. This parameter is only available withe the Enterprise Edition. It was deprecated in 9i in favour of FAST_START_MTTR_TARGET, and Oracle removed it in 10g. There is a view V$MTTR_TARGET_ADVICEwhich, er, provides advice on setting the FAST_START_MTTR_TARGET.
FAST_START_IO_TARGET 是一个不同的提议。它指定恢复数据库所需的 I/O 数量的目标。然后，数据库智能地管理其检查点以实现此目标。同样，这是恢复时间和后台活动量之间的权衡，尽管对正常处理的影响应该小于错误设置的 LOG_CHECKPOINT 参数。此参数仅适用于企业版。它在 9i 中被弃用，取而代之的是FAST_START_MTTR_TARGET，Oracle 在 10g 中删除了它。有一个视图V$MTTR_TARGET_ADVICE，它提供设置 FAST_START_MTTR_TARGET 的建议。
We should set either the FAST_START%TARGET or the LOG_CHECKPOINT_% parameters but not both. Setting the LOG_CHECKPOINT_INTERVAL will override the setting of FAST_START_MTTR_TARGET.
我们应该设置 FAST_START%TARGET 或 LOG_CHECKPOINT_% 参数，但不能同时设置两者。设置 LOG_CHECKPOINT_INTERVAL 将覆盖 FAST_START_MTTR_TARGET 的设置。
