2.3.9 Oracle 10g 自動檢查點調整（2.3.7 FAST_START_MTTR_TARGET 續集）

 

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
flash cache insert skip: DBWR overloaded                                  0
DBWR checkpoint buffers written                                    61031854
DBWR thread checkpoint buffers written                                   53
DBWR tablespace checkpoint buffers written                            34559
DBWR parallel query checkpoint buffers written                            0
DBWR object drop buffers written                                         10
DBWR transaction table writes                                        302348
DBWR undo block writes                                             11185035
DBWR revisited being-written buffer                                       0
DBWR lru scans                                                            0
DBWR checkpoints                                                       2432

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
DBWR fusion writes                                                 29391272

12 rows selected.

SQL> select RECOVERY_ESTIMATED_IOS REIO,
  2  TARGET_MTTR TMTTR,
  3  ESTIMATED_MTTR EMTTR,
  4  WRITES_MTTR WMTTR,
  5  WRITES_OTHER_SETTINGS WOSET,
  6  CKPT_BLOCK_WRITES CKPTBW,
  7  WRITES_AUTOTUNE WAUTO,
  8  WRITES_FULL_THREAD_CKPT WFTCKPT
  9  from v$instance_recovery;

      REIO      TMTTR      EMTTR      WMTTR      WOSET     CKPTBW      WAUTO
---------- ---------- ---------- ---------- ---------- ---------- ----------
   WFTCKPT
----------
    118839          0         86          0          0   21042726   60997480
         0

SQL> select 61031854 - 34559 - 60997480 from dual;

61031854-34559-60997480
-----------------------
                   -185

資料表空間檢查點的寫出不是通過自動調整檢查點完成的。Oracle可以通過FILEQ而不是CKPTQ來針對錶空間檢查點事件進行寫出。

DBWR checkpoint buffers written = DBWR tablespace checkpoint buffers written + WRITES_AUTOTUNE