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