/***dbwr checkpoints***即发生检查点事件时，dbwr扫描buffer cache中位于checkpoint queue的buffer cache block或者为了恢复发生的次数***/
/***也就是发生检查点时,dbwr工作的次数********此参数大于background checkpoints completed****/
DBWR checkpoints
 

Number of times the DBWR was asked to scan the cache and write all blocks marked for a checkpoint or the end of recovery.
This statistic is always larger than "background checkpoints completed".

/*******查看上述指标的定义**********/
SQL> select * from v$statname where name like '%checkpoint%';
 
STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
       113 DBWR checkpoints                                                          8 1732023165

SQL> insert into t_checkpoint select level from dual connect by level<=1000000;

1000000 rows created.
/***提交上述参数未变化*********/
SQL> commit;

Commit complete.

SQL> select value from v$sysstat where  STATISTIC#=113;
 
     VALUE
----------
         0
        
/***发生检查点事件****/        
SQL> alter system checkpoint;

System altered.

/****发生检查点事件才会递增此参数的值****/
SQL> /
 
     VALUE
----------
        16
       
/********经测切换日志此参数也会递增*****/       
SQL> /
 
     VALUE
----------
        16
 
SQL>
SQL>
SQL>
SQL> /
 
     VALUE
----------
        17       
       
       
       
/*******dbwr checkpoint buffers written参数即发生检查点事件被写入到数据文件的缓冲个数***********/
DBWR checkpoint buffers written

/*******测试前参数值*********/
SQL> select value from v$sysstat where STATISTIC#=104;
 
     VALUE
----------
     72426
 
SQL> /******插入测试表数据**********/
SQL> insert into t_checkpoint select level from dual connect by level<=1000000;

1000000 rows created.
SQL>

/*********参数增了100多个值，即发生检查点事件写入了100多个buffer到数据文件*****/
SQL> /
 
     VALUE
----------
     72535

/****我间断查询上述参数,此参数值在增加,dbwr进程会根据buffer cache dirty的情况动态调整写入的频率与大小****/
SQL> select value from v$sysstat where STATISTIC#=104;
 
     VALUE
----------
     76219
 
SQL> /
 
     VALUE
----------
     76299
 
SQL> /
 
     VALUE
----------
     76377
 
SQL> /
 
     VALUE
----------
     76377
        
        
/****background checkpoints completed参数：后台进程成功完成检查点事件的次数****/

SQL> select value from v$sysstat where STATISTIC#=219;
 
     VALUE
----------
        17
       
SQL> insert into t_checkpoint select level from dual connect by level<=1000000;

1000000 rows created.

/***此参数未变化****/
SQL> select value from v$sysstat where STATISTIC#=219;
 
     VALUE
----------
        17     
       
/***手工检查点是否变化*****/ 

SQL> alter system checkpoint;

System altered.

/****手工检查点发生了变化****/
SQL> /
 
     VALUE
----------
        18       
       
/***手工切换日志参数未变化******/       
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.       

/****background checkpoints started参数：发生检查点事件的个数***、
不包括：
1,对于每个表空间或数据文件的离线和热备
2,alter system checkpoint

/******测试前参数值************/
SQL> select * from v$statname where name like '%background checkpoints started%';
 
STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
       218 background checkpoints started                                            8 3048007007
 
SQL> select value from v$sysstat where STATISTIC#=218;
 
     VALUE
----------
        20
       
/***手工检查点会递增此参数值*****/       
SQL> alter system checkpoint;

System altered.

SQL> /
 
     VALUE
----------
        21       
       
/***手工切换日志递增此参数值*****/       
SQL> alter system switch logfile;

System altered.

SQL> /
 
     VALUE
----------
        22       
       
/***单独的数据文件离线也不会递增此参数值*****/       
SQL> alter database datafile 21 offline;
 
Database altered
 
SQL> select value from v$sysstat where STATISTIC#=218;
 
     VALUE
----------
        22
 
SQL> alter database datafile 21 online;
 
alter database datafile 21 online
 
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: 'C:\TBS16K_1.DBF'
 
SQL> show user
User is "SYS"
 
SQL> recover datafile 21;
 
recover datafile 21
 
ORA-00900: invalid SQL statement
 
SQL> show user
User is "SYS"
 
SQL> alter database datafile 21 online;
 
Database altered
 
SQL> select value from v$sysstat where STATISTIC#=218;
 
     VALUE
----------
        22