CBO成本計算初探

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/，如需轉載，請註明出處，否則將追究法律責任。

上一篇：撤消表空間資料檔案丟失的恢復.

下一篇：應用9i新特性：在資料庫中使用不同塊大小的表空間

請登入後發表評論登入

全部評論

husthxd

長期從事政務、金融等行業產品研發和架構設計工作，ITPUB資料庫版塊資深版主，對Oracle、PostgreSQL以及大資料等相關技術有深入研究。現就職於廣州雲圖資料技術有限公司，系統架構師。

註冊時間：2007-12-28

博文量
1243
訪問量

3715781

CBO成本計算初探

§1.1 簡介

§1.2 建立測試資料

§1.3 CBO計算Cost原理初探

§1.4 初始化引數對執行計劃的影響初探

§1.4.1 初始化引數db_file_multiblock_read_count

最新文章

相關文章