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
我的测试环境
[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为一个较大的值而导致数据库倾向于全表扫描。
呵呵，这个排版进步许多啊
踩一脚先~
踩2脚先