CBO全表扫描代价计算公式推导(2)
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
3.1. 系统统计数据
首先，我们看系统统计数据对COST_CPU的影响。这些数据包括CPUSPEED、IOTFRSPEED、IOSEEKTIM、SREADTIM、MREADTIM，我们这里推导的是noworkload模式下的公式，SREADTIM、MREADTIM的值是由IOTFRSPEED、IOSEEKTIM和MBRC、BLKSIZ计算得出的，因此我们只需要推导MBRC、BLKSIZ在公式中的位置，而不需要推导SREADTIM、MREADTIM。这两个因子我们放在系统参数部分进行推导。
3.1.1. CPUSPEED
通过DBMS_STATS.set_system_stats修改CPUSPEED的值，再与基线数据(蓝色部分)比较：
CPUSPEED
Table Scan Cost_cpu
1000
2542429
2000
2542429
4000
2542429
8000
2542429
我们发现CPUSPEED的变化并未引起COST_CPU的变化，得出第一个规则：
规则1：CPUSPEED与#CPUCYCLES无关。
3.1.2. IOTFRSPEED
同样通过对IOTFRSPEED的修改，发现它也不影响COST_CPU的值
IOTFRSPEED
Table Scan Cost_cpu
1024
2542429
2048
2542429
4096
2542429
8192
2542429
规则2：IOTFRSPEED与#CPUCYCLES无关。
3.1.3. IOSEEKTIM
通过对IOSEEKTIM的修改，发现它同样不影响COST_CPU的值
IOSEEKTIM
Table Scan Cost_cpu
10
2542429
20
2542429
40
2542429
80
2542429
规则3：IOSEEKTIM与#CPUCYCLES无关。
注：在推导过程中，我还同时观察了快速全索引扫描、索引扫描等数据，以上参数同样没有影响它们的COST_CPU，因此这些规则同样适应这些方法方式。…
3.2. 表统计数据
表的统计数据主要有3个：HWM下的数据块数量（以下我们简称数据块数量）TABBLKS、表的记录数TABROWS和记录平均长度AVGLEN。用以上的方法，同样发现AVGLEN并不能影响COST_CPU。
3.2.1. TABROWS
使用dbms_stats.set_table_stats修改TABROWS的值，比较变化：
TABROWS
Table Scan Cost_cpu
CPU_A
CPU_B
10000
2542429
2400000
142429
20000
4942429
4800000
142429
40000
9742429
9600000
142429
80000
19342429
19200000
142429
可以看到变化很有规律，绘出曲线图：
可以看到，这是一条斜线，说明他们之间是正比的一次的关系！我们就可以通过上述数据推导出这个二元一次方程式：
10000*X + Z = 2542429 … … (1)
20000*X + Z = 4942429 … … (2)
(2) – (1) =>
10000*X = 2400000
=>
X = 2400000 / 10000 = 240
=>
Z = 142429
这样，我们得出第一个初始方程式：
公式1：COST_CPU = 240 * TABROWS + 142429
其中：
CPU_A：240 * TABROWS
CPU_B：142429
将我们在推导中没有使用的40000和80000代入方程式，结果一致。
但是，这两个常数240、142429一定不是常数那么简单，它们必定受到剩余因子的影响，我们接下来就要推导这些因子如何影响这2个数字。按照方程式的格式，我将COST_CPU划分为2个部分，暂且称为CPU_A、CPU_B（见上表）。
3.2.2. TABBLKS
继续测试TABBLKS，修改值：
TABBLKS
Table Scan Cost_cpu
CPU_A
CPU_B
10
2471214
2400000
71214
20
2542429
2400000
142429
40
2684858
2400000
284858
80
2969715
2400000
569715
这里的数据变化基本上也是线性的，其中出现些许偏差，我们假设是计算过程中的ROUND造成的。通过对基线数据（TABBLKS=20）将COST_CPU分解为A、B部分，按规律延续对其它数据分解（事实上这个分解并没有依据，而是一种类似解IQ题的方法），发现CPU_B呈线性规律。得出一个假设1：TABROWS只影响CPU_A部分，TABBLKS只影响CPU_B部分。
基于这个假设解一元一次方程式（这里TABROWS为10000）：
240*10000 + X*10 = 2471214
=>
X = 7121.4
得出第二个方程式：
公式2：COST_CPU = 240 * TABROWS + 7121.4 * TABBLKS
CPU_A：240 * TABROWS
CPU_B：7121.4 * TABBLKS
由于这个方程式是基于之前的假设得出的，为了证明这个假设，我将多种TABBLKS下的多种TABROWS的数据代入方程式，结果都吻合，我们认为这个假设是成立的。
3.3. 系统参数
系统参数中有许多参数都会影响到代价计算。但是，通过上述方法，发现大多数参数并没有影响全表扫描的COST_CPU，包括_cpu_to_io、_db_file_optimizer_read_count等。影响COST_CPU的主要是一个参数：_optimizer_block_size。
3.3.1. _optimizer_block_size
_optimizer_block_size(我们用BLKSIZ代替)设置的是优化器中计算代价的数据块大小。它对COST_CPU的影响比较特殊，其推导很是费了一番周折，通过IQ解法设立了多次假设，然后再反证假设。我们看看它的变化引起COST_CPU的变化（这里我们的基表换了，数据和之前不同TABROWS=1000000、TABBLKS=1000，但不影响推导）：
_optimizer_block_size
Table Scan Final Cost_CPU
4096
245810720
8192
247121440
16384
249742880
首先，我们还是发现它对COST_CPU的影响是线性的，即
COST_CPU = X*BLKSIZ + Z
但是，之前证明COST_CPU的方程式最少可以分为2部分，TABROWS影响CPU_A部分，TABBLKS影响CPU_B部分，那我们仍然尝试将其分解为2部分，先由基线数据类推分解：
_optimizer_block_size
Table Scan Final Cost_CPU
CPU_A
CPU_B
4096
245810720
240000000
5810720
8192
247121440
240000000
7121440
16384
249742880
240000000
9742880
这个分解是基于BLKSIZ没有影响CPU_A部分的假设（假设2）的，从分解结果看，CPU_B是呈线性规律的，也是可推导的。初步的方程式为：
COST_CPU = CPU_A + X*BLKSIZ + Z
由公式2，方程式转换为：
COST_CPU = 240*TABROWS + TABBLKS*(X*TABSIZ + Z)
解方程式组：
240*1000000 + 1000*(X*4096 + Z) = 245810720 … … (1)
240*1000000 + 1000*(X*8192 + Z) = 247121440 … … (2)
(2) – (1) =>
1000*4096*X = 1310720
=>
X = 0.32
=>
Z = 4500
得出新的公式：
公式3：COST_CPU = 240*TABROWS + TABBLKS*( 0.32*TABSIZ + 4500 )
= 240*TABROWS + 0.32*TABBLKS*TABSIZ + 4500*TABBLKS
其中：
CPU_A：240 * TABROWS
CPU_B：0.32*TABBLKS*TABSIZ + 4500*TABBLKS
再次通过随机数据对假设进行反证，全部通过。
注意：当表所在表空间的BLOCK_SIZE不为默认值（8192时），BLKSIZ则为表空间的BLOCK_SIZE。
3.4. 查询语句
这一部分是所有因子中最复杂的部分。其中一些因子是没有预计到的，而是从实际表中找到特例后，再找出特例中的特殊之处予以印证。
3.4.1. 查询字段数
这个影响因子最初是没有考虑到的，但是，当我对数据库中的一个真实表做了Trace，再将数据代入公式3时，发现结果并不吻合。这说明应该还有其他因素影响到了结果，通过比较真实表和基表，首先发现的是它们的表的字段数不一样。于是尝试增加基表的字段数，对比数据：
（TABROWS: 1000000; TABBLKS: 1000）
COLNUMS
COST_CPU
CPU_B
CPU_A
5
237121440
7121440
230000000
6
257121440
7121440
250000000
7
277121440
7121440
270000000
8
297121440
7121440
290000000
首先由公式3对基线数据进行分解，然后依照数据排列规律，对其它数据进行类推分解。从分解出来的数据规律看，我们不难得出一个假设3：字段数只影响CPU_A部分。
由于数据是线性递增的，基于假设3，可以建立方程式组：
1000000*(X*5 + Z) = 230000000 … … (1)
1000000*(X*6 + Z) = 250000000 … … (2)
(2)/1000000 – (1)/1000000 =>
X = 20
=>
Z = 130
得出新的公式：
COST_CPU = (20*COLUMNS + 130)*TABROWS + 0.32*TABBLKS*TABSIZ + 4500*TABBLKS
再次通过随机数据对假设进行反证，全部通过。
但是，等等。这里的影响因子真的是表的字段数吗？看下我们做Trace用的语句：“SELECT * FROM TABLE”，注意到这里使用的是*，说明这种情况下还有一个数字是和COLNUMS完全相等的：查询字段数。为了辨别到底是谁在起作用，或者2者都在其作用，我在导出2组数据。
第一组是相同表字段数情况下、不同查询字段数：
QRYCOLNUMS
COST_CPU
CPU_B
cpu_A
2
208121440
7121440
201000000
3
209121440
7121440
202000000
4
210121440
7121440
203000000
5
211121440
7121440
204000000
第二组数据是相同查询字段、不同表字段数：
COLNUMS
COST_CPU
CPU_B
cpu_A
5
208121440
7121440
201000000
6
208121440
7121440
201000000
7
208121440
7121440
201000000
8
208121440
7121440
201000000
从这2组数据可以看到，事实上影响因子并非表的字段数，而是查询字段数。因此真正的公式是：
公式4：COST_CPU = (20*QRYCOLUMNS + 130)*TABROWS + 0.32*TABBLKS*TABSIZ + 4500*TABBLKS
其中：
CPU_A：(20*QRYCOLUMNS + 130) * TABROWS
CPU_B：0.32*TABBLKS*TABSIZ + 4500*TABBLKS
P.S.测试过程中还发现，对查询字段加上函数、增加常量字段都不会影响结果，即下面3条件语句的代价是相同的：
select a, b from T_PEEKING11 a;
select a, to_number(b) from T_PEEKING11 a;
select a, 6, 1 from T_PEEKING11 a;
在对随后的因子推导的过程发现，剩余的因子数字规律都可以得出一个假设4：它们只影响CPU_A部分。基于这个假设，为了简化推导过程，我将CPU_A部分剥离出来：CPU_A = TYPFAC * TABROWS，而我们要做的就是对TYPFAC的推导。在这一步中，TYPFAC的公式为：
公式5：TYPFAC = CPU_A/TABROWS
公式6：TYPFAC = 20*QRYCOLNUMS + 130
到目前为止，实际上已经将“SELECT COLA, …, COLN FROM TABLE”这种情况下的全表扫描COST_CPU公式推导出来了。最少我通过对多个生产系统上的实际表进行计算、以及对各种可能数据进行调整，没有发现还有其他影响因素。
3.4.2. 过滤条件
注意：为了简化推导过程，我们这里使用的都是绑定变量。
从一个语句在Oracle中的整个处理流程中，我们知道，Oracle的Row Source Generator是需要对结果集进行处理的，因此最终结果集的数量必将成为影响代价的因素之一。我们这里虽然考察的是全表扫描，但是过滤条件还是会影响到结果集的数量。通过对比数据我们可以看到其影响：
过滤条件
COST_CPU
CPU_B
cpu_A
无
237121440
7121440
204000000
A < :V1
211121440
7121440
201000000
可以看到确实是有影响。但是怎么将这个因素数字化、并推导出方程式呢？我们可以想到的相关因素有很多，包括字段上的统计数据、过滤条件中的字段数量、过滤方式、字段数据类型、数据长度。由于无法确定这些因素，在整个推导过程中发生了不少类似之前将过滤字段数误以为是表字段数这样的错误，走了不少弯路。为了使推演过程更加清晰，在后面的推演中就不再演示这些被误导的推导过程了。
3.4.3. 字段统计数据
首先我想到的就是字段上的统计数据，包括字段平均长度(AvgLen)、字段中唯一值数量(NDV)、字段中空值数量(Nulls)、字段密度(Density)。这些数据我们都可以通过dbms_stats.set_column_stats来修改。
