OS与Oracle

Kaya 发表于 os2ora.com 

本文要分析的是下面这个SQL，执行了半个多钟头还没返回结果。

INSERT /*+ APPEND */
INTO
 T_D
SELECT
 *
FROM
 T_A a
,T_B b
,T_C c
WHERE a.id = b.id
AND   b.number = c.number
;

Wait events 是了解Oracle运行状态的一个重要途径。对于某个具体的SQL，SQL Monitor Report提供了drill down的方式得到这个具体SQL在运行中的wait events的分布情况，下面是SQL Monitor Report的相应图形。

这里最突出的等待事件是enq: TS – contention，这是关于临时segment的等待事件，这可能是一般的表空间的争用(例如并行直接路径加载数据)，也可能是临时表空间的争用(例如为了hash join或者sort)。那么这时临时表空间的增长状态是怎么样子的呢？SQL Monitor Report提供了这方面的信息。

可以看出，temp space从开始的3G慢慢增长到20G，整个过程2800 seconds，如果算下速度的话17GB/2800=6MB/s。这刚好与上面的等待事件 enq: TS – contention相吻合。

这是一个直接加载数据的例子，在这个例子中，最后表的大小是30GB左右。可以怀疑，这些临时表空间的分配是为了存放最后放入目标表的数据。

这于这个等待事件enq: TS – contention发生在这个SQL执行过程中的哪一步呢？SQL Monitor Report也给出了答案，在执行计划那一页：

注意上图鼠标位置，标志着位于HASH JOIN BUFFERED这个步骤的等待事件enq: TS – contention占了这个SQL所有等待活动的97%！

问题到这里已经基本明了了：

1. 优化器采用Hash Join Buffered的方式返回三个表Join的结果，当为这些结果集分配临时表的空间时，碰到了严重的竞争。

2. 由于分配速度极其缓慢，导致了整个SQL超过97%的时间花在了这个等待事件上，通过去掉这个等待事件，这个SQL应该能提升上百倍的速度。

解决这个问题的根本在于加速临时表空间的回收速度，不过，也有workaround的办法，那就是预先分配足够的临时表空间，避免回收临时表空间时出现TS – contention竞争。

另外一种思路，则在于减少对临时表空间的利用，在这里，为什么要用到这么多临时表空间在于Oracle采用Hash Join Buffered而不是采用Hash Join，这意味着Oracle会先对Join的结果集进行buffer，等到所有结果ready之后再写到目标表，另一种思路当然是让结果直接写到目标表中，也就是实现并行的DML插入操作。方法很简单：在运行这个SQL之前加上

ALTER session enable parallel dml;

即可，在这种情形下，Oracle不需要再为结果集分配临时表空间，同时，由于采用并于DML操作，整个SQL的运行时间会得到很大的提速。下面是相应的执行计划。

可以看出，Hash Join Buffered变成了Hash Join，同时，等待事件enq: TS contention已然消失，换成了”喜闻乐见”的ON CPU。同时，整个SQL在小于30s的时间内完成了。

不可否认，Hash Join Buffered是这里极其隐蔽，可能不小心就被忽略了，以为上面两个执行计划就是LOAD AS SELECT的位置从第2行搬到了第4行，其实背后发生的事情远没有这么简单。以后有机会再对这个话题进行展开吧。