11g中的db_block_checking参数

初始化参数DB_BLOCK_CHECKING控制Oracle如何全面检查读写的每个数据块的完整性。启用的检查界别是环境中的故障承受级别(通常很低)与连续检查块所需的开销折中的结果。在11g中db_block_checking参数有了更多的选项，以满足不等的块检验粒度:

SQL> alter system set db_block_checking=AA;
alter system set db_block_checking=AA
*
ERROR at line 1:
ORA-00096: invalid value AA for parameter db_block_checking, must be from among FULL, TRUE, MEDIUM, LOW, OFF, FALSE

/* 可选的有 OFF=FALSE,FULL=TRUE以及MEDIUM和LOW */

不同的DB_BLOCK_CHECKING选项对应不同的检查粒度:

• OFF或FALSE 不执行任何检查块的操作
• LOW 在内存中更改块或从磁盘中读取块后对块进行基本检查，其中包括RAC环境中在实例间传输块的情形
• MEDIUM 包括所有LOW检查，另加检查所有非IOT(索引组织表)块
• FULL或TRUE 包括所有LOW和MEDIUM检查，另加检查索引块

在客户愿意承担性能开销的前提下，Oracle建议使用FULL值。默认值是OFF，但仍始终启用针对SYSTEM表空间的FULL块检查功能(受到隐式参数_db_always_check_system_ts的控制,默认为TRUE)。通常认为块检查开销的范围在1%~10%之间，在OLTP环境中更接近于10%。 以下为Oracle GCS对块检查性能损耗的描述: Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. 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 FULL if the performance overhead is acceptable

• 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

10%？！这是真的吗？我们不妨自己来测试一下！:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

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    /

 truncate table MACLEAN;
 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;

 alter system set db_block_checking=TRUE;

 begin
  /* DO_CHECKING_50000 */
  insert_data(50000);
  end;
  /

 truncate table MACLEAN;
 alter system flush buffer_cache;

 begin
  /* DO_CHECKING_100000 */
  insert_data(100000);
  end;
  /

 truncate table MACLEAN;
 alter system flush buffer_cache;

  begin
  /* DO_CHECKING_150000 */
  insert_data(150000);
  end;
  /

PL/SQL procedure successfully completed.

SQL> SQL> SQL>
Table truncated.

SQL>
System altered.

SQL> SQL>   2    3    4    5

PL/SQL procedure successfully completed.

SQL> SQL>
Table truncated.

SQL>
System altered.

SQL> SQL>   2    3    4    5

PL/SQL procedure successfully completed.

SQL> SQL>
Table truncated.

SQL>
System altered.

SQL> SQL>
System altered.

SQL> SQL>   2    3    4    5

PL/SQL procedure successfully completed.

SQL> SQL> SQL>
Table truncated.

SQL>
System altered.

SQL> SQL>   2    3    4    5

PL/SQL procedure successfully completed.

SQL> SQL>
Table truncated.

SQL>
System altered.

SQL> SQL>   2    3    4    5

PL/SQL procedure successfully completed.

SQL> SQL> SQL> SQL> SQL> SQL>

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.

可以看到在面对频繁的dml操作时(模拟OLTP环境)，DB_BLOCK_CHECKING为TRUE对CPU资源的使用影响可能远大于10%；实际上只有极少数对数据完整性要求异常苛刻的环境中，我们才会使用到它。