SQL优化误用'append'案例一则

编辑手记：SQL是数据库系统的核心，因SQL问题引发的系统蝴蝶效应屡见不鲜，今天继续学习SQL优化的技巧。。

这是某客户关键系统的一个TOP SQL：

根据下图sqlhc获取的信息，该SQL平均每次插入不到一条记录，每半小时执行10万次左右。

为什么cpu时间消耗很少，大部分等待时间是application等待？

sqlhc里面也有交待：

这些wait class是application的具体等待事件是enq:TM - contention，也就是表锁。在AWR报告的TOP 10事件中，也出现了这个事件，而且占DB time的4.6%，可见一个SQL就对系统造成了较大的影响：

这个表锁是如何生成的？罪魁祸首就是SQL中使用的append Hint。

append的Hint一般使用在insert select语句，插入大量结果集的时候，采用直接路径（direct path）在表的高水位线以上直接写入数据。在没有commit之前，sql会一直持有表锁。这个Hint在数据仓库的SQL中使用较多，一次插入记录几十万以上，执行频率低。

但是，在本例OLTP系统中，频繁执行而且插入少量记录的SQL也使用了append的hint，造成的后果就是：

1、sql执行效率低，大量的表锁等待，并发越多等待越严重。

2、插入的表TF_B_OCS_BATDEAL，有大量的空间被浪费，每插入一条记录都会占用一个block。而且即使有大量记录被delete，这些高水位线以下的空闲块不会被重新使用。

解决方法

这种频繁执行，每次插入少量记录的情况，不能使用append，必须马上去掉这个hint。

补充： 并行DML开启时，默认启用append插入模式。

insert /*+ parallel(4) */ into xxx select ....

这个SQL默认并没有开启并行DML，只是后面的select部分使用了并行，这种情况没有使用append。

alter session enable parallel dml; --启用并行DML

insert /*+ parallel(4) */ into xxx select

这时就启用了并行DML，不加append的hint，默认也是append插入模式。