24 luglio
10g r2 db_file_multiblock_read_count在cbo中的变化
10g r2和前几个版本比起来对db_file_multiblock_read_count在cbo成本计算中的公式做了调整,看一下下面我实验后的对照表。
我的测试环境
[oracle@csdba ~]$ uname -a
Linux csdba 2.6.9-11.ELsmp #1 SMP Fri May 20 18:26:27 EDT 2005 i686 i686 i386 GNU/Linux
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 24 17:07:42 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining Scoring Engine options
分别测试了两个版本下1000行,10000行,100000行记录的6个对比
exec dbms_stats.SET_TABLE_STATS(OWNNAME=>'TEST',TABNAME=>'T1',NUMBLKS=>1000);
exec dbms_stats.SET_TABLE_STATS(OWNNAME=>'TEST',TABNAME=>'T1',NUMBLKS=>10000);
exec dbms_stats.SET_TABLE_STATS(OWNNAME=>'TEST',TABNAME=>'T1',NUMBLKS=>100000);
10g r2版本
| MBRC |
BLOCKS |
COST |
ADJUSTED_MBRC |
| 4 |
1000 |
377 |
2.652519894 |
| 8 |
1000 |
273 |
3.663003663 |
| 16 |
1000 |
221 |
4.524886878 |
| 32 |
1000 |
195 |
5.128205128 |
| 64 |
1000 |
182 |
5.494505495 |
| 128 |
1000 |
176 |
5.681818182 |
| MBRC |
BLOCKS |
COST |
ADJUSTED_MBRC |
| 4 |
10000 |
3763 |
2.657454159 |
| 8 |
10000 |
2722 |
3.673769287 |
| 16 |
10000 |
2201 |
4.543389368 |
| 32 |
10000 |
1941 |
5.151983514 |
| 64 |
10000 |
1811 |
5.521811154 |
| 128 |
10000 |
1745 |
5.730659026 |
| MBRC |
BLOCKS |
COST |
ADJUSTED_MBRC |
| 4 |
100000 |
37615 |
2.658513891 |
| 8 |
100000 |
27199 |
3.676605758 |
| 16 |
100000 |
21990 |
4.547521601 |
| 32 |
100000 |
19386 |
5.158361704 |
| 64 |
100000 |
18084 |
5.529750055 |
| 128 |
100000 |
17433 |
5.736247347 |
9i r2
| MBRC |
BLOCKS |
COST |
ADJUSTED_MBRC |
| 4 |
1000 |
241 |
4.149377593 |
| 8 |
1000 |
153 |
6.535947712 |
| 16 |
1000 |
98 |
10.20408163 |
| 32 |
1000 |
63 |
15.87301587 |
| 64 |
1000 |
40 |
25 |
| 128 |
1000 |
26 |
38.46153846 |
| MBRC |
BLOCKS |
COST |
ADJUSTED_MBRC |
| 4 |
10000 |
2397 |
4.171881519 |
| 8 |
10000 |
1519 |
6.583278473 |
| 16 |
10000 |
963 |
10.38421599 |
| 32 |
10000 |
611 |
16.36661211 |
| 64 |
10000 |
388 |
25.77319588 |
| 128 |
10000 |
246 |
40.6504065 |
| MBRC |
BLOCKS |
COST |
ADJUSTED_MBRC |
| 4 |
100000 |
23953 |
4.1748424 |
| 8 |
100000 |
15179 |
6.588049279 |
| 16 |
100000 |
9619 |
10.39609107 |
| 32 |
100000 |
6096 |
16.40419948 |
| 64 |
100000 |
3863 |
25.88661662 |
| 128 |
100000 |
2449 |
40.83299306 |
最后得出的测试结果是在10g r2里面,db_file_multiblock_read_count
对cost计算的影响明显变小,看起来oracle对db_file_multiblock_read_count
采取了更谨慎的态度,这样一来不会因为设置了
db_file_multiblock_read_count为一个较大的值而导致数据库倾向于全表扫描。