【云和恩墨大讲堂】Oracle线上嘉年华第二讲

编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。

1

嘉宾介绍

小鱼(邓秋爽)

云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting

新系统割接的library cache问题

这是我们在做系统割接的时候的一个案例,可能并不是很常见,这个案例是将Oracle 11g升级到12c的时候遇到的问题,出现了大量的library cache的问题。具体情况是:

新系统割接后,不定时出现大量library cache lock、library cache:mutex X,几分钟后系统自动恢复。

在短暂的时间里,我们来不及做systemdump的,而且出现的频率和时间也是不固定的,很难抓取到当时系统的信息。

先获取故障时段的AWR报告:

我们看到这两个等待事件已经占了整体DB time的72%,大家看到这个问题,可能一般都会想到解析,

我们从以下几个角度分析:

  • TopSQL的解析、执行频率是否合理:

故障点的library lock相关的SQL来看,基本占据db time、parse阈值高的Top SQL解析、执行频率并没有数量级的增加,他们更像是受害者。

  • Oraclebug是否存在:

系统版本Oracle 12.1.0.2,经过原厂排查并不存在相关bug引起。

  • SQL解析是否存在问题,绑定变量使用分析:

查看awr报告硬解析次数很高,但是挖掘sharedpool发现系统中并未发现大幅度未使用绑定变量的SQL。

在oracle 10g的时候,V$SQLAREA视图有一个FORCE_MATCHING_SIGNATURE 参数,可以将SQL经过绑定变量代替后生成一个hashvalue值,通过这个值找到未使用绑定变量的SQL,而开发商的SQL的质量比较高,并未发现核心业务SQL未使用绑定变量的情况。

这样看来,这个问题是很棘手的,硬解析次数很高,但我们找不到对应的SQL在哪里。

我们接着分析,来看AWR报告里面的time model statistic

我们看到红色标记的部分,解析时间消耗了63.74%、解析失败消耗了50.55%。

解析失败是什么?Oracle的解释是这样的:

failed parse:语法、权限等无法执行的SQL解析,也是硬解析,并且解析失败是不能被重用的,当然它也不会存储在V$SQLAREA视图中,所以也挖掘不到这类SQL。

我们如何去发现在系统中解析失败的SQL呢?

Oracle提供了event 10035,会将解析失败的SQL记录到alert 日志里面

从上面的日志可以看到各种解析错误的代码,其中error=942,表示:表不存在,因此判断这是他们做系统变更的时候做过一些表的删除,我们可能在系统割接的时候都会做一些旧表的drop或者rename,这时候一定要严格挖掘应用端的代码,将下线的业务代码停掉,避免错误解析导致数据库出现严重的性能问题。

SQL优化改写技巧

接下来和大家分享执行计划结合业务逻辑的一个等价改写的例子

案例中的SQL如上,大致由两部分组成,上下各是一个标量子查询,然后用union all联合在一起做了一个order by,在结果显示中使用了分页。

我们通过脚本获得该SQL单次逻辑读将近18000000.返回行数为10行,响应时间达到104036MS。

这是个很复杂的SQL,包含标量子查询、表连接、unionall、排序、分页,还有一些复杂的decode、nvl等函数,通过awr报告我们得知该SQL单次执行需要1500多万到1900多万的逻辑读,平均都只返回10行数据,单次执行时间也要100秒左右。

我们可以将SQL简化如下:

select * from (select row_.*, rownum rownum_ from (SELECT ST.* FROM (Select …TO_CHAR(T.SIGNDATE, ‘YYYYMMDDHH24:MI:SS’) AS SIGNDATE …(标量子查 询1)、(标量子查询2)... From MM_MK_CUSTMGR_SIGN T WHERE T.REGION = 14 Union all Select …TO_CHAR(T.SIGNDATE, 'YYYYMMDDHH24:MI:SS') AS SIGNDATE …(标量子查询 1)、(标量子查询2)... From MM_MK_CUSTMGR_SIGN T WHERE T.REGION = 14 AND T.SIGNOUTOID IS NOT NULL) ST ORDER BY SIGNDATE DESC) row_ where rownum <= :1) where rownum_ > :2

对于这种复杂的SQL,我们先看执行计划

这个执行计划我们做过相应删减。

在优化SQL中,我们优先考虑能否优化cost高的步骤,比如大表全表扫描、大表全索引快速扫描、跳跃索引扫描、大表排序等cost消耗; 其次看filter(优化了的nestedloop)、nested loop、hash join、笛卡尔积等表关联步骤cost消耗。

在上面的标量子查询中,Cost消耗最高的在这个view操作,COST消耗达到了14M、rows达到了501K,而这个view是由两部分union all组成的。

在下面的标量子查询中,两部分union all发现上层部分主查询MM_MK_CUSTMGR_SIGN T估算返回501k Rows,下层主查询则只有1Rows数据。

注:在Oracle的估算中是不存在0 Rows的情况,如果评估的结果是0,会算作1.

对于标量子查询,我们简单做个介绍,就是说优化器在这种情况下永远只做一种操作就是filter,这是一种变相优化nest loop。对于这种标量自从查询,我们知道其实SQL之所以出现问题是因为下面的501k导致需要驱动上面那堆复杂的标量子查询,

那么如何优化呢?

常规优化:对于标量子查询,可以使用等价改写为表的外连接方式让其走hash jion的执行计划,但是如果标量子查询中有大表则并不合适,该SQL恰恰包含大表,并不适合用常规的等价外连接的方式来改写。 业务结合执行计划分析:那么这个ORDER BYSIGNDATE DESC排序后rownum能否推进到主查询MM_MK_CUSTMGR_SIGN T表中,rownum限制后再去驱动标量子查询,减少标量子查询的循环次数。

