Oracle CBO中怎么计算调整MBRC

网络整理 - 07-27

　　在CBO的IO模式中，全表扫描的IO代价不是直接由MBRC(db_file_multiblock_read_count)计算来的，而是由一个相应的调整的值(ADJMBRC)计算的：

　　IO = 1+CEIL(TABBLKS/ADJMBRC)

　　Jonathan Lewis曾经讨论过ADJMBRC是由MBRC和数据块大小决定的，以8k数据块大小为例，相应的ADJMBRC是

　　SQL代码

　　Code highlighting produced by Actipro CodeHighlighter (freeware)

　　-->MBRC ADJMBRC

　　------------ --------------

　　8 6.59

　　16 10.40

　　32 16.41

　　64 25.90

　　可以看到，ADJMBRC是一些ROUND过的值，且看起来似乎无规律。我们这里做个数字游戏，看看这些数字直接是否有没有规律。

　　由之前的公式，可知ADJMBRC计算如下：

　　ADJMBRC ≈ TABBLKS/(IO - 1)

　　我们通过测试，调整TABBLKS，可以发现ADJMBRC会逐渐接近某个数字。以MBRC=16为例，其ADJMBRC接近为6.588755338...，同样可得到其他MBRC的ADJMBRC为

　　Code highlighting produced by Actipro CodeHighlighter (freeware)

　　-->SQL代码

　　MBRC ADJMBRC

　　------------ --------------

　　8 6.588755338

　　16 10.39779676

　　32 16.4088924

　　64 25.89507721

　　虽然精度提高了，但是规律还是不可寻。我们尝试计算ADJMBRC/MBRC看看会有什么结果：

　　Code highlighting produced by Actipro CodeHighlighter (freeware)

　　-->SQL代码

　　MBRC ADJMBRC ADJMBRC/MBRC

　　------------ -------------- -------------

　　8 6.588755338 0.823594417

　　16 10.39779676 0.649862298

　　32 16.4088924 0.512777888

　　64 25.89507721 0.404610581

　　嗯，还是看不到规律。不过注意到，我们这的MBRC直接是倍数关系，那么再试试将ADJMBRC/MBRC的结果前后相除会有什么结果：

　　Code highlighting produced by Actipro CodeHighlighter (freeware)

　　-->SQL代码

　　MBRC ADJMBRC ADJMBRC/MBRC (ADJMBRC/MBRC[n*2])/(ADJMBRC/MBRC[n])

　　------------ -------------- - -------------------------------------

　　8 6.588755338 0.823594417 1.26733682

　　16 10.39779676 0.649862298 1.26733682

　　32 16.4088924 0.512777888 1.26733682

　　64 25.89507721 0.404610581

　　哈，这下找到了。由上面的推导过程，也就不难得出ADJMBRC的公式了：

　　ADJMBRC = 1.6764459346/POWER(1.26733682,LOG(MBRC,2))*MBRC

　　再考虑BLKSIZ因素，上述公式就可以调整为：

　　ADJMBRC = 1.6764459346/POWER(1.26733682,LOG(8192/BLKSIZ*MBRC,2))*8192/BLKSIZ*MBRC

　　对1.6764459346取反，可知其为(1/0.5965)。但是我仍然没有看出1.26733682是如何来的。上述公式调整为：

　　ADJMBRC = 1/0.5965/POWER(1.26733682,LOG(8192/BLKSIZ*MBRC,2))*8192/BLKSIZ*MBRC

　　有了上述公式，我们就不难通过SQL来估算表(或分区)的Full Table Scan的IO代价了：

　　Code highlighting produced by Actipro CodeHighlighter (freeware)

　　-->SQL代码

　　HELLODBA.COM>select p.value mbrc,

　　2 1/0.5965/POWER(1.26733682,LOG(2,8192/sp.block_size*p.value))*8192/sp.block_size*p.value adjmbrc,

　　3 s.blocks,

　　4 1+ceil(s.blocks/(1/0.5965/POWER(1.26733682,LOG(2,8192/sp.block_size*p.value))*8192/sp.block_size*p.value)) IOCOST

　　5 from dba_segments s, dba_tablespaces sp, all_parameters p

　　6 where s.owner='DEMO' and s.segment_name = 'T_TEST1'

　　7 and s.tablespace_name = sp.tablespace_name

　　8 and p.name = 'db_file_multiblock_read_count';

　　MBRC ADJMBRC BLOCKS IOCOST

　　------ ----------- ---------- ----------

　　16 10.3977968 680 67

　　HELLODBA.COM>set autot trace exp

　　HELLODBA.COM>select /*+no_cpu_costing*/* from demo.t_test1;

　　Execution Plan

　　----------------------------------------------------------

　　Plan hash value: 1883417357

　　-------------------------------------------------------------

　　-------------------------------------------------------------

　　| 0 | SELECT STATEMENT | | 47582 | 4600K| 67 |

　　| 1 | TABLE ACCESS FULL| T_TEST1 | 47582 | 4600K| 67 |