本文要分析的是下面这个SQL,执行了半个多钟头还没返回结果。
INSERT 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行,其实背后发生的事情远没有这么简单。以后有机会再对这个话题进行展开吧。