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%；实际上只有极少数对数据完整性要求异常苛刻的环境中，我们才会使用到它。

© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. 11g 新特性IGNORE_ROW_ON_DUPKEY_INDEX提示
2. 滚动游标失效(Rolling Cursor Invalidations)
3. New Parameter DB_ULTRA_SAFE introduce In 11g
4. _shared_pool_reserved_pct or shared_pool_reserved_size with ASMM
5. How to Make Use of Oracle's Data Compression with Materialized Views.
6. HOW TO DROP PLANS FROM SPM REPOSITRY
7. undo backup optimization does not work on 11.2.0.1?
8. BMB versus Freelist Segment: DBMS_SPACE.UNUSED_SPACE and DBA_TABLES.EMPTY_BLOCKS
9. SQL PLAN MANAGEMENT
10. DBMS_REPAIR example Internal Only