db_block_checking,db_block_checksum,db_lost_write_protect和db_ultra_safe深入研究(原创)

先看看Oracle文档上对db_block_checking参数的说明：
DB_BLOCK_CHECKING controls whether Oracle performs block checking for data blocks. When this parameter is set to true, Oracle performs block checking for all data blocks. When it is set to false, Oracle does not perform. block checking for blocks in the user tablespaces. However, block checking for theSYSTEM tablespace is always turned on.
Oracle checks a block by going through the data on the block, making sure it is self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to trueif the performance overhead is acceptable
从文档中可以看到，DB_BLOCK_CHECKING参数主要是用于数据块的逻辑(一致)检查(但只是块内，不包括块间的逻辑检查，比如索引项目的 ROWID指向的是不存在的行等)。主要用于防止在内存中损坏或数据损坏。由于是逻辑检查，因此引起的额外负荷比较高，甚至可以达到10%，因此对于一个 繁忙的系统，特别是插入或更新操作很多的系统，性能影响是比较明显的。
该参数对SYSTEM表空间始终是处于“打开”状态，而不管该参数是否设置为FALSE。
下面再看看db_block_checksum参数的说明：
DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum(a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read-only if this parameter is true and the last  write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log.
If this parameter is set to false, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces.
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. Turning on this feature typically causes only an additional 1% to 2% overhead. Therefore, Oracle Corporation recommends that you set DB_BLOCK_CHECKSUM to true
可以看到，DB_BLOCK_CHECKSUM只是在写入(DBWn常规写法或用户进程直接路径写入)，根据一个CHECKSUM算法，计算数据块的校验和。然后写入数据块的一个特定位置(CACHE HEADER，具体是块的16-17字节，以0字节起算)。在读取块时，再进行检验。主要是防止IO硬件和IO子系统的错误。
CHECKSUM的算法只是根据块的字节值计算一个效验和，因此算法比较简单，引起的系统额外负荷通常在1%-2%
实际上，即使将该参数设为TRUE，将数据块(包括SYSTEM表空间)的16-17字节清0，同时将15字节(flag)，第3位(即值为16进制 0x04)清为0，则在块读取时也不会做CHECKSUM检查。如果该参数为FALSE，对于除SYSTEM的其他表空间，如果原来有CHECKSUM 值，将15-16字节清0也不会做CHECKSUM检查。

设置 db_block_checking和db_block_checksum

在Oracle 10g R2版本之后db_block_checking和db_block_checksum这两个参数有了新的变化

先来看看db_block_checking，这个参数现在有四个可能的设置：
OFF - 与原来的FALSE一样，对非SYSTEM表空间的块关闭检查，这个值在设置时仍然可以用false。
LOW： 只检查块头。这个检查发生在当块的内容在内存中发生改变时，比如UPDATE、INSERT、DELETE等，以及将块从磁盘读入、RAC结点间块的传输。
MEDIUM：比LOW更高一级，还包括了非IOT的表的块内部检查（即不仅仅是块头）。

FULL：与原来的TRUE一样，与MEDIUM相比，还包括了索引块的检查。
再看看db_block_checksum这个参数有什么变化：
OFF：与原来的FALSE一样，只会给SYSTEM表空间的块计算checksum值。
TYPICAL：与原来的TRUE一样，Oracle在向磁盘写入块时计算checksum值，下次读入时进行校验。
FULL：这是新增的值，Oracle不关在写入块时计算checksum值，而且在更改块( 比如执行UPDATE语句等)之前对checksum值进行校验，同时在更改块之后对checksum值进行重新计算。另外Oracle也会在写入日志块时，计算块的checksum。这个设置大大增加了系统负荷，大约带来了4-5%的负荷。而TYPICAL值会带来1-2%的负荷。

db_block_checking的性能花销及测试

Oracle官方文档对db_block_checking各个参数设置的性能花销是有如下数据

full：see above depending on updates and inserts and how well the database is tuned it can be costly 10%+
medium：midrange but can be up to 10%.
low：very low around 1 %
off：no overhead

测试过程

SQL> create table MACLEAN(t1 int,t2 char(20),t3 char(20),t4 char(20), t5 char(20),t6 date) tablespace users;
Table created.
SQL> create or replace  procedure insert_data(s int) as
  2  begin
  3    for i in 1..s  loop
  4      insert into MACLEAN values(i,'A','B','C','D',sysdate);
  5      commit;
  6      end loop;
  7      end;
  8  /
Procedure created.

SQL> show     parameter    db_block_checking
NAME                                                  TYPE         VALUE
-----------------------------------            -----------    ------------------------------
db_block_checking                          string        FALSE
SQL> begin
  2    /* NON_CHECKING_50000 */
  3    insert_data(50000);
  4    end;
  5    /
SQL> truncate table MACLEAN;
SQL>alter system flush buffer_cache;
 begin
  /* NON_CHECKING_100000 */
  insert_data(100000);
  end;
  /
truncate table MACLEAN;
alter system flush buffer_cache;
  begin
  /* NON_CHECKING_150000 */
  insert_data(150000);
  end;
  /
 truncate table MACLEAN;
 alter system flush buffer_cache;

将db_block_checking设置为true
SQL> begin
2    /* NON_CHECKING_50000 */
3    insert_data(50000);
4    end;
5    /
SQL> truncate table MACLEAN;
SQL>alter system flush buffer_cache;
begin
/* NON_CHECKING_100000 */
insert_data(100000);
end;
/
truncate table MACLEAN;
alter system flush buffer_cache;
begin
/* NON_CHECKING_150000 */
insert_data(150000);
end;
/
truncate table MACLEAN;
alter system flush buffer_cache;

查看消耗的cpu时间
SQL> col sql_text for a70;
SQL> select sql_text, cpu_time, elapsed_time
  2  from v$sql
  3  where sql_text like '%CHECKING%'
  4  and sql_text not like '%v$sql%'
  5  order by CPU_TIME;
SQL_TEXT                                                                                           CPU_TIME ELAPSED_TIME
----------------------------------------------------------------------                          ---------------------- ------------
begin   /* NON_CHECKING_50000 */   insert_data(50000);   end;             7222902      7675162
 begin   /* DO_CHECKING_50000 */   insert_data(50000);   end;             8285740      8522438
 begin   /* NON_CHECKING_100000 */   insert_data(100000);   end;         13142002     13327092
 begin   /* DO_CHECKING_100000 */   insert_data(100000);   end;          15353665     15686535
  begin   /* NON_CHECKING_150000 */   insert_data(150000);   end;        19346058     19502160
  begin   /* DO_CHECKING_150000 */   insert_data(150000);   end;         25374143     26539033
6 rows selected.

可以看出，这两者的差别是相当显著的，这两者参数设置为false时比设置为true竟然快了40%以上。不过这只是个简单的测试，实际情况可能没那么突出，但差异在10%以上是有可能的。
值得注意的是，性能上的差异，主要是由于CPU的消耗造成的，对于CPU资源不足的系统，将这两个参数设置为TRUE无疑会增大CPU的负担，引起性能问题。同时还会引起redo copy latch的持有时间增加和引起这个latch的竞争。
另外，由于不管db_block_checking和db_block_checksum这两个参数的值为何值，SYSTEM表空间都会进行做checking和checksum，除非把隐含参数_db_always_check_system_ts设置为FALSE，当然为了SYSTEM表空间数据安全，不建议将这个隐含参数值设置为FALSE。 因此，不要将用户表和索引放到SYSTEM空间中。
在启用一起特定的功能后，SYSTEM表空间中一些表和索引会增长很快。比如启用了审计并且将审计日志存储到数据库中，则AUD$和FGA_LOG$会迅速增长；如果使用高级复制，DEF$_AQCALL表会增加很快，并且如果要复制的数据量比较大，则这个表上的DML是非常多的，在这样的情况会下，会消耗更多的CPU和引起性能降低。如果使用了审计和高级复制，建议将AUD$、FGA_LOG$、DEF$_AQCALL迁移到其他表空间，一方面避免产生大量数据使得SYSTEM表空间过大，另一方面则是避免出现本文提到的性能问题。不过这些表都是特殊的对象，最好在Oracle技术支持指导下进行。

db_block_checsum的性能花销及测试

先来看下ixora的一篇文章

The checksum is a simple XOR of all the data in the block, and as such takes a small but noticeable amount of CPU time to compute. This has to be done for every I/O operation. The extra CPU time required for writes is of less concern than the extra CPU time required for reads, because writes are typically performed in the background by DBWn and LGWR and foreground processes should not normally wait for the services of these processes. However, the extra CPU time required for reads is sustained by foreground processes and thus impacts end user response times directly.

The Oracle documentation acknowledges this impact and indicates that the overhead is typically in the order of 1% to 2%. That is true on average ! However, the impact on I/O intensive queries on a system with moderate to high CPU usage can be much worse than that. The following comparison on an otherwise idle system shows a 4% difference in elapsed time and an 8% difference in CPU usage. On a busy system with not much spare CPU capacity, the impact on the response time of such queries rises to about 10%, and it increases further if the application uses mostly index-based access paths rather than full table scans to fetch its data.

从上面的叙述来看,db_block_checksum对读的映像比对写的影响要大。这是由于在负责将数据写入磁盘的后台进程DBWn和LGWR会计算除sum值，而前台进程正常情况下不会等待DBWn和LGWR。而当进行读取的时候，前台进程需奥消耗额外的CPU时间，故影响了用户的相应速度。

在Oracle的联机文档中写道，checksum只需要消耗额外1%～2%的系统负荷。然而在I/O频繁而且CPU利用率中等偏上的系统中，可能会增加10%左右的系统负载。而且，如果大部分使用索引路径访问而不是全表扫描的话，情况会更糟！因此，当系统I/O频繁或者CPU使用率偏高时，最好禁用该参数

SQL> show parameters db_block_checksum
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    boolean     FALSE
SQL> set timing on
SQL> select count(*) from checksum_off;
COUNT(*)
----------
10000
Elapsed: 00:00:49.02
SQL> set timing off
SQL> select
2    n.name,
3    m.value
4  from
5    sys.v_$mystat  m,
6    sys.v_$statname    n
7  where
8    m.statistic# in (12, 42, 164) and
9    n.statistic# = m.statistic#
10  /
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                183
physical reads                                                        10182
no work - consistent read gets                                        10360
SQL> show parameters db_block_checksum
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    boolean     TRUE
SQL> set timing on
SQL> select count(*) from checksum_on;
COUNT(*)
----------
10000
Elapsed: 00:00:51.02
SQL> set timing off
SQL> select
2    n.name,
3    m.value
4  from
5    sys.v_$mystat  m,
6    sys.v_$statname    n
7  where
8    m.statistic# in (12, 42, 164) and
9    n.statistic# = m.statistic#
10  /
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                198
physical reads                                                        10182
no work - consistent read gets                                        1036

db_lost_write_protect 

This is a new initialization parameter  in 11G R1 , which initiates checking for any data block lost writes that may occur on a physical standby database when the I/O subsystem signals block write completion before it’s completely written to disk. The default value for this parameter is typical, same as the value Oracle recommends that you use. In RAC instances, the parameter value is system-wide.

When the parameter is set to TYPICAL on the primary database, the instance logs buffer cache reads for read-write tablespaces in the redo log, which is necessary for detection of lost writes.
When the parameter is set to FULL on the primary database, the instance logs reads for read-only tablespaces as well as read-write tablespaces.
When the parameter is set to TYPICAL or FULL on the standby database or on the primary database during media recovery, the instance performs lost write detection.
When the parameter is set to NONE on either the primary database or the standby database, no lost write detection functionality is enabled.

db_ultra_safe

db_ultra_safe is introduced in 11G R1,it's used for controling the values of the above three parameter simply.

DB_ULTRA_SAFE sets the default values for other parameters that control protection levels.
Values:
OFF
the value of the db_block_checking parameter is off (or false), the value of the db_block_checksum parameter is typical, and the value of the db_lost_write_protect parameter is typical. In brief, if you set any of the three parameters explicitly, no changes are made to those values.
DATA_ONLY
DB_BLOCK_CHECKING will be set to MEDIUM.
DB_LOST_WRITE_PROTECT will be set to TYPICAL.
DB_BLOCK_CHECKSUM will be set to FULL.

When the value of the db_block_checking parameter is set to medium, the database will check for logical corruption in the data blocks. Any time a block’s contents change, say because of an update or insert operation, the database performs block header checks, including semantic block checking, for all data blocks.
DATA_AND_INDEX
DB_BLOCK_CHECKING will be set to FULL.
DB_LOST_WRITE_PROTECT will be set to TYPICAL.
DB_BLOCK_CHECKSUM will be set to FULL.

When the value of the db_block_checking parameter is set to full, the database will check for logical corruption in the data blocks as well as the index blocks. Any time a block’s contents change, say because of an update or insert operation, the database performs block header checks, including semantic block checking, for both data and index blocks.

 

本文原创，转载请注明出处、作者

参考至:
《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》             http://space.itpub.net/69924/viewspace-329637

             http://www.oracledatabase12g.com/archives/11g%E4%B8%AD%E7%9A%84db_block_checking%E5%8F%82%E6%95%B0.html

             http://www.ixora.com.au/tips/db_block_checksum.htm
             http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams058.htm

             http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams064.htm#REFRN10295

               http://www.laoxiong.net/httpwwwlaoxiongnetdb_block_checking_and_db_block_checksum_part3.html

如有错误，欢迎指正

邮箱:[e-mail]