ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CBO成本计算初探

CBO成本计算初探

原创 Linux操作系统 作者:husthxd 时间:2004-09-30 00:00:00 0 删除 编辑

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         

          0)                                                                                                                                            

   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

4.175

8

6.589

16

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

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1394
  • 访问量
    3841374