cost-based oracle fundamentals,读<<Cost-Based Oracle Fundamentals>>关于成本计算的疑惑。

正在阅读 Jonathan

Lewis的大作 <

Fundamentals>>, 现在正在看第五章clustering_factor，还是没有完全明白关于index的Cost的计算

大致过程和环境如下：

Here is the case.

My oracle version is oracle9.2.0.1 runing on Windows XP

db_block_size=8k

db_file_multiblock_read_count=8

optimizer_mode=all_rows

create tablespace cost

d:\oracle\ora92\orcl\cost01.dbf' size 128m

extent management local uniform size 1m segment space

management manual;

1.create test table t1

create table t1 tablespace cost

pctfree 90 pctused 10

as

select

sysdate + trunc((rownum-1) / 500) movement_date,

trunc(dbms_random.value(1,60.999)) product_id,

trunc(dbms_random.value(1,10.000)) qty,

lpad(rownum,10) small_vc,

rpad('x',100) padding

from

all_objects

where

rownum <= 10000

;

2.create index t1_i1

create index t1_i1 on t1(movement_date) tablespace

cost;

3.using dbms_stats package to analyz table t1

begin

dbms_stats.gather_table_stats(

user,

't1',

cascade => true,

estimate_percent => null,

method_opt => 'for all columns size

1'

);

end;

4. check the analyze output

SQL> select blocks, num_rows from

user_tables where table_name = 'T1';

BLOCKS NUM_ROWS

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

182 10000

select index_name, blevel, leaf_blocks,

clustering_factor

from user_indexes where table_name = 'T1'

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

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

t1_i1 1 27 182

5.Execute the following query(Query A)

SQL>

SQL> select

sum(qty)

from

t1

where

movement_date = trunc(sysdate) + 7

and product_id = 44;

Execution Plan

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

0 SELECT STATEMENT

Optimizer=ALL_ROWS (Cost=12 Card=1 Bytes=14)

1 0 SORT

(AGGREGATE)

2 1 TABLE

ACCESS (BY INDEX ROWID) OF 't1' (Cost=12 Card=8 Bytes=112)

3 2 INDEX (RANGE SCAN) OF 't1_i1'

(NON-UNIQUE) (Cost=2 Card=500)

why the index range scan's cost is 2

accound to your baseline formula

cost =

blevel +

ceiling(leaf_blocks * effective index selectivity)

+

ceiling(clustering_factor * effective table

selectivity)

Question 1

blevel = 1

leaf_blocks = 27

index selectivity = 1/20

clustering_factor = 182

Total cost = 1+ ceiling(27/20) + ceiling(182/20) = 1 +

2 + 10 =13

and I think index range scan's

cost is 3 not 2.

Question 2

because blevel>=1 and ceiling(leaf_blocks

* effective index selectivity >=1,

Can I think index's cost always > =

2 (知道这个猜想是不对的，但是错在哪里呢？)

Question 3

if I drop index t1_i1 and creating the following index

create index t1_i1 on t1(movement_date, product_id)

tablespace cost;

select index_name, blevel, leaf_blocks,

clustering_factor

from user_indexes where table_name = 'T1'

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

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

t1_i1 1 31 6689

execute query A again

SQL>

SQL> select

sum(qty)

from

t1

where

movement_date = trunc(sysdate) + 7

and product_id = 44;

Execution Plan

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

0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1

Bytes=14)

1  0  SORT (AGGREGATE)

2  1  TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=7

Card=8 Bytes=112)

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

Card=8)

Execution Plan index range scan cost = 1

And I think index range scan's cost = 1 + ceiling(27*(1/20*1/60) =

2(not Cost = 1)

Total cost= 2 + ceil(6689*(1/20*1/60)) = 2 + 6 = 8

Question 4

I have used event 10053 to trace query A,And Get

IX_SEL: 0.0000e+000

Does it means index selectivity is 0(zero)?

Oracle CBO who to calculate IX_SEL?

以上的内容已经写信给了Jonathan Lewis和 Wolfgang

Breiting，但是快一星期了，他们还是没有回复我。

因为大师没有给我回复，我自己现在有如下的猜想，在index使用等值查询的条件下，index部分的成本有如下猜想：

1、如果索引的Blevel＝1，

cost＝ceiling(index selectivity * Leaf_blocks)

2、如果索引的Blevel＝2，cost＝Blevel ＋

ceiling(index selectivity * Leaf_blocks)

这个猜想可以解释上面两个index

cost的计算。

＝＝＝＝＝＝＝＝＝＝＝＝＝

create table t1

nologging

as

select

trunc(dbms_random.value(0,25)) n1,

rpad('x',40) ind_pad,

trunc(dbms_random.value(0,20)) n2,

lpad(rownum,10,'0') small_vc,

rpad('x',200) padding

from

all_objects

where

rownum <= 10000

;

create index t1_i1 on t1(n1, ind_pad, n2)

nologging pctfree 91 ;

begin

dbms_stats.gather_table_stats(

user,

't1',

cascade => true,

estimate_percent => null,

method_opt => 'for all columns size

1'

);

end;

/

SQL> select

table_name,blocks from user_tables where

table_name='CBO2';

TABLE_NAME BLOCKS

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

CBO2 10000

SELECT num_rows,

distinct_keys,blevel, leaf_blocks, clustering_factor cluf,

avg_leaf_blocks_per_key leaf_KEY, avg_data_blocks_per_key

data_Key

FROM user_indexes

WHERE table_name = 'CBO2_T1'

AND index_name = 'CBO2_I1'

;

NUM_ROWS DISTINCT_KEYS BLEVEL

LEAF_BLOCKS CLUF Leaf_KEY data_KEY

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

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

10000 500 2 1111 9741 2 19

执行下面的查询：

SQL> l

1 select

2 small_vc

3 from

4 cbo2

5 where

6 n1 = 2

7 and ind_pad = rpad('x',40)

8 and n2 = 3

9*

SQL> /

Execution Plan

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

0 SELECT STATEMENT

Optimizer=ALL_ROWS (Cost=25 Card=20 Bytes=1160)

1 0 TABLE ACCESS (BY

INDEX ROWID) OF 'CBO2' (Cost=25 Card=20 Bytes=1160)

2 1 INDEX

(RANGE SCAN) OF 'CBO2_I1' (NON-UNIQUE) (Cost=5 Card=20)

index cost=blevel+ceiling(1111/500)=2+3 = 5

如果把index tree的高度降到1看看

create index cbo2_i1 on cbo2(n1, ind_pad, n2) tablespace

cost;

上面的index结果如下：

NUM_ROWS

DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUF Leaf_KEY data_KEY

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

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

10000 500 1 82

9741 1

19

再执行上面的查询，cost结果如下:

Execution

Plan

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

0 SELECT STATEMENT

Optimizer=ALL_ROWS (Cost=21 Card=20 Bytes=1160)

1 0 TABLE ACCESS (BY

INDEX ROWID) OF 'CBO2' (Cost=21 Card=20 Bytes=1160)

2 1 INDEX

(RANGE SCAN) OF 'CBO2_I1' (NON-UNIQUE) (Cost=1

Card=20)

index cost = ceiling(82/500) =1

上面的假设也可以解释现象。

Johnthan 给我回了信，还是没有仔细看书(5月8日)

在第四章的LOOSE END中有说：

Indexes where the blevel is set to 1 (so the index

goes straight from the root block to the leaf blocks). The

optimizer effectively ignores the blevel if every column in the

index appears in an equality predicate.