CBO成本计算初探

最新推荐文章于 2023-12-19 19:53:11 发布

cuichao1900

最新推荐文章于 2023-12-19 19:53:11 发布

阅读量199

点赞数

os：windows 2000 server sp4

数据库：Oracle 901

本文可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明

http://blog.itpub.net/post/11/2629

CBO成本计算初探

§1.1 简介

本文简要介绍了CBO成本计算的基本原理，并初步解释了初始化参数optimizer_index_cost_adj和db_file_multiblock_read_count对CBO的影响。

数据库版本为Oracle 9.0.1

平台为Windows2000

system@FXSB01> select *from v$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production

PL/SQL Release 9.0.1.1.1 - Production

CORE 9.0.1.1.1 Production

TNS for 32-bit Windows: Version 9.0.1.1.0 - Production

NLSRTL Version 9.0.1.1.1 – Production

§1.2 建立测试数据

system@FXSB01> @conn test/test@test

已连接。

test@FXSB01> -- 建立执行计划表

test@FXSB01> @%ORACLE_HOME%rdbmsadminutlxplan.sql

表已创建

test@FXSB01>

test@FXSB01> -- 建立测试表

test@FXSB01> -- 表1,2除索引列外有其他列,表3没有其他列

test@FXSB01> drop table test1

2 /

表已丢弃。

test@FXSB01> create table test1

2 (

3 n1 number(10),

4 c1 char(100)

5 )

6 /

表已创建。

test@FXSB01> drop table test2

2 /

表已丢弃。

test@FXSB01> create table test2

2 (

3 n1 number(10),

4 c1 char(100)

5 )

6 /

表已创建。

test@FXSB01> drop table test3

2 /

表已丢弃。

test@FXSB01> create table test3

2 (

3 n1 number(10)

4 )

5 /

表已创建。

test@FXSB01> -- 插入test1唯一值

test@FXSB01> begin

2 for i in 1..5000 loop

3 insert into test1 values(i,'test');

4 end loop;

5 end;

6 /

PL/SQL 过程已成功完成。

test@FXSB01> declare

2 i number;

3 begin

4 i := 1;

5 for j in 1..5000 loop

6 i := mod(j,250);

7 insert into test2 values(i,'test');

8 insert into test3 values(i);

9 end loop;

10 end;

11 /

PL/SQL 过程已成功完成。

test@FXSB01> commit

2 /

提交完成。

test@FXSB01> -- 建立索引

test@FXSB01> create index idx_test1_n1 on test1(n1)

2 /

索引已创建。

test@FXSB01> create index idx_test2_n1 on test2(n1)

2 /

索引已创建。

test@FXSB01> create index idx_test3_n1 on test3(n1)

2 /

索引已创建。

test@FXSB01> analyze table test1 compute statistics

2 /

表已分析。

test@FXSB01> analyze table test2 compute statistics

2 /

表已分析。

test@FXSB01> analyze table test3 compute statistics

2 /

表已分析。

§1.3 CBO计算Cost原理初探

CBO的成本计算主要由物理I/O组成,实际公式为:

IO + CPU/1000 + NetIO*1.5

IO表示物理I/O请求,’CPU’表示逻辑I/O请求,’NetI/O’表示通过数据库连接访问远程数据库的逻辑I/O请求.CBO会尝试计算所有可能执行计划的物理I/O,保留只需要最小物理I/O的计划.

n 通过以下简单的例子,初步探究CBO是如何计算cost的.

n 下面是表统计信息和索引统计信息

test@FXSB01> select table_name,blocks,num_rows

2 from user_tables

3 /

TABLE_NAME BLOCKS NUM_ROWS

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

PLAN_TABLE

TEST1 158 5000

TEST2 158 5000

TEST3 20 5000

test@FXSB01> select

2 table_name ,

3 num_rows ,

4 avg_leaf_blocks_per_key l_blocks,

5 avg_data_blocks_per_key d_blocks,

6 clustering_factor cl_fac

7 from user_indexes

8 /

TABLE_NAME NUM_ROWS L_BLOCKS D_BLOCKS CL_FAC

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

TEST1 5000 1 1 157

TEST2 5000 1 20 5000

TEST3 5000 1 15 3875

各列的粗略解释：

avg_leaf_blocks_per_key:每个索引值的平均叶块数目

avg_data_blocks_per_key:每个索引值的平均数据块数目

clustering_factor:B树叶块和表数据之间的关系称为CLUSTERINT_FACTOR.索引叶子块指向的数据块越多,该参数值越小,在范围扫描使用索引的性能越好.如果该值与表的块数相接近,表示表行顺次按索引排序;如果该值与表的行数接近,表示表行不是按索引排序.该值很高的索引通常在范围扫描中不使用.

test@FXSB01> set autotrace trace exp

test@FXSB01> select *from test1 where n1 = 100

2 /

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=103)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=2 Card=1 By

tes=103)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST1_N1' (NON-UNIQUE) (Cost=

1 Card=1)

cost计算：从t1的索引统计信息中得知，idx_test1_n1的l_blocks，d_blocks均为1，cost＝1+1=2，索引叶块物理读取cost为1，数据块物理读取cost为1

test@FXSB01> select *from test2 where n1 = 100

2 /

已选择20行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=20 Bytes=206

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (Cost=21 Card=20

Bytes=2060)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST2_N1' (NON-UNIQUE) (Cost=

1 Card=20)

cost计算：从t2的索引统计信息中可以看出，idx_test2_n1的l_blocks为1，d_blocks为20，cost＝1+20=21，其中索引叶块物理读取cost为1，数据块物理读取cost为20.

test@FXSB01>

test@FXSB01> select *from test3 where n1 = 100

2 /

已选择20行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=20 Bytes=60)

1 0 INDEX (RANGE SCAN) OF 'IDX_TEST3_N1' (NON-UNIQUE) (Cost=1

Card=20 Bytes=60)

cost计算：从t3的索引统计信息，idx_test2_n1的l_blocks为1，d_blocks为15，但因为无需访问表,故cost＝1，索引叶块物理读取cost为1

如果单纯是选择索引列的话,不需要访问表数据块.如下例所示:

test@FXSB01> select n1 from test2 where n1=100;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=20 Bytes=60)

1 0 INDEX (RANGE SCAN) OF 'IDX_TEST2_N1' (NON-UNIQUE) (Cost=1

Card=20 Bytes=60)

cost计算：cost=1，索引叶块物理读取cost为1

§1.4 初始化参数对执行计划的影响初探

§1.4.1 初始化参数db_file_multiblock_read_count

下面是db_file_multiblock_read_count值与cost的换算因子

db_file_multiblock_read_count值

调整因子

4.175

6.589

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

上一篇：撤消表空间数据文件丢失的恢复.

下一篇：应用9i新特性：在数据库中使用不同块大小的表空间

请登录后发表评论登录

全部评论

<%=items[i].createtime%>

<%=items[i].content%>

<%if(items[i].items.items.length) { %>

<%for(var j=0;j

<%=items[i].items.items[j].createtime%> 回复

<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>

<%}%> <%if(items[i].items.total > 5) { %>

还有<%=items[i].items.total-5%>条评论 ) data-count=1 data-flag=true>点击查看

<%}%>

<%}%> <%}%>

husthxd

ITPUB数据库版块资深版主，对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司，系统架构师。

注册时间：2007-12-28

博文量
1357
访问量

3816009

CBO成本计算初探

§1.1 简介

§1.2 建立测试数据

§1.3 CBO计算Cost原理初探

§1.4 初始化参数对执行计划的影响初探

§1.4.1 初始化参数db_file_multiblock_read_count

最新文章