CBO CPU耗费模型：索引 vs 全表扫
原文：The CBO CPU Costing Model: Indexes vs. Full Table Scans
作者：Richard Foote
翻译：Johnny Cao
就如之前承诺过，我觉得我有必要去看看CBO是如何通过系统统计信息和CPU耗费模型来处理一个全表扫(FTS)的，这样我们就可以理解为什么CBO做了这个选择，而不是其他选择。
提醒：你可能需要一个计算器来帮助你：）
为了阐述方便，我简单的使用原来的BOWIE_STUFF表跟索引，这些步骤是我之前CBO介绍里面有的。BUT在这儿我会重新创建事例以帮助你唤醒你的记忆。
我首先创建了一个100,000条记录的表，它有一个ID字段，ID有100个唯一值，并且平均分布。对哪些数学挑战来说(讽刺？)，这意味着每个唯一值会返回1000行。
SQL> CREATE TABLE bowie_stuff AS SELECT (mod(rownum,100)+1)*10 id, ‘Ziggy Stardust’ name FROM dual CONNECT BY LEVEL <= 100000; Table created. SQL> CREATE INDEX bowie_stuff_i ON bowie_stuff(id); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> ‘BOWIE_STUFF’, cascade=> true, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′); PL/SQL procedure successfully completed. SQL> select blocks from dba_tables where table_name=’BOWIE_STUFF’; BLOCKS ------ 329
注意这个表有329个块。这个数字在这篇文章中我会提到好几次。
SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = ‘BOWIE_STUFF_I’; INDEX_NAME BLEVEL ------------- ------ BOWIE_STUFF_I 1 LEAF_BLOCKS CLUSTERING_FACTOR ----------- ----------------- 207 32900
同样注意，这个索引的blevel是1，有207个叶子快以及一个相当差的聚簇因子32900(CF)，这个CF跟表的块数相当不接近。我们下面将看到，这个CF是如此差以至于CBO选择了FTS而不是索引。
SQL> show parameter db_file_multi NAME VALUE ----------------------------- ----- db_file_multiblock_read_count 16
注意db_file_multiblock_read_count手工的设成了16。这个值跟用I/O耗费模型计算开销有关，但是我们会发现这个耗费要比CPU模型来的小。
最后，我们看下系统的统计信息：
SQL> select pname, pval1 from sys.aux_stats$ where pname in (‘SREADTIM’, ‘MREADTIM’, ‘MBRC’, ‘CPUSPEED’); PNAME PVAL1 -------- ----- SREADTIM 5 MREADTIM 10 CPUSPEED 1745 MBRC 10
所有这些值都与CPU开销模型计算FTS的开销有关。
好了，我们现在已经有了CBO如何决定这个表的索引跟FTS的信息。
我们首先来看下基于I/O模型的CBO是如何处理这种情况的。
SQL> alter session set “_optimizer_cost_model” = io; Session altered.
好了，我们跑个简单的查询来查找某个ID的信息。记住，这个表有100个平均分布的唯一ID，所以这个查询会返回1%的数据(1000行)。
SQL> set autotrace traceonly SQL> SELECT * FROM bowie_stuff WHERE id = 420; —————————————————————– | Id | Operation | Name | Rows | Bytes | Cost | —————————————————————– | 0 | SELECT STATEMENT | | 1000 | 18000 | 33 | |* 1 | TABLE ACCESS FULL| BOWIE_STUFF | 1000 | 18000 | 33 | —————————————————————–
注意：CBO已经采用了FTS来查找1%的数据，因为它的相对开销更小。
就像之前讨论的，使用索引的开销大约是：
index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)
= 1 + (207 x 0.01) + (32900 x 0.01) = 1 + 3 + 329 = 333
注意：索引的blevel 1在计算开销时可以被CBO去掉的，这个后面有时间再讨论。
之前同样讨论过，FTS大约是：
segment header I/O + ceil(table blocks/fudged mbrc value)
注意：对于db_file_multiblock_read_count是16的情况，CBO调整后的“伪造”值大约是10.4。
因此，上面这个例子，FTS的开销就可以如下计算：
= 1 + ceil(329/10.4) = 1 + 32 = 33
33 是个比 333 小很多的数，所以FTS很轻松的胜出了。
那么当使用系统统计信息跟CPU开销模型的时候是什么情况呢？CBO又是利用以上系统统计信息计算FTS开销的呢？
就像我之前已说的，CPU开销模型的改变对走索引的情况影响不大，但是对FTS的影响就比较大了。
我们跑下同样一个SQL，但是这次试用CPU开销模型：
SQL> alter session set “_optimizer_cost_model” = cpu; Session altered. SQL> SELECT * FROM bowie_stuff WHERE id = 420; 1000 rows selected. ——————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ——————————————————————————– | 0 | SELECT STATEMENT | | 1000 | 18000 | 70 (5)| 00:00:01| |* 1 | TABLE ACCESS FULL| BOWIE_STUFF | 1000 | 18000 | 70 (5)| 00:00:01| ——————————————————————————–
注意：CBO仍然使用了FTS因为CF实在是太差了。但是FTS的开销从33严重增长到70，尽管离索引的333开销还有很远。
那么为什么FTS开销涨了，这个新的开销又是如何计算的呢？
同样，就像之前讨论过的，CPU开销算法基本是：
(sum of all the single block I/Os x average wait time for a single block I/O +
sum of all the multiblock I/Os x average wait time for a multiblock I/O +
sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
如果我们关注算法的单块I/O部分，CBO选择FTS过程中唯一会考虑的单块读就是段头了。注意平均单块读延时是SREADTIM系统统计信息。
如果只有一个单块I/O，那算法的单块读部分跟(1 x sreadtim) / sreadtim就等价了，也就是1.所以只要是使用I/O开销模型，1就都会加到开销中，因为会读段头。
好了，我们再看下算法的多块I/O部分。
多块I/O的总和在I/O开销模型中是近似计算的，它就是简单的表的高水位(HWM)以下的块数(事例中的329)，但需要除以MBRC系统统计信息。注意MBRC不是通过db_file_multiblock_read_count 参数“伪造”的相对武断的一个值，而是真实的数据库环境的平均大小。同样注意，多块读的平均延时是MREADTIM系统统计信息。
所以以上事例的多块读总的延时就是：
sum of all the multiblock I/Os x average wait time for a multiblock I/O =(BLOCKS/MBRC) x MREADTIM = ceil(329/10) x 10 = 330.
然后这个值除以单快读的延时(SREADTIM系统统计信息)来得到总的多块读的开销，只不过是通过以单块I/O为单元表示出来的。
总的多块I/O的开销就是：
((BLOCKS/MBRC) x MREADTIM)/ SREADTIM = 330/5 = 66.
所以所有I/O的开销就是读段头的1加上66的多块读=67.
然而，FTS的开销是70，不是67。多余的3个开销又是哪里冒出来的呢？
是的，被你发现了，这就是算法的CPU部分。CBO发现FTS操作需要’x'次CPU周期，然后这个值除以CPUSPEED得到CPU操作的时间。
然后这个值再除以单块读延时(SREADTIM)得到以单块读为单元的值。在这个例子中，总的CPU相关的开销是3.
CPU部分所有的开销，oracle是通过执行计划中%CPU （例子中是5）的方式展示给我们的。这个(%CPU)的值是CPU相关开销除以总的开销再向上取整得到的：
%CPU = ceil(CPU related costs/overall costs)
所以，例子中，%CPU = ceil(3/70 x 100) = ceil(4.29) = 5% (也就是上面执行计划中显示的那样)
又一次，CPU开销模型中FTS的所有相关开销可以推算出来，并且还说得通。只要所有的需要的信息都是正确有效的，CBO就会选择FTS而不是索引，因为它的开销更小。
后面我会展开这些观点，并且说明为什么这些开销值都搞清楚对我们很有帮助。
现在，你可以放下你的计算器了：）
