9i以后可能大家都喜欢通过设置fast_start_mttr_target来控制instance recovery的粒度。但是仍然有两个参数一直影响着我们的checkpoint,就是log_checkpoint_interval和log_checkpoint_timeout
log_checkpoint_interval
Oracle8.1版本后log_checkpoint_interval指的是两次checkpoint之间操作系统数据块的个数。checkpoint时Oracle把内存里修改过的数据块用DBWR写到物理文件,用LGWR写到日志(在8i的时候lgwr进程在兼有ckpt进程的作用,呵呵。为了减轻我们本来就可能在高压情况下疲于奔命的LGWR兄弟的负担,Oracle引入了ckpt来更新我们的控制文件和数据文件头的SCN信息)。
一般UNIX操作系统的数据块为512bytes。
从性能优化的角度来说,建议log_checkpoint_interval=redologfilesizebytes/512bytes,根据我们的online redo file的大小来指定我们数据块的个数.假设LOG_CHECKPOINT_INTERVAL 设置为2000,也就是说,当产生的日志量1m之后,就会触发一个checkpoint。我们可以通过设置log_checkpoints_to_alert =true进行观察测试。
from concept:
LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints(用来指定检查点发生的频率) in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.
Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size, checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure.
log_checkpoint_timeout
Oracle8.1版本后log_checkpoint_timeout指的是两次checkpoint之间时间秒数(单位是秒)。
Oracle建议不用这个参数来控制,因为事务(transaction)大小不是按时间等量分布的(事务的长短并不是最重要的,重要的是我们的业务逻辑和数据的完整性)。那么我们用log_checkpoint_interval参数控制会更好一些。
我们可以通过log_checkpoint_timeout=0来禁用此参数或者按默认的900。
LOG_CHECKPOINT_TIMEOUT specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.
Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.
感谢惜分飞的帮助
参考至:http://space.itpub.net/12361284/viewspace-150449
如有错误,欢迎指正
邮箱:czmcj@163.com
log_checkpoint_interval和log_checkpoint_timeout
最新推荐文章于 2023-06-20 23:01:54 发布