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 条评论
登录 后参与评论

相关文章

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

一个oracle查询引起的bug (r4笔记第59天)

任何软件都不是完美的,oracle也是如此,隔一段时间就会收到oracle的邮件说建议打哪些安全补丁什么的。新发布的产品都是release 1,比如10gR1...

3476
来自专栏技术小黑屋

Dump Table Structure in SQLite3

Best answer to the question Use PRAGMA table_info

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

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

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

3339
来自专栏数据之美

常用统计分析 SQL 在 AWK 中的实现

最近有需求需要本地处理一些临时的数据,用做统计分析。如果单纯的 MYSQL 也能实现, 不过一堆临时数据这样从 mysql 导来导去还是挺麻烦的,比较理想的选...

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

海量数据切分抽取的实践场景(r11笔记第43天)

如果一个大表要抽取数据导出成csv文件,我们有什么策略,如何改进。 一、问题背景 今天开发的同学找到我,他们需要做一个数据统计分析,需要我提供一些支持,把一...

3286
来自专栏CDA数据分析师

【干货】找不到适合自己的编程书?我自己动手写了一个热门编程书搜索网站(附PDF书单)

原作者 Vlad Wetzel 编译 CDA 编译团队 本文为 CDA 数据分析师原创作品,转载需授权 选择适合自己的编程书绝非易事,美国的程序员小哥根据国外著...

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

关于查看dba_data_files的一个小问题(r7笔记第72天)

今天帮一个朋友看一个pl/sql的问题,他已经钻到一个死胡同里列,可能明眼人一看就知道哪里有问题,但是当局者迷,所以我抽空看了一下这个pl/sql块。 pl/s...

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

特殊的物化视图刷新 (r4笔记第77天)

现在有一个需求,某个环境中存在两个用户,一个用户中存在物化视图,另一个用户中存在源表,根据业务的需要,需要做一种特别的物化视图刷新。 ? 物化视图用户中的物化...

3307
来自专栏数据和云

【快讯】在线体验Oracle Database 12.2 SQL新特性

Oracle Database 12.2 已经让广大粉丝望眼欲穿,虽然文档已然发布,但是实验无从做起。 现在,可以通过 Oracle Live SQL 站点(文...

3225
来自专栏深度学习

利用RNN和LSTM生成小说题记

一、选取素材 本文选取的小说素材来自17k小说网的一篇小说《两只橙与遠太郎》,手工复制小说中的题记。 小说网址:http://www.17k.com/list/...

4657

扫描关注云+社区