接下来主要针对第二种,结合业务进行分析改写。

在上面的SQL中,是先取501k数据做了驱动,然后再做标量子查询和order by的操作,我们能不能把order by的操作推回到标量子查询前面,这样子的话标量子查询要驱动的只是前面排序取rownum限制条件的数据,我们通过画图的方式来分析一下:

首先是两个同样的表,做了标量子查询的操作,这里的数据是501k,然后标量子查询完了之后,做了order by后rownum的限制,这是原SQL的执行业务逻辑。

我认为应该写成这样,我们想限制标量子查询的循环次数,那我们就先去对主查询取order by排序rownum限制后的数据,再将主查询取出来的这部分数据去驱动标量子查询,做完后再做一次order by rownum的限制。(这里并不会改变SQL的业务逻辑,虽然我们是先排序取rownum限制了,但是标量子查询时主查询是先排序还是后排序取rownum限制对于主查询返回结果集没有任何影响)

根据这种思路,我把SQL改写如下:

SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT ST.* FROM (SELECT …TO_CHAR(T.SIGNDATE, ‘YYYYMMDDHH24:MI:SS’) AS SIGNDATE …(标量子查询1)、(标 量子查询2)... FROM (SELECT * FROM tbcs.MM_MK_CUSTMGR_SIGN x WHERE x.REGION = 14 ORDER BY SIGNDATE DESC offset :2 rows FETCH NEXT :1 rows only) T

UNION ALL SELECT …TO_CHAR(T.SIGNDATE, 'YYYYMMDDHH24:MI:SS') AS SIGNDATE …(标量子查询1)、(标 量子查询2)... FROM (SELECT * FROM tbcs.MM_MK_CUSTMGR_SIGN x WHERE x.REGION = 14 ORDER BY SIGNDATE DESC offset :2 rows FETCH NEXT :1 rows only ) T ) ST ORDER BY SIGNDATE DESC) row_ WHERE rownum <= :1) WHERE rownum_ > :2

其中红色部分是12C的改写方式。是用一个分析函数的方式去做的。

它的执行计划如下:

先访问表MM_MK_CUSTMGR_SIGN排序取rownum限制(前10行数据后),再去驱动那堆复杂的标量子查询,最后再次排序取rownum条件数据,逻辑读从千万级降低到了26661。

这个SQL在改写后,资源消耗降低了许多,基本上能够满足业务的需求。

如果我们再去剖析原SQL代码,发现union all部分是同一个MM_MK_CUSTMGR_SIGN表的查询,下面那个UNION ALL部分查询出来的结果是上面UNION ALL部分的子集。

而跟研发沟通发现实际上union all的下层查询可以去掉,去掉后则该SQL无需改写rownum就可以直接推进到主查询中,从这个例子可以看到不严谨的代码容易造成性能隐患,影响优化器评估最合理的执行计划。

通过以上分享,我们得出:

1、线上系统变更、表下线需要严格挖掘应用端代码,避免因为表结构变更导致SQL解析错误。 2、复杂业务逻辑对应的SQL需要核查,对于不需要的结果和表关联等尽可能去掉,简化表关联数量,合理利用优化器。

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

原文发表时间:2016-12-12

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏鸿的学习笔记

事务处理的数据存储

在上篇文章我们讨论了数据模型,今天试着讨论更基础的数据存储和搜索。数据存储根据开发者使用,可以分为一般的事务处理和数据分析,因为这两者面临的情况不一样。事务处理...

763
来自专栏Android机动车

Socket TCP协议 实时通信的粘包处理之Java与C++实现

详细内容请阅读 http://blog.csdn.net/zhangxinrun/article/details/6721495

1012
来自专栏数据和云

性能分析:Oracle的CLOB使用与临时段访问及其性能优化

编辑手记:在系统测试、上线和优化的过程中,抓住核心环节、不放过任何可疑,这是DBA的基本要求之一,在这个案例中,高频度调用的存储过程引起了注意。 客户新上线的一...

2955
来自专栏WeTest质量开放平台团队的专栏

Unity3d底层数据传递分析

这篇文章主要分析了在Mono框架下,非托管堆、运行时、托管堆如何关联,以及通过哪些方式调用。内存方面,介绍了什么是封送,以及类和结构体的关系和区别。

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

MySQL中的反连接(r12笔记第45天)

关于Oracle的半连接,反连接,我一直认为这是一个能讲很长时间的话题,所以在我的新书《Oracle DBA工作笔记》中讲性能优化的时候,我花了不少的笔墨做...

2575
来自专栏PingCAP的专栏

三篇文章了解 TiDB 技术内幕:说计算

上一篇文章介绍了 TiDB 如何存储数据,也就是 TiKV 的一些基本概念。本篇将介绍 TiDB 如何利用底层的 KV 存储,将关系模型映射为 Key-Valu...

5721
来自专栏Golang语言社区

聊一聊goroutine stack

推送在外卖订餐中扮演着重要的角色,为商家实时接单、骑手实时派单提供基础的数据通道。早期推送是由第三方服务商提供的, 随着业务复杂度的提升、订单量和用户数的持续增...

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

缓慢的update语句性能分析(r6笔记第61天)

最近处理一个问题的时候,先是收到DB time升高的报警,然后查看DB time的情况发现,已经有近1000%的负载了。 ? 带着好奇心想看看到底是什么样的...

2645
来自专栏从零开始学 Web 前端

嵌入式面试题(一)

4. 空指针(null pointer)指向了内存的什么地方(空指针的内部实现)?

702
来自专栏数据和云

专家出诊:SQL Server 高CPU系列之索引诊断

作者题记:CPU高使用率往往会导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死,可以说CPU高使用率是数据库这种后台进程服务的第一大杀手。引发C...

3664

扫码关注云+社区