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

相关文章

来自专栏分布式系统和大数据处理

Hive中分区和分桶的概念和操作

在使用传统的RDBMS数据库(关系数据库),例如MySql时,对于一些大表,我们通常会进行分表操作,以提升查询效率。在Hive中也提供了类似的概念和操作,本文将...

1101
来自专栏沃趣科技

数据库对象事件与属性统计 | performance_schema全方位介绍

上一篇《事件统计 | performance_schema全方位介绍》详细介绍了performance_schema的事件统计表,但这些统计数据粒度太粗,仅仅按...

1904
来自专栏数据和云

警惕:Oracle中删除的分区不会进入回收站(Recyclebin)

在Oracle数据库中,单个删除的分区并不会进入回收站,全表删除的分区才可能和全表一起放入回收站。这是因为单个分区删除之后,是无法通过简单的闪回加入原分区表中,...

3398
来自专栏数据和云

如何编写更好的SQL查询:终极指南(上)

结构化查询语言(SQL)是数据挖掘分析行业不可或缺的一项技能,总的来说,学习这个技能是比较容易的。对于SQL来说,编写查询语句只是第一步,确保查询语句高效并且适...

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

探究AWR 第一篇

statspack相比awr算是比较通用,而且免费,可以在标准版,企业版中使用,awr是新企业版本中才有的,算是statspack的一个升级版,而且代码不公开。...

3447
来自专栏乐沙弥的世界

Oracle 数据字典和数据字典视图

是元数据的集合,从逻辑上和物理上描述了数据库及内容,存储于SYSTEM与SYSAUX表空间内的若干段。

603
来自专栏沃趣科技

删用户删表空间的操作还能flashback回来吗?

有一次在某微信群里,有人提问以下两条操作还能恢复吗?而且是在没有开归档。紧接着又有人提问数据库是否开了闪回?

1092
来自专栏程序猿

Oracle_12C的新特性

这里我们来领略下Tom眼中的12个特性增强: ? #1 Even better PL/SQL from SQL, 直接在SQL中嵌入PL/SQL对象并运行,猜测...

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

alert日志中的一条ora警告信息的分析(59天)

今天照例检查数据库alert日志,发现一个错误。但是也没在意,想可能有大的操作导致的,马上会释放空间的,但是转眼一想,这是生产库,而且现在时早上,泰国的运营商还...

2555
来自专栏乐沙弥的世界

Oracle db_file_mulitblock_read_count参数

     Oracle DB_FILE_MULTIBLOCK_READ_COUNT是Oracle比较重要的一个全局性参数,可以影响系统级别及sessioin级别...

793

扫码关注云+社区