HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

CBO全表扫描代价计算公式推导(3)

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2009-08-01 15:03:35

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

3.4.4.               过滤字段位置

这一点实际上是不能单独考虑的,因为如果存在多个过滤字段,每个过滤字段都有一个字段位置。我们这先考虑一个过滤字段的情况。

select /*+full(a)*/a, b, c, d, e from T_PEEKING11 a where col < :v2;

TABROWS: 1000000; TABBLKS: 1000; QRYCOLNUMS: 5

FLTCOLPOS

COST_CPU

CPU_B

cpu_A

1

211121440

7121440

204000000

2

230121440

7121440

223000000

3

249121440

7121440

242000000

4

268121440

7121440

261000000

很好,变化是线性的,且符合假设4的情况。因此我们需要推导FLTCOLPOSTYPFAC的方程式。在公式6中,存在2个不确定因子,因此我们要解一个4元一次方程式组,而这里我们只变换了公式其中一个数,因此只能得出2个方程式来,还不足以求解

(X1*1+Z1)*5 + (X2*1+Z2) = 204000000/1000000      … …      (1)

(X1*2+Z1)*5 + (X2*2+Z2) = 223000000/1000000      … …      (2)

再变化公式中的一个系数——QRYCOLNUMS

select /*+full(a)*/a, b, c, d from T_PEEKING11 a where col < :v2;

TABROWS: 1000000; TABBLKS: 1000; QRYCOLNUMS: 4

FLTCOLPOS

COST_CPU

CPU_B

cpu_A

1

210121440

7121440

203000000

2

229121440

7121440

222000000

3

248121440

7121440

241000000

4

267121440

7121440

260000000

得出方程组中的另外2个方程式

(X1*1+Z1)*4 + (X2*1+Z2) = 203000000/1000000      … …      (3)

(X1*2+Z1)*4 + (X2*2+Z2) = 222000000/1000000      … …      (4)

(2) – (1), (4) – (3) =>

X1*5 + X2 = 19

X1*4 + X2 = 19

=>

X1 = 0

=>

X2 = 19

再解二元一次方程组:

Z1*5 + 19 + Z2 = 204

Z1*4 + 19 + Z2 = 203

=>

Z1 = 1

Z2 = 180

得出新的TYPFAC的公式:

公式7TYPFAC = QRYCOLNUMS + 19*FLTCOLPOS + 180

3.4.5.               有效字段数

这个概念可能你一时无法理解。这是我在测试过程中出现违反之前公式的特例中发现的另外一个影响因子。先看以下的测试数据:

select /*+full(a)*/a from T_PEEKING11 a where c < :v2;

COST_CPU

CPU_B

cpu_A

247121440

7121440

240000000

select /*+full(a)*/a, b from T_PEEKING11 a where c < :v2;

COST_CPU

CPU_B

cpu_A

247121440

7121440

240000000

select /*+full(a)*/a, b, d from T_PEEKING11 a where c < :v2;

COST_CPU

CPU_B

cpu_A

248121440

7121440

241000000

第一份数据查询了1个字段,第二份数据查询了2个字段,但它们的结果却是相同的!而第三份数据是3个字段,它的结果与前面2份又不相同!这样一个结果最初很让我困惑,似乎我们之前的公式46不成立。而且当我将三份数据代入公式7发现结果不吻合:

1+19*3+180 = 238 != 240000000/1000000

2+19*3+180 = 239 != 240000000/1000000

3+19*3+180 = 240 != 241000000/1000000

OK!似乎过程中出现了偏差,抑或是我们的某个假设有错误。让我们再仔细观察一下这些特例数据,看看是否有规律可循。

先看第一、二份数据——他们的查询字段数变了,但是COST_CPU并没有变化,这里我留意到了它们之间的一个共性:查询字段的位置都小于过滤字段的位置(a1b2c3)!那我们是否可以得出一个这样的假设5:位置小于或等于过滤字段位置的查询字段都不计算在有效的查询字段数内。通过多组数据,证明了该假设是成立的(无法找到特例,具体数据不再列举)。

那么,公式4中的QEYCOLNUMS值是从哪来的呢?先依据公式7,算出第12份数据中的QEYCOLNUMS

240000000/1000000 – 180 – 19*3 = 3

3,这个数字意味这什么呢?我们发现过滤字段位置FLTCOLPOS也是为3,难道是巧合(或许我的这个发现是巧合J)。那我们可再假设一个命题,假设6:查询字段都不是有效字段时(或者说查询字段的位置都小于等于过滤字段),查询字段数(我们这里改称为有效字段数)为查询字段位置(即查询字段及其之前字段数)。同样,这个假设我也通过一组数据证明了(无法找到特例)。

