Oracle误用append优化案例

0    70    1

👉 本文共约1140个字,系统预计阅读时间或需5分钟。

这是某客户关键系统的一个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万次左右。

img
为什么cpu时间消耗很少,大部分等待时间是application等待?

sqlhc里面也有交待:

img

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

img

这个表锁是如何生成的?

罪魁祸首就是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

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

insert /+ parallel(4) / into xxx select

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

参考

参考:老虎刘谈SQL优化

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

5 × 4 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部