ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MTTR And Self-tuning CheckPoints

MTTR And Self-tuning CheckPoints

原创 Linux操作系统 作者:yyp2009 时间:2012-04-01 17:05:04 0 删除 编辑

      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 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> 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 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>
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 春风沉醉的晚上
请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    330
  • 访问量
    1025894