CBO学习笔记1

http://hi.baidu.com/richard%5Fcxs/blog/item/5ae2f9c80979d6157e3e6f4e.html 

什么是COST？

1. 关于CBO，我们都知道CBO是optimizer based on cost，我们很清楚optimizer是什么，based是什么，On是什么，但问题是：cost是什么？

在Oracle8i里面，一条语句的cost基本上代表着Oracle为了完成这条语句而必须执行的IO次数，对于一条SQL语句，CBO产生多个不同的执行计划并选择其中需要IO次数最少的Plan。尽管Oracle8i的designing and tuning performance文档里面说：
The optimizer calculates the cost of each possible access method and join order based on the estimated computer resources, including (but not limited to) I/O and memory。

但实际上在8i里，我们看到的CBO确实是只关心IO的次数。可能在设计8i的时候，Oracle的开发人员意识到仅仅计算IO对于CBO来说是不完美的，也已经在Oracle8i的代码里面加入了计算其他因素的程序，但这些（如果有的话）都对用户隐藏了起来，当然也没有记录在8i的官方文档里，结果还是相当于没有。----- 这确实是Oracle开发优化器的风格，对于某一个很有前途的功能，首先在最初的版本里把这个功能作为一个隐含参数加入到Oracle里，并且设置这个参数为false，在接下来的版本里把这个参数偷偷的改成true，但是不影响cost，也就是说，如果这个功能是收集新的信息，这些信息会被收集，但不会被CBO使用。最后，当成熟的时候，Oracle会在最后一个版本里把这个参数改成true并且影响cost的计算，这时候很可能这个参数也从原来的隐含参数变成一个可见的参数。

让我们回到原来的话题，我们知道Oracle8i的CBO关心的cost就是为完成一个SQL所需要的IO次数，这种计算方法显然是不完美的，它至少忽略了三个客观的事实：
1，了当我们执行一个IO的时候，在这次IO里，我们可能读1个Block，也有可能是读多个block，很明显读1个Block的cost和读多个block的cost是不可能一样的，但8i的CBO里面并没有对这两种IO进行区别。所有的IO都被当作是单block读取来计算的。

2，IO并不是整个SQL执行过程中耗费的资源的全部，还应该考虑CPU的使用，这一点8i的CBO也是不考虑的。

3，当我们执行一次IO的时候，我们需要的数据block很可能已经在SGA的buffer cache里了，很明显这时候的IO的cost要比从datafile读取要小，oracle8i的CBO同样也没有重视这个问题。

在Oracle9i，Cost的内涵得到了丰富，在9i里面，计算一条SQL的COST考虑到了我们上面的客观事实的前两条（至于第三条，我们晚一点讨论）：明确的区别了一次IO里面单block读取和多block读取的不同，把CPU使用做为一种cost的一种。于是，9i里面的我们关于Cost的公式大概看上去就是这样的：

cost = "单块读的cost" + "多块读的cost" + "CPU的cost"

延续8i的说法，单块读的cost其实就是单块读的次数，但是对于多块读和CPU我们不能简单的解释成多块读的次数和CPU操作的次数，因为单位不统一，我们必须把"多块读的cost"和"CPU的cost"也转换成单块读的次数，怎么转换呢？由于我们知道时间是一个统一的单位，而时间等于操作的次数乘以每次操作所用的时间，所以，我们实际上可以是通过
把"多块读的次数" * "每个多块读的时间" / "每个单块读的时间" 把多块读的次数转成"单块读的次数"，
把"CPU的操作次数" * "每个CPU操作的时间" / "每个单块读的时间"也转换成"单块读的次数"。

于是我们可以把原来的公式改写：

cost = "单块读的cost" + "多块读的cost" + "CPU的cost"
= "单块读的次数" + "多块读的cost" + "CPU的cost"
= ("单块读的次数" * "每个单块读的时间" + "多块读的次数" * "每个多块读的时间" + "CPU的操作次数" * "每个CPU操作的时间")/ "每个单块读的时间"
= ("单块读的次数" * "单块读的速度" + "多块读的次数" * "每个多块读的时间" + "CPU的操作次数" /"CPU速度")/ "每个单块读的时间"

这里注意一下，因为我们一般看CPU都是看速度，比如500兆次/s，很少看每个CPU操作用多少时间，比如0.0000001秒/每操作，所以我们把公式里的"CPU的操作次数" * "每个CPU操作的时间"最后改写为"CPU的操作次数" /"CPU速度"

 

大家也可以在9i的performance tuning guide and reference里面看到同样的公式：

According to the CPU costing model:

Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim

where
#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second

The cost is the time spent on single-block reads, plus the time spent on multiblock reads, plus the CPU time required, all divided by the time it takes to do a single-block read. Which means the cost is the total predicted execution time for the statement, expressed in units of the single-block read time.

在Oracle9i里，当为一个SQL选择执行计划的时候，Oracle可以预测出执行这个SQL需要多少个单块读取，多少个多块读取，多少次CPU操作。那么我们怎么知道"每个单块读的时间"，"每个多块读的时间" 和"CPU速度"呢，很简单：通过统计信息，不过这不是我们常说的表的统计信息或schema的统计信息，而是Oracle9i里面才开始有的（8i里面没有哦，所以8i的cost只能考虑单块读:)），叫做system statistics。对于system

statistics的统计我们可以在系统正常workload的时候执行：
execute dbms_stats.gather_system_stats('start')
execute dbms_stats.gather_system_stats('stop')

来统计start和stop期间的系统的真实情况，

也可以手工指定一个确定的值：
begin
dbms_stats.set_system_stats('CPUSPEED',500);
dbms_stats.set_system_stats('SREADTIM',5.0);
dbms_stats.set_system_stats('MREADTIM',30.0);
dbms_stats.set_system_stats('MBRC',12);
end;
/

alter system flush shared_pool;

由于手工指定system statistics不会影响当前shared pool里面的数据，所以如果要想使shared pool里面现有的SQL重新计算执行计划，我们需要flush shared pool。

这里CPUSPEED就是我们告诉Oracle的CPU的速度:500M/s，即500,000,000个操作/秒
SREADTIM就是single block read time，单位是milliseconds，千分之一秒
MREADTIM就是multi-block read time，单位是milliseconds，千分之一秒
MBRC是typical multiblock read size，单位是block，我们会在讨论tablescan的时候来看这个问题。