Archive for November, 2010
Oracle hash join
Sunday, November 28th, 2010
hash join是oracle里面一个非常强悍的功能，当做hash join时，oracle会选择一个表作为驱动表，先根据过滤条件排除不必要的数据，然后将结果集做成hash表，放入进程的hash area，接着扫描第二张表，将行的键值做hash运算，到内存的hash表里面去探测，如果探测成功，就返回数据，否则这行就丢弃掉这个是最基本的解释，实际情况中，考虑到单个进程PGA的大小，oracle不会让进程任意的消耗OS内存，hash area是有一定限制的，所以在oracle中，hash也有三种模式：
optimal，onepass，multipass
optimal:当驱动结果集生成的hash表全部可以放入PGA的hash area时，称为optimal，大致过程如下：
1.先根据驱动表，得到驱动结果集
2.在hash area生成hash bulket，并将若干bulket分成一组，成为一个partition，还会生成一个bitmap的列表，每个bulket在上面占一位
3.对结果集的join键做hash运算，将数据分散到相应partition的bulket中，当运算完成后，如果键值唯一性较高的话，bulket里的数据会比较均匀，也有可能有的桶里面数据会是空的，这样bitmap上对应的标志位就是0，有数据的桶，标志位会是1
4.开始扫描第二张表，对jion键做hash运算，确定应该到某个partition的某个bulket去探测，探测之前，会看这个bulket的bitmap是否会1，如果为0，表示没数据，这行就直接丢弃掉
5.如果bitmap为1，则在桶内做精确匹配，判断OK后，返回数据
这个是最优的hash join，他的成本基本是两张表的full table scan，在加微量的hash运算
onepass
如果进程的pga很小，或者驱动表结果集很大，超过了hash area的大小，会怎么办？当然会用到临时表空间，此时oracle的处理方式稍微复杂点需奥注意上面提到的有个partition的概念，可以这么理解，数据是经过两次hash运算的，先确定你的partition，再确定你的bulket，假设hash area小于整个hash table，但至少大于一个partition的size，这个时候走的就是onepass
当我们生成好hash表后，状况是部分partition留在内存中，其他的partition留在磁盘临时表空间中，当然也有可能某个partition一半在内存，一半在磁盘，剩下的步骤大致如下：
1.扫描第二张表，对join键做hash运算，确定好对应的partition和bulket
2.查看bitmap，确定bulket是否有数据，没有则直接丢弃
3.如果有数据，并且这个partition是在内存中的，就进入对应的桶去精确匹配，能匹配上，就返回这行数据，否则丢弃
4.如果partition是在磁盘上的，则将这行数据放入磁盘中暂存起来，保存的形式也是partition，bulket的方式
5.当第二张表被扫描完后，剩下的是驱动表和探测表生成的一大堆partition，保留在磁盘上
6.由于两边的数据都按照相同的hash算法做了partition和bulket，现在只要成对的比较两边partition数据即可，并且在比较的时候，oracle也做了优化处理，没有严格的驱动与被驱动关系，他会在partition对中选较小的一个作为驱动来进行，直到磁盘上所有的partition对都join完
可以发现，相比optimal，他多出的成本是对于无法放入内存的partition，重新读取了一次，所以称为onepass，只要你的内存保证能装下一个partition，oracle都会腾挪空间，每个磁盘partition做到onepass
multipass
这是最复杂，最糟糕的hash join，此时hash area小到连一个partition也容纳不下，当扫描好驱动表后，可能只有半个partition留在hash area中，另半个加其他的partition全在磁盘上,剩下的步骤和onepass比价类似，不同的是针对partition的处理
由于驱动表只有半个partition在内存中，探测表对应的partition数据做探测时，如果匹配不上，这行还不能直接丢弃，需要继续保留到磁盘，和驱动表剩下的半个partition再做join，这里举例的是内存可以装下半个partition，如果装的更少的话，反复join的次数将更多，当发生multipass时，partition物理读的次数会显著增加
oracle数据库的CPU/IO信息采集
Thursday, November 4th, 2010
CPU时间采集
从10G开始，oracle引入了时间模型，我们可以从oracle的角度来看CPU的使用程度
先说说几个概念
db time:oracle数据库消耗的时间，这个范围比较大，包括了CPU使用，等待IO子系统返回，网络处理等
db cpu：指oracle单纯消耗CPU，做CPU运算的时间，关于IO,网络的等待都不在这个范围内，用它来统计真实CPU的消耗比较准确
CPU TIME:这个是我取的名字，表示CPU能给你提供的最大时间，比如你有4个cpu/core，那么1小时内，CPU TIME就是4X60分钟
我通过一个测试脚本来看看oracle里面关于CPU时间的统计
#killcpu.sh
#!/bin/sh
export ORACLE_SID=innodb
export ORACLE_HOME=/opt/oracle/products/11.2.0
$ORACLE_HOME/bin/sqlplus /nolog <<_kof_
connect iops/iops@innodb
declare v_count pls_integer := 0;
begin
for x in 1..400000000 loop
for d in 1..400000000 loop
for c in 1..400000000 loop
v_count :=mod(c, mod(x,d));
end loop;
end loop;
end loop;
end;
/
_kof_
测试机器是Intel(R) Xeon(R) CPU E7530的4C6核 CPU，虚拟出来总共是48个core
killcpu采用24并发，48并发，60并发做测试，每个10分钟，通过statspack收集信息，按照上面的公式，我们的cpu time=10min*60 * 48core=28800秒
最终结果如下(时间单位都是秒)：
并发数
db time
db cpu
cpu time
top显示CPU占用率
LOAD
24
14066
14064
28800
50
24
48
27984
27401
28800
100
48
60
34556
27179
28800
100
57
从结果可以看出：
24并发，oracle使用了24个core，整体的CPU占用率在50%，load在24，非常准确，此时db time基本和db cpu一致，因为你干的所有事情，都是在CPU上
48并发，oracle使用了48个core，CPU使用率达到100%，oracle在多cpu环境下对资源的利用确实很高，此时db time，db cpu,cpu time一致了，CPU在满负荷运转
60并发，这个时候会发现，db cpu没变，因为CPU已经耗尽，没得涨了，db time现在已经远大于cpu [...]
