公告

CBO学习笔记4

http://hi.baidu.com/richard%5Fcxs/blog/item/04c75e0923348cad2eddd449.html

full tablescan and CPU Cost

这里我们看一下Oracle9i里面的CBO是如何使用system statistics的，重点看一下CBO如何计算CPU cost。

我们前面介绍过oracle8i的CBO的一些缺点（参考：http://fusnow.itpub.net/post/681/211814），其中有两点是：

1，当我们执行一个IO的时候，在这次IO里，我们可能读1个Block，也有可能是读多个block，很明显读1个Block的cost和读多个block的cost是不可能一样的，但8i的CBO里面并没有对这两种IO进行区别。所有的IO都被当作是单block读取来计算的。

2，IO并不是整个SQL执行过程中耗费的资源的全部，还应该考虑CPU的使用，这一点8i的CBO也是不考虑的。

为了解决上面的两个问题，Oracle9i引入了system statistics机制，关于system statistics的使用方法我们前面已经介绍过了（参考：http://fusnow.itpub.net/post/681/211814），和schema statistics一样，可以收集系统的真实情况，也可以手工指定。

为了实验方便，我们采用手工指定的方式导入system statistics。

实验1：

1）手工给系统指定一系列system statistics的值：
begin
begin execute immediate 'purge recyclebin';
exception when others then null;
end;

dbms_stats.set_system_stats('CPUSPEED',500);
dbms_stats.set_system_stats('SREADTIM',5.0);
dbms_stats.set_system_stats('MREADTIM',30.0);
dbms_stats.set_system_stats('MBRC',12);

end;
/

这里指定的值的含义分别是：
CPUSPEED就是告诉我们Oracle的CPU的速度: 500M/s，即500,000,000个操作/秒。
SREADTIM就是single block read time，单位是milliseconds，千分之一秒，这里告诉Oracle一个single block read time是5毫秒。
MREADTIM就是multi-block read time，单位是milliseconds，千分之一秒，这里告诉Oracle一个multi-block read time是30毫秒。
MBRC是typical multiblock read size，单位是block，这里我们指定为12个block。

我们可以查询我们指定的system statistics：
SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME PVAL1
------------------------------ ----------
CPUSPEED 500
MAXTHR -1
MBRC 12
MREADTIM 30

SLAVETHR -1
SREADTIM 5

2）创建表t1：

create table t1
pctfree 99
pctused 1
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 3000
)
select
/*+ ordered use_nl(v2) */
rownum id,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000
;

注意这里我们按照前面的惯例，建立了一个pctfree为99的表，从而使block的个数和行的个数相同。

然后收集表的统计信息如下：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

3）最后我们设置不同的db_file_multiblock_read_count值（4, 8, 16, 32, 64, 128）分别做
select max(val)
from t1;

我们发现执行计划如下：

db_file_multiblock_read_count = 4

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5031 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=5031 Card=10000 Bytes=40000)

db_file_multiblock_read_count = 8

db_file_multiblock_read_count = 16

db_file_multiblock_read_count = 32

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5032 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=5032 Card=10000 Bytes=40000)

db_file_multiblock_read_count = 64

db_file_multiblock_read_count = 128

在这个实验里，我们发现引入了system statistics之后，full table scan的cost不再随着db_file_multiblock_read_count的改变而发生明显的改变，很显然，现在的CBO采用了我们指定的system statistics来计算cost。为了进一步明确Oracle是如何使用这些system statistics的，我们需要生成一个更详细的explain plan，这个详细的plan可以通过脚本plan_run92.sql生成（脚本可以在http://fusnow.itpub.net/resource/681/18171获得，由于网页的问题，请使用右键点击并另存），这个脚本的用法是把SQL写入到一个target.sql的脚本里，然后直接调用plan_run92.sql（请保证plan_run92.sql和target.sql在同一个目录下）。

比如现在我的target.sql内容是：
cat target.sql
select max(val)
from t1;

我们运行@plan_run92.sql得到的新的explain plan是：

Id Par Pos Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
0 #### SELECT STATEMENT (all_rows) Old Cost (5031,1,4) New Cost (5001,72914400,0)
1 0 1 SORT (aggregate) Old Cost (0,1,4)
2 1 1 1 TABLE ACCESS (analyzed) LII T1 (full) Old Cost (5031,10000,40000) New Cost (5001,72914400,0)

Old Cost (5031,10000,40000) 部分就是以前的cost, cardinality和bytes
我们需要引起注意的是New Cost (5001,72914400,0)这个部分，这个部分的内容意思是（IO cost, CPUCycles，temp_space）

IO cost
根据前面我们介绍过的9i 的Cost计算公式：
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim

进一步可以写为：
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +
#CPUCycles / (cpuspeed * sreadtim)
)

这里由于做的是全表扫描，所以:
#SRds=0

再根据我们指定的
dbms_stats.set_system_stats('CPUSPEED',500);
dbms_stats.set_system_stats('SREADTIM',5.0);
dbms_stats.set_system_stats('MREADTIM',30.0);
dbms_stats.set_system_stats('MBRC',12);
我们知道mreadtim=30毫秒，sreadtim=5毫秒，MBRC=12（即每次多块读取需要读取12个block，我们又知道整个表一共有10000个block，所以#MRds = 10000/12 ），

