Oracle误用append优化案例

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

INSERT INTO /+ append / TF_B_OCS_BATDEAL (...... )

SELECT F_SYS_GETSEQID(EPARCHY_CODE, 'batch_id')

......

FROM TF_F_USER B

WHERE B.REMOVE_TAG = '0' AND USER_ID = :B1 AND B.USER_STATE_CODESET = '0' AND

LENGTH(SERIAL_NUMBER) = '11' AND SERIAL_NUMBER LIKE '1%' AND

NOT EXISTS

(

SELECT 1

FROM TF_F_USER_SHARE_RELA C

WHERE B.USER_ID = C.USER_ID_B AND C.END_DATE > SYSDATE

);

根据下图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插入模式。

参考

参考：老虎刘谈SQL优化