初始化参数DB_BLOCK_CHECKING控制Oracle如何全面检查读写的每个数据块的完整性。启用的检查界别是环境中的故障承受级别(通常很低)与连续检查块所需的开销折中的结果。在11g中db_block_checking参数有了更多的选项，以满足不等的块检验粒度:
不同的DB_BLOCK_CHECKING选项对应不同的检查粒度:
|
1
2
3
4
5
6
7
|
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 */
|
- OFF或FALSE 不执行任何检查块的操作
- LOW 在内存中更改块或从磁盘中读取块后对块进行基本检查，其中包括RAC环境中在实例间传输块的情形
- MEDIUM 包括所有LOW检查，另加检查所有非IOT(索引组织表)块
- FULL或TRUE 包括所有LOW和MEDIUM检查，另加检查索引块
- 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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
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%；实际上只有极少数对数据完整性要求异常苛刻的环境中，我们才会使用到它。
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277566
