§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
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博客 ” ，链接：http://blog.itpub.net/7194105/viewspace-675326/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7194105/viewspace-675326/
扫一扫
1659
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