但是,这两个假设的成立还不足以解释第三份数,按照公式7,其QRYCOLNUMS = 241000000/1000000 – 180 – 19*3 = 4,不等于过滤字段位置3。不用急,按照假设5a, b两个字段不能计算在有效字段内,但是d不是假设5的范围之内的字段,如果将它考虑成有效字段,再加上假设6,由过滤字段的有效字段数为3,得出(3+1)=4。我们可以得出另外一个假设7:位置大于过滤字段位置的查询字段都计算在有效的查询字段数内。通过多组数据,证明了该假设是成立的。

OK,过滤字段位置的影响就这些吗?让我们再看下面一组测试数据:

select /*+full(a)*/X from T_PEEKING11 a where c < :v3;

X

COST_CPU

CPU_B

CPU_A

d

512873940

7121440

505752500

e

512876440

7121440

505755000

f

512878940

7121440

505757500

g

512881440

7121440

505760000

d, e

512876440

7121440

505755000

d, f

512878940

7121440

505757500

f, g

512881440

7121440

505760000

d, g ,h

512883940

7121440

505762500

由这组数据计算出来的EFFCOLNUMS,我们不难发现规律:有效字段数由位置大于过滤字段位置的查询字段中的最大位置决定。这也可以成为我们的一个假设8,并且同样用多组其它数据可以证明它。

由以上假设,可以得出:

公式8EFFCOLNUMS = EFFQRYCOLNUM + FLTCOLPOS

EFFCOLNUMS:有效字段数

FLTCOLPOS:过滤字段位置

EFFQRYCOLNUM:有效查询字段。如果查询字段中存在位置大于过滤字段位置的字段,则为查询字段最大位置与过滤字段位置之差,否则为0

公式7QRYCOLNUMS也变为EFFCOLNUMS

公式9TYPFAC = EFFCOLNUMS + 19*FLTCOLPOS + 180

3.4.6.               过滤字段最大位置

注:对多个过滤字段的情况中,我们先推导只有AND的情况,最后在引入OR进行推导。

完成上述推导后,我本来应该推导过滤字段数的影响公式的。但是,测试数据的特殊变化让我留意到了多过滤条件下的另外一个影响因素:过滤字段最大位置。

先看这一组数据:2个过滤字段,其中位置最大字段不变