最后：
#MRds * mreadtim / sreadtim= 10000/12 * 30/5 = 5000

我们前面提到每次全表扫描多有一个额外的开销，就是读取table的segment header，所以这个全表扫描的IO部分的cost就是5001，对应new cost的第一块：
New Cost (5001,72914400,0)

CPU Cost：
CPU的Cost计算也很简单，我们从explain plan里知道#CPUCycles = 72914400，我们在system statistics里面指定了cpuspeed = 500M个cycles/秒，以及sreadtim = 5毫秒
CPU Cost
= #CPUCycles / (cpuspeed * sreadtim)
= 72914400 / (500 * 5000)
= 29.16576
约等于30

所以最后我们这个full table scan的cost是5001 + 30 = 5031，和我们老版本的plan给出的Cost是一样的。

这里我们唯一不是很清楚的是New Cost (5001,72914400,0)里面的72914400是怎么算出来的，我只能说在执行一个SQL里面有很多需要计算的地方，具体算出来的CPUCycles是完全取决于需要做什么计算，做多少计算。

我们可以做一个简单的实验来看看不同的计算导致CPUCycles的不同。

实验2：

1）创建表T1：

create table t1(
v1,
n1,
n2
)
as
select
to_char(mod(rownum,20)),
rownum,
mod(rownum,20)
from
all_objects
where
rownum <= 3000
;

然后收集表的统计信息。
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

2）运行三个几乎相同的SQL，这三个SQL唯一的不同是where后面条件的顺序不同，注意我们特意使用/*+ cpu_costing ordered_predicates */ 这个hint来强迫Oracle遵循where后面条件的顺序来执行语句，否则Oracle的CBO将会自己选择最佳的次序。实验的SQL和结果如下：

SQL1:

select
/*+ cpu_costing ordered_predicates */
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998
;

Id Par Pos Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
0 6 SELECT STATEMENT (all_rows) Old Cost (6,1,9) New Cost (5,1070604,0)
1 0 1 1 TABLE ACCESS (analyzed) LII T1 (full) Old Cost (6,1,9) New Cost (5,1070604,0) Filter (TO_NUMBER("T1"."V1")=1 AND "T1"."N2"=18 AND "T1"."N1"=998)

SQL 2:

select
/*+ cpu_costing ordered_predicates */
v1, n2, n1
from
t1
where
n1 = 998
and n2 = 18
and v1 = 1
;

Id Par Pos Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
0 6 SELECT STATEMENT (all_rows) Old Cost (6,1,9) New Cost (5,762787,0)
1 0 1 1 TABLE ACCESS (analyzed) LII T1 (full) Old Cost (6,1,9) New Cost (5,762787,0) Filter ("T1"."N1"=998 AND "T1"."N2"=18 AND TO_NUMBER("T1"."V1")=1)

SQL 3:

select
/*+ cpu_costing ordered_predicates */
v1, n2, n1
from
t1
where
v1 = '1'
and n2 = 18
and n1 = 998
;

Id Par Pos Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
0 6 SELECT STATEMENT (all_rows) Old Cost (6,1,9) New Cost (5,770604,0)
1 0 1 1 TABLE ACCESS (analyzed) LII T1 (full) Old Cost (6,1,9) New Cost (5,770604,0) Filter ("T1"."V1"='1' AND "T1"."N2"=18 AND "T1"."N1"=998)

我们可以看到尽管这三个SQL是同义的，但由于条件的顺序不同，导致了CPU的cost不同。

对于第一个SQL, 执行的顺序是：
1. v1 = 1，这需要做3000次数据类型转换（从varchar2到number）并进行数据值的比对（=1），得出一个150行的结果集（3000/20=150）。
2. n2 = 18，需要做150次数据的值比对，应该得出8行的结果集（理论上是150/20=7.5行）。
3. n1=998，做8次数据的值比对。

所以第一个SQL的CPU cost的大致的量可以用 3000 + 150 + 8 = 3158来衡量，而对应的执行计划里的CPU cycles = 1070604

同样的方法我们发现第二个SQL的CPU cost大致为3002，而对应的CPU cycles = 762787，明显小于第一个SQL的CPU cycles，虽然我们不知道Oracle的CPU cycles的具体算法，但是我们至少可以看到影响CPU cycles的因素。

而第三个SQL的顺序和第一个SQL是一样的，但在第一步的时候不需要做varchar2到number的字段转换，从而减少了CPU的使用。

最后，如果我们去掉/*+ cpu_costing ordered_predicates */这个hint，会发现Oracle选择了cost最小的第二个SQL：

select
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998

Id Par Pos Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
0 6 SELECT STATEMENT (all_rows) Old Cost (6,1,9) New Cost (5,762787,0)
1 0 1 1 TABLE ACCESS (analyzed) LII T1 (full) Old Cost (6,1,9) New Cost (5,762787,0) Filter ("T1"."N1"=998 AND "T1"."N2"=18 AND TO_NUMBER("T1"."V1")=1)

posted on 2010-06-09 11:26 天空-天空阅读(250) 评论(0) 收藏举报

刷新页面返回顶部