SQL之美- 通过SQL MONITOR解读并优化SQL

编辑手记:SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。

本系列经典文章

之一:标量子查询优化

之二:OR展开与子查询优化案例详解。

之三:IN子查询返回结果集异常

今天是系列第四讲:通过SQL MONITOR来优化SQL

作者简介:

黄廷忠(网名:认真就输)

云和恩墨技术专家

个人博客:http://www.htz.pw/

注意:SQL MONITOR是ORACLE 11G新增加的功能,在12C中此功能得到了增强。

下面这个案例,是在出账的时候,开发工程师收到通知账号被锁了,需要手动KILL进程,才能保证出账进程能顺利完成。

首先我们查询数据库中锁的信息,看到1752阻塞者是ACTIVE的,正在执行3wscxx88myd7t这个SQL。

查询1752会话的状态,可以发现,1752会话执行ID为 3wscxx88myd7t 的SQL已经很长一段时间了。我们通过sql monitor来查看执行计划。这里需要特别注意红色方框里面的内容。

SQL执行的开始时间在15:48分,已经执行4760S还没有执行完,通过MODULE PROGRAM我们知道这个是前台打印发票,这个SQL正常情况下,应该在1S内出结果,执行这么久,明显是异常的。

下面继续查看SQL MONITOR的信息。首先来看绑定变量和全局的一些信息。

执行计划如下:

我们重点来看红色方框标出的内容。

在红色标记部分,可以看到最后一个VIEW是NL被驱动表,视图内部是通过HASH JOIN连接返回0行结果,整个问题就出现在VIEW这里。

下面来查看一个VIEW这部分的代码

(SELECT/*+use_nl(balance_source,a,A_CACHED_BALANCE)*/ NVL(SUM(AA.AMOUNT), NULL) PAYOUT_BALANCE FROM (SELECT AMOUNT, ACCT_BALANCE_ID FROM BALANCE_PAYOUT WHERE OPER_DATE > TO_DATE('20160102144414', 'YYYYMMDDHH24MISS') AND STATE = '00A') AA, A_CACHED_BALANCE WHERE A_CACHED_BALANCE.ACCT_BALANCE_ID= AA.ACCT_BALANCE_ID ANDA_CACHED_BALANCE.ACCT_ID = 114680344) D,

这里开发使用NL提示,出发点是好的,但是执行计划出来是走的HASH JOIN

下面手动添加提示

(SELECT /*+ index(A_CACHED_BALANCE,IDX_A_CACHED_BALANCE_ACCT_ID_1)use_nl(AA,A_CACHED_BALANCE)*/ NVL(SUM(AA.AMOUNT), NULL)PAYOUT_BALANCE FROM (SELECT AMOUNT,ACCT_BALANCE_ID FROM BALANCE_PAYOUT WHERE OPER_DATE > TO_DATE('20160102152005', 'YYYYMMDDHH24MISS') AND STATE = '00A')AA, A_CACHED_BALANCE WHEREA_CACHED_BALANCE.ACCT_BALANCE_ID = AA.ACCT_BALANCE_ID ANDA_CACHED_BALANCE.ACCT_ID = 144023766) D,

添加提示后的执行计划信息如下:

这里看到,SQL已经按我们预期的提示走执行计划了。

下面看看SQL执行的性能。

逻辑读下降到57 ,执行时间为Elapsed:00:00:00.06

The end

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-06-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库

关于女神SQLite的疑惑(2)

还是女神SQLite的话题,继续讨论有关她的种种常见疑惑。 1.问:女神SQLite是线程安全的吗? 1.答:SQLite是线程安全的,这点确凿无疑。但我要补充...

1978
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第二篇(r2第38天)

在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时,得到的sql monitor报告如下: Global Informat...

2717
来自专栏数据和云

深入内核:CBO对于Cost值相同索引的选择

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

3415
来自专栏Spark学习技巧

浪尖,请问如何确定hive分桶数?

顺便打个广告,更多优质文章和问题答疑及视频教程请点击原文链接,加入浪尖知识星球-Spark技术学院获取。

1705
来自专栏杨建荣的学习笔记

每秒执行6000的简单SQL优化(二) (r10笔记第65天)

继续前几天的一次性能调优,这次调优难度不小,而且空间很小,看起来简直就是绝处逢生的感觉。下面的两条SQL语句执行频率极高,每秒达到6000次,希望能够优化。 s...

2838
来自专栏数据和云

极限优化:从75到2000,由技能到性能提升岂止20倍

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

2374
来自专栏杨建荣的学习笔记

Oracle表中含有255列以上时需要注意的(r12笔记第77天)

今天看JL(Jonathan Lewis)的一篇文章,真是费了不少的脑细胞,玩Oracle几十年的老司机,看问题的角度和深度果然不一样,当时看他的大作《O...

37010
来自专栏数据和云

【安全警告】Oracle 12c 多租户的SQL注入高危风险防范

在使用Oracle多租户选件时,由于Container容器和PDB融合共存,则权限控制必将更加重要,在之前的文章中我们提到,Oracle 12.2 的 loc...

3466
来自专栏杨建荣的学习笔记

MySQL中的Online DDL(第一篇)(r11笔记第3天)

记得有一天快下班的时候,一位开发同事找到我说,需要对一个表做变更,数据量据说有上千万,而当时是使用的MySQL版本是5.5,这可如何是好,对于在线业务要求高的情...

3379
来自专栏数据和云

一个不懂业务的DBA不是好的DBA

编辑手记:懂业务,懂系统逻辑,你才能做一个更好的DBA。 在数据库巡检中发现一个MES生产信息数据库中一个存储过程中一条SQL单次逻辑读为2100,且执行很频繁...

2856

扫码关注云+社区