MTTR And Self-tuning CheckPoints

Oracle数据库10g中检查点自调优与手动设置对比

最新推荐文章于 2025-11-26 14:12:29 发布

转载最新推荐文章于 2025-11-26 14:12:29 发布 · 88 阅读

文章标签：

#数据库

本文详细介绍了在Oracle数据库10g中使用自调优检查点活动来实现良好的恢复时间和低的影响度，以及如何通过设置FAST_START_MTTR_TARGET参数手动调整检查点行为。通过实验展示了不同情况下检查点行为对实例恢复时间的影响，并提供了实测数据作为支撑。

With Oracle Database 10g, the database can self-tune checkpoints activity to achieve good recovery times with low impact on normal throughput. With automatic checkpoint tuning, Oracle Database takes advantage of periods of low I/O usage to write out data modified in memory to the data files without adverse impact on the throughput. Consequently, a reasonable crash recovery time can be achieved even if the administrator does not set any checkpoint-related parameter or if this parameter is set to a very large value.
Another enhancement done in the second release of Oracle Database 10g dramatically improves the performance6 of object-checkpoint requests issued for objects accessed through direct path reads, a situation that can occur with parallel query. Before an object can be accessed through direct path reads, dirty buffers of the object must be written to data files on disk via an object-checkpoint request.(Hervé Lejeune)

1 No Self-tuning checkpoints：

alert：

……

Sun Apr 01 15:20:30 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Apr 01 15:20:30 2012

……

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter fast_start_mttr_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0

SQL> create table t1 as select * from all_objects where 1=2;

表已创建。

SQL> insert into t1 select * from all_objects;

已创建49876行。

SQL> desc V$INSTANCE_RECOVERY;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
RECOVERY_ESTIMATED_IOS                             NUMBER
ACTUAL_REDO_BLKS                                   NUMBER
TARGET_REDO_BLKS                                   NUMBER

……

SQL> select TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES from V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
0 17 465

SQL> select RECOVERY_ESTIMATED_IOS,ACTUAL_REDO_BLKS,ESTIMATED_MTTR from V$INSTANCE_RECOVERY;

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS ESTIMATED_MTTR
---------------------- ---------------- --------------
813 11692 17

SQL> commit;

提交完成。

SQL> select RECOVERY_ESTIMATED_IOS,ACTUAL_REDO_BLKS,ESTIMATED_MTTR from V$INSTANCE_RECOVERY;

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS ESTIMATED_MTTR
---------------------- ---------------- --------------
815 11700 17
SQL> alter system checkpoint;

系统已更改。

SQL> select RECOVERY_ESTIMATED_IOS,ACTUAL_REDO_BLKS,ESTIMATED_MTTR from V$INSTANCE_RECOVERY;

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS ESTIMATED_MTTR
---------------------- ---------------- --------------
0 0 17

SQL> select TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES from V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
0 17 561

SQL> select name,value from v$sysstat where upper(name) like '%DBWR%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
DBWR checkpoint buffers written                                        2884
DBWR thread checkpoint buffers written                                  815
DBWR tablespace checkpoint buffers written                                0
DBWR parallel query checkpoint buffers written                            0
DBWR object drop buffers written                                          0
DBWR transaction table writes                                           138
DBWR undo block writes                                                  759
DBWR revisited being-written buffer                                       0
DBWR make free requests                                                   0
DBWR lru scans                                                            0
DBWR checkpoints                                                          2

NAME VALUE
---------------------------------------------------------------- ----------
DBWR fusion writes 0

已选择12行。

SQL> select writes_autotune from V$INSTANCE_RECOVERY;

WRITES_AUTOTUNE
---------------
2069

2 Self-tuning checkpoints：

SQL> alter system set FAST_START_MTTR_TARGET=10;

系统已更改。

SQL> show parameter FAST_START_MTTR_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 10

SQL> create table t1 as select * from all_objects where 1=2;

表已创建。

SQL>
SQL>
SQL>
SQL> insert into t1 select * from all_objects;

已创建49876行。

SQL> select TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES from V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
16 14 0

SQL> select RECOVERY_ESTIMATED_IOS,ACTUAL_REDO_BLKS,ESTIMATED_MTTR from V$INSTANCE_RECOVERY;

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS ESTIMATED_MTTR
---------------------- ---------------- --------------
609 11518 14

SQL> select name,value from v$sysstat where upper(name) like '%DBWR%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
DBWR checkpoint buffers written                                           0
DBWR thread checkpoint buffers written                                    0
DBWR tablespace checkpoint buffers written                                0
DBWR parallel query checkpoint buffers written                            0
DBWR object drop buffers written                                          0
DBWR transaction table writes                                             0
DBWR undo block writes                                                   18
DBWR revisited being-written buffer                                       0
DBWR make free requests                                                   0
DBWR lru scans                                                            0
DBWR checkpoints                                                          0

NAME VALUE
---------------------------------------------------------------- ----------
DBWR fusion writes 0

已选择12行。

SQL>
SQL> select writes_autotune from V$INSTANCE_RECOVERY;

WRITES_AUTOTUNE
---------------
0

SQL> commit;

提交完成。

SQL> select TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES from V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
16 14 0

SQL>
SQL> select RECOVERY_ESTIMATED_IOS,ACTUAL_REDO_BLKS,ESTIMATED_MTTR from V$INSTANCE_RECOVERY;

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS ESTIMATED_MTTR
---------------------- ---------------- --------------
614 11526 14

SQL> select name,value from v$sysstat where upper(name) like '%DBWR%';

NAME VALUE
---------------------------------------------------------------- ----------
DBWR fusion writes 0

已选择12行。

SQL>
SQL> select writes_autotune from V$INSTANCE_RECOVERY;

WRITES_AUTOTUNE
---------------
0

SQL> select writes_autotune from V$INSTANCE_RECOVERY;

WRITES_AUTOTUNE
---------------
0

SQL> select name,value from v$sysstat where upper(name) like '%DBWR%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
DBWR checkpoint buffers written                                         621
DBWR thread checkpoint buffers written                                  621
DBWR tablespace checkpoint buffers written                                0
DBWR parallel query checkpoint buffers written                            0
DBWR object drop buffers written                                          0
DBWR transaction table writes                                            11
DBWR undo block writes                                                   38
DBWR revisited being-written buffer                                       0
DBWR make free requests                                                   0
DBWR lru scans                                                            0
DBWR checkpoints                                                          1

NAME VALUE
---------------------------------------------------------------- ----------
DBWR fusion writes 0

已选择12行。

SQL> select name,value from v$sysstat where upper(name) like '%DBWR%';

NAME VALUE
---------------------------------------------------------------- ----------
DBWR fusion writes 0

已选择12行。

SQL>
SQL> select writes_autotune from V$INSTANCE_RECOVERY;

WRITES_AUTOTUNE
---------------
0

Conclusions：

1 commit has no effect on the DBWR；

2 No Self-tuning checkpoints：DBWR checkpoint buffers written=DBWR thread checkpoint buffers written+writes_autotune,and this test case to reflect the checkpoint influences MTTR on the instance recovery；

3 Self-tuning checkpoints：DBWR checkpoint buffers written=writes_autotune，after alter checkpoint will not Self-tuning checkpoints，writes_autotune will be zero, DBWR checkpoint buffers written=DBWR thread checkpoint buffers written+writes_autotune，so DBWR checkpoint buffers written=DBWR thread checkpoint buffers written。

--------end-------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-720197/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-720197/