采用MERGE 语句的非关联形式提升SQL性能

0    100    1

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

今天照例巡查垃圾sql时,发现一个跑了很长时间的sql,且其执行计划也非常的大,这个sql非常可疑,得排查排查:

img

第一步,照例查询内存中的执行计划:

SELECT *** FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('17t226txddfy5',0,'advanced'));**

img

img

可以看出执行计划的cost花费非常的大,且Predicate Information(即谓语)部分全是filter过滤的,怎么会没有access访问呢???????拿出其具体的sql语句:

MERGE INTO FLN_RCW_CM_ENGINE_BLACK_PURIFI BLACK_PURIFI

USING (SELECT *****

FROM (SELECT T.*,

ROW_NUMBER() OVER(PARTITION BY CERTIFICATION_NO_18, INFO_CHARACTER, FILTER_CONFIG_ID ORDER BY CERTIFICATION_NO_18) NUM

FROM FLN_RCW_CM_T_ENGINE_BLACK T) T

WHERE NUM = 1) BLACK

ON (BLACK_PURIFI.CERTIFICATION_NO_18 = BLACK.CERTIFICATION_NO_18 AND BLACK_PURIFI.INFO_CHARACTER = BLACK.INFO_CHARACTER AND (BLACK.FILTER_CONFIG_ID IS NULL OR BLACK_PURIFI.FILTER_CONFIG_ID = BLACK.FILTER_CONFIG_ID) AND BLACK_PURIFI.DATE_DELETE IS NULL)

WHEN MATCHED THEN

UPDATE

SET BATCH_DATE = :B1,

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

FLAG_DELETE = 'N',

PURIFICATION_DATE = :B1

WHEN NOT MATCHED THEN

INSERT

VALUES

(:B1,

BLACK.CERTIFICATION_NO_18,

BLACK.FILTER_GRADE,

BLACK.FILTER_GRADE_CONTENT,

BLACK.INFO_CHARACTER,

BLACK.INFO_CHARACTER_CONTENT,

'Y',

TO_DATE(:B1,

'yyyy-mm-dd'),

'N',

NULL,

SYSDATE,

SYSDATE,

:B1,

BLACK.FILTER_CONFIG_ID)

;

我们看看其数据量吧,我之前提出过一个观念:任何离开数据量来谈sql优化都是没有意义的:

select *** from vw_table_lhr v where v.TABLE_NAME in ('FLN_RCW_CM_ENGINE_BLACK_PURIFI','FLN_RCW_CM_T_ENGINE_BLACK');**

img

可以看出,统计信息是最近收集的,一张是小表,一张是大表,根据sql语句可以看出该sql是属于典型的用小表来更新大表,根据原来的执行计划看出走的是NL连接,且内表是全表扫描,这样的话内表大约要要访问94W+次,每次都对10G的大表来全表扫描的,大家知道,NL连接至少要保证内表的关联列有个索引,外表返回的结果集非常的小这个时候效率才非常的高,但是这里呢????神马都不满足,,,,,你还敢走NL??????脑残,,,,,所以这个执行计划很恐怖的,当然也是错误的执行计划,这里应该走hash连接,或者利用一下索引神马的,当然,这里如果只是单纯的update语句的话,我们可以采用快速游标更新法,效率是非常好的,但是这里同时涉及到update和insert语句,根据经验这个应该修改为merge语句的非关联形式的表对表的更新,所以优化后的sql如下:

优化后的sql:

MERGE INTO RISKREPT.FLN_RCW_CM_ENGINE_BLACK_PURIFI BLACK_PURIFI

USING (SELECT BLACK_PURIFI.ROWID ROWIDS,

BLACK.*****

FROM (SELECT *****

FROM (SELECT T.*,

ROW_NUMBER() OVER(PARTITION BY CERTIFICATION_NO_18, INFO_CHARACTER, FILTER_CONFIG_ID ORDER BY CERTIFICATION_NO_18) NUM

FROM FLN_RCW_CM_T_ENGINE_BLACK T) T

WHERE NUM = 1) BLACK,

RISKREPT.FLN_RCW_CM_ENGINE_BLACK_PURIFI BLACK_PURIFI

WHERE BLACK_PURIFI.CERTIFICATION_NO_18 = BLACK.CERTIFICATION_NO_18

AND BLACK_PURIFI.INFO_CHARACTER = BLACK.INFO_CHARACTER

AND (BLACK.FILTER_CONFIG_ID IS NULL OR

BLACK_PURIFI.FILTER_CONFIG_ID = BLACK.FILTER_CONFIG_ID)

AND BLACK_PURIFI.DATE_DELETE IS NULL) T

ON (BLACK_PURIFI.ROWID = T.ROWIDS)

WHEN MATCHED THEN

UPDATE

SET BATCH_DATE = :B1,

FLAG_DELETE = 'N',

PURIFICATION_DATE = :B1

WHEN NOT MATCHED THEN

INSERT

VALUES

(:B1,

T.CERTIFICATION_NO_18,

T.FILTER_GRADE,

T.FILTER_GRADE_CONTENT,

T.INFO_CHARACTER,

T.INFO_CHARACTER_CONTENT,

'Y',

TO_DATE(:B1,

'yyyy-mm-dd'),

'N',

NULL,

SYSDATE,

SYSDATE,

:B1,

T.FILTER_CONFIG_ID)

;

优化后的执行计划:

img

从执行计划来看,对这个sql的简单优化进行的差不多了。

注:这篇文章是几天前就写好的,但是博客审核有点问题,今天重新发布一下,就在发布之前我看了一下该语句仍然在跑,也就是已经运行了大概4天的时间还没有跑完,,,,,时间就这么过去了,,,,,,所以对系统垃圾sql语句的监控仍然是比较重要的。

关于这个sql,我们的同事终于受不了这个sql了,找我来优化,我说这个sql我之前就检测到了的,,所以已经优化好了的,你直接拿过去跑吧,【这里说明一下:由于是job,boss不让停,那没办法,只能等业务人员自己来找我了】由于最近有点忙就没有关注这个sql语句了,今天终于闲暇下来了,想起了这个sql 了,去看看优化后跑了多久了呢???

img

由图可以看出,该sql最终跑了5天18个小时后被终止了,也就是被手动杀掉会话了,之后修改为我优化后的sql,运行时间为5分到13分钟左右,由5天的时间缩短到了十几分钟,这个就是优化呀,,,,,,时间呀,,,,,,由单车到飞船的速度。。。。。

标签:

头像

小麦苗

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

您可能还喜欢...

发表回复

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

4 × 5 =

 

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

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

  • 回到顶部
返回顶部