ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CBO学习笔记系列4(转载)

CBO学习笔记系列4(转载)

原创 Linux操作系统 作者:alsrt 时间:2011-05-19 14:04:27 0 删除 编辑

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 ptimizer=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

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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 = 16

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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 = 32

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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 = 128

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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)


在这个实验里,我们发现引入了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)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8410760/viewspace-732352/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-02

  • 博文量
    34
  • 访问量
    33701