§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%\rdbms\admin\utlxplan.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 计算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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 初始化参数对执行计划的影响初探
n 初始化参数db_file_multiblock_read_count
下面是db_file_multiblock_read_count值与cost的换算因子
db_file_multiblock_read_count值调整因子
4 4.175
8 6.589
16 10.398
32 16.409
64 25.895
128 40.865

test@FXSB01> show parameter db_file_multiblock_read_count
NAME                               TYPE       VALUE
------------------------------------ ----------- -------------
db_file_multiblock_read_count       integer    8
-- 根据表的统计信息和换算因子可以算出全表扫描的cost
test1 : 158/6.589=23.979约为24
test2 : 158/6.589=23.979约为24
test3 : 20/6.589=3.035约为4
均大于使用索引的cost

-- 修改该参数
test@FXSB01>
test@FXSB01> alter session set db_file_multiblock_read_count = 16
  2  /

会话已更改。

test@FXSB01> select *
  2  from test2
  3  where n1 = 100
  4  /

已选择20行。
Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=CHOOSE (Cost=16 Card=20 Bytes=206
      0)
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=16 Card=20 Bytes=2060
      )

执行计划变成全表扫描,cost计算:使用索引的cost为21,使用全表扫描的cost=158/10.398=15.195,取整后为16.

test@FXSB01> select *
  2  from test3
  3  where n1 = 100
  4  /

已选择20行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=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为1,全表扫描的cost为20/10.398=1.923,取整为2

test@FXSB01>
test@FXSB01> alter session set db_file_multiblock_read_count=32
  2  /

会话已更改。

test@FXSB01> select *
  2  from test2
  3  where n1 = 100
  4  /

已选择20行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=20 Bytes=206
      0)
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=10 Card=20 Bytes=2060
      )

执行计划为全表扫描,cost计算:使用索引的cost为21,使用全表扫描的cost=158/16.409=9.628,取整后为10.选择全表扫描.

test@FXSB01> select *
  2  from test3
  3  where n1 = 100
  4  /

已选择20行。
Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=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为1,索引叶块的一次物理读取.

n 初始化参数optimizer_index_cost_adj对执行计划的影响
n 参数optimizer_index_cost_adj是1到10000之间的一个百分值,表示索引访问和全表扫描之间相关物理I/O请求cost的一个比值.默认值100意味着索引访问与全表扫描是完全等价的.
n 最后cost=最初cost*optimizer_index_cost_adj/100

test@FXSB01> show parameter db_file_multiblock_read_count
NAME                               TYPE       VALUE
------------------------------------ ----------- --------
db_file_multiblock_read_count       integer    32
test@FXSB01> alter session set optimizer_index_cost_adj=50
  2  /

会话已更改。

test@FXSB01> select *
  2  from test2
  3  where n1 = 100
  4  /

已选择20行。
Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=20 Bytes=206
      0)
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=10 Card=20 Bytes=2060
      )

执行计划为全表扫描,cost计算:使用索引的cost=1+20*0.5=11,使用全表扫描的cost=158/16.409=9.628,取整后为10.选择全表扫描.

test@FXSB01>
test@FXSB01> alter session set optimizer_index_cost_adj=25
  2  /
会话已更改。
test@FXSB01> select *
  2  from test2
  3  where n1 = 100
  4  /
已选择20行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=CHOOSE (Cost=6 Card=20 Bytes=2060
      )
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (Cost=6 Card=20 B
      ytes=2060)
2 1    INDEX (RANGE SCAN) OF 'IDX_TEST2_N1' (NON-UNIQUE) (Cost=
      1 Card=20)

执行计划选择了使用索引,cost计算:使用索引的cost=1+20*0.25=6,索引叶物理读取cost为1,表数据块物理读取为5;使用全表扫描的cost=158/16.409=9.628, 取整后为10,数据块物理读取cost为10,.选择使用索引.

ITPub博客

CBO成本计算初探（转自ITPUB）

最新文章