(所有字段数据类型相同,TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5

COLA

COLB

columns

COST_CPU

CPU_B

cpu_A

1

5

A&E

289621440

7121440

282500000

2

5

B&E

289621440

7121440

282500000

3

5

C&E

289621440

7121440

282500000

4

5

D&E

289621440

7121440

282500000

可以发现,小于最大字段位置的其他字段的位置变化不会影响结果。

再看另外一组数据:2个过滤字段,其中字段最大位置变化

(所有字段数据类型相同,TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5

COLA

COLB

columns

COST_CPU

CPU_B

cpu_A

1

2

A&B

229771440

7121440

222650000

1

3

A&C

249721440

7121440

242600000

1

4

A&D

269671440

7121440

262550000

1

5

A&E

289621440

7121440

282500000

这组数据发生了线性变化。我们可以得出一个这样的结论:多个AND过滤字段中,只有其中的最大位置会影响COST_CPU,其他字段位置不影响。基于这个结论,我们先下一个这样假设8:公式89中的FILCOLPOS就是过滤字段最大位置MAXFLTCOLPOS但是,由于上面的数据中存在2个变数:MAXFLTCOLPOS和过滤字段数FLTCOLNUM,且FLTCOLNUM并不呈线性变化(有其它数据得出,在后面给出),不能直接通过解方程组的方法解出。那么我先看看基于这个假设,有没有办法解出关于FLTCOLNUM的非一次方程出来。

先由假设8得出新的公式

公式8-1EFFCOLNUMS = EFFQRYCOLNUM + MAXFLTCOLPOS

公式10TYPFAC = EFFCOLNUMS + 19*MAXFLTCOLPOS + 180

3.4.7.               过滤字段数

过滤字段数在整个公式中是最特殊。无法列出简单方程式,最终还是通过观察——假设公式——反证的方法找出答案。先看它对COST_CPU的影响:

(所有字段数据类型相同,TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5; MAXFILPOS: 5

FLTCOLNUM

COST_CPU

CPU_B

CPU_A

1

287121440

7121440

280000000

2

289621440

7121440

282500000

3

289746440

7121440

282625000

4

289752690

7121440

282631250

5

289753003

7121440

282631563

看到这组数据的变化不是线性的。这让我很难列方程式。但是,可以通过其他方法看看是否有规律可循,先根据公式5解出各个TYPFAC来:

FLTCOLNUM

COST_CPU

CPU_B

CPU_A

TYPFAC

 

1

287121440

7121440

280000000

280

2.5

2

289621440

7121440

282500000

282.5

0.125

3

289746440

7121440

282625000

282.625

0.00625

4

289752690

7121440

282631250

282.63125

0.000313

5

289753003

7121440

282631563

282.63156

 

再将计算出的TYPFAC前后相减,得出另外一组数据。哈哈,这是一组有规律的数据:前后相差20倍!2.5/20 = 0.125; 0.125/20 = 0.00625; 0.00625/20 = 0.0003125 ~= 0.000313。按照这样的规律,推测出这样一个公式:

公式10-1TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 50*(20^(1 – FLTCOLNUM) + 20^(2 – FLTCOLNUM) + … + 20^( FLTCOLNUM- FLTCOLNUM))

         = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 50*(1/20)^(FLTCOLNUM-1) + 50*(1/20)^(FLTCOLNUM-2) + … + 50(1/20)^(FLTCOLNUM- FLTCOLNUM)

         = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 50*(1/20)^0 + 50*(1/20)^1 + … + 50*(1/20)^(FLTCOLNUM-1)

通过更多的其它数据,证明了这个公式是成立的,说明之前假设8也是成立的。

3.4.8.               过滤字段数据类型

在前面的推导过程中,为了消除数据类型的影响,我将所有字段都设置为了VARCHAR2(50)。现在,可以开始推导数据类型的影响。

首先,通过数据证明数据类型对有效查询字段没有影响(数据不再列出)。

下面要推导的是过滤字段数据类型对公式的影响。由于数据类型之间的变化不是线性的,我们假设它是一个CACE ... WHEN的关系:

限制将数据类型改为CHAR,发现结果和VARCHAR2一直

再把所有数据类型改为NUMBER

(所有字段数据类型相同,TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5; MAXFILPOS: 5

FLTCOLNUM

COST_CPU

CPU_B

CPU_A

TYPFAC

 

1

387121440

7121440

380000000

380

7.5

2

394621440

7121440

387500000

387.5

0.375

3

394996440

7121440

387875000

387.875

0.01875

4

395015190

7121440

387893750

387.89375

0.000938

5

395016128

7121440

387894688

387.89469

 

通过数据对比,不难发现公式10-1演变成了:

公式10-2TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 150*(1/20)^0 + 150*(1/20)^1 + … + 150*(1/20)^(FLTCOLNUM-1)

 

然后再推导DATE类型

FLTCOLNUM

COST_CPU

CPU_B

CPU_A

TYPFAC

 

1

537121440

7121440

530000000

530

15

2

552121440

7121440

545000000

545

0.75

3

552871440

7121440

545750000

545.75

0.0375

4

552908940

7121440

545787500

545.7875

0.001875

5

552910815

7121440

545789375

545.78938

 

公式演变为:

公式10-3TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 300*(1/20)^0 + 300*(1/20)^1 + … + 300*(1/20)^(FLTCOLNUM-1)

 

其它数据类型就不再推导了。由此,可以得出新的计算公式:

公式11TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + COLTYPEFAC*(1/20)^0 + COLTYPEFAC*(1/20)^1 + … + COLTYPEFAC*(1/20)^(FLTCOLNUM-1)

数据类型

COLTYPEFAC

CHAR

50

VARCHAR

50

NUMBER

150

DATE

300

3.4.9.               段数长度

再观察字段长度是否有影响,经测试数据证实,长度不影响COST_CPU

3.4.10.           过滤字段计算顺序

在前面的推导中,表中所有字段的数据类型都是统一的,但是我们也看到,不同数据类型是有不同COLTYPEFAC系数的。那么,当存在混合的数据类型结果有如何呢?还是从特殊情况入手:最大位置的过滤字段设置为number,其他字段设置为date(之所以这样设,也是我故意安排,如果将两个数据类型反过来,结果就很难推了,在后面的推导中会说明这个问题),观察变化:

FLTCOLNUM

COST_CPU

CPU_B

CPU_A

TYPFAC

 

1

387121440

7121440

380000000

380

15

2

402121440

7121440

395000000

395

0.75

3

402871440

7121440

395750000

395.75

0.0375

4

402908940

7121440

395787500

395.7875

0.001875

5

402910815

7121440

395789375

395.78938

 

可以看到,COLTYPEFAC的系数变化是基于DATE的,尝试完全按照DATECOLTYPEFAC计算出来,比较数据:

FLTCOLNUM

TYPFAC_CAL

 

1

380

 

2

387.5

0.375

3

387.875

0.01875

4

387.89375

0.0009375

5

387.8946875

 

发现数据变化也是呈之前的幂排列的方式变化的,其基数正是DATENUMBERCOLTYPEFAC之差(通过类似方法对DATEVARCHAR2NUMBERVARCHAR2也能得出同样结果)。因此,变化公式11

公式12TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + COLTYPEFAC1*(1/20)^0 + COLTYPEFAC2*(1/20)^1 + … + COLTYPEFACn*(1/20)^(FLTCOLNUM-1)

其中,过滤字段的处理顺序将在3.4.13中再次确认。

根据这个公式,再对测试数据进行计算,结果都吻合。

 

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat