Home > TSOP > CBO CPU耗费模型:索引 vs 全表扫

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而不是索引,因为它的开销更小。

后面我会展开这些观点,并且说明为什么这些开销值都搞清楚对我们很有帮助。

现在,你可以放下你的计算器了:)

Categories: TSOP Tags: ,
  1. No comments yet.
  1. No trackbacks yet.