SQL性能突然降低引起的业务办理缓慢案例一则
钟时荣
云和恩墨技术专家
大家好，我是云和恩墨的技术支持工程师钟时荣，服务的行业主要分布在通信、能源、政府、医疗和金融。作为一位冲在一线的现场 DBA 而言，回望过去，往往有种“往事不堪回首“的感觉，还没有闭上眼曾经处理各种问题的景象就已经浮现在眼前。
作为一名一线 DBA工程师，处理各类应急问题是常事，面对突入而来的紧急技术支持请求，我们该怎么去处理呢？接下来为大家分享一个紧急技术支持的案例，处理手法极其简单、粗暴，但是回味整个过程又不乏乐趣。理论为道、实战为器，分享的主题为《SQL 性能突然降低引起的业务办理缓慢案例一则》。
云和恩墨是一个滋养 DBA 的好地方，在这里有着 ORACLE DBA 发展所需的沃土和广袤的空间。
在正式分享 ORACLE 技术案例前，先给大家分享一点自己和 ORACLE 的一些事:
1）说到 ORACLE，刚接触 ORACLE 的时候我经常这样问自己：
ORACLE 是什么
ORACLE 怎样工作
我为什么要用 ORACLE 来开始和延续自己的职业生涯
ORACLE 在什么时候使用
ORACLE 在什么场景使用
ORACLE 通常在哪些客户中使用
我作为一名 DBA 的价值是什么
2）当成为 DBA 有一段时间后，我又会经常问自己这样的一些的问题：
我是一名什么类型的 DBA
我平时怎么学习和使用 ORACLE
我学习和使用 ORACLE 到了什么程度
在 ORACLE 领域中我擅长什么、不擅长什么
我所拥有的 ORACLE 技能在什么时候、什么场景能为哪些客户提供服务
我对 ORACLE 的付出和收获能让自己的 ORACLE 职业走多远，走多广以及走多深
除了做 ORACLE DBA，我的价值还有哪些
相信我问自己的问题，有些也是大家会问自己的问题，在此引用盖总曾经分享的一张 ORACLE 学习导图和大家一起在 ORACLE 的前进道路上共勉。
有人问 ORACLE 技术中的什么最有技术难度？
安装、升级和迁移
备份和恢复
故障诊断
性能调优
数据安全
基础运维管理
SQL、PL/SQL 开发
围绕 ORACLE 的综合应用设计
其它
因为工作的阅历差异，相信每个 DBA 心中都有自己心中的一把尺，今天我要给大家分享的内容就是涉及到故障诊断和性能调优的一个案例。
本案例是一则由于 SQL 执行性能突然下降而引起的业务办理缓慢的问题处理分
享。
1.1. 问题处理流程
俗话说“工欲善其事、必先利其器”，在正式分享该案例的诊断过程前，先给大家分享一个我工作中常用的问题处理流程，有一个总的指导方针，方能让自己在很多问题处理的环境中不会处于太被动的局面：
我作为工程师的角色时，只需要主要关心工程师这个角色的任务即可，而在工程师角色提供技术支持服务的过程中比较容易出现三个方面的主要问题：
1. 解决方案出现问题，导致问题不能被解决或者问题更加严重。
2. 没有问题升级的机制，当发生的问题不是自己解决范围内而问题又对业务系统产生严重影响时，应该给自己创建一条问题升级的机制的路线，比如5分钟内进行问题解决可能性的初判，10分钟未能真正解决的便向项目经理或者其它一切可以协调的资源进行问题升级，当然这个时间范围是根据你真实的业务环境来定的。
3. 没有技术支持文档的沉淀。很多时候我们提供了技术支持帮客户解决问题后就不再做其它工作了，为了养成一个良好的习惯，还是建议在技术支持完成后对所支持的内容进行一个简要或者详细的文档沉淀，以备不时之需。
1.2. 问题现象
客户通过电话的方式反馈一个基于 ORACLE 数据库的系统业务运行缓慢，影响的业务范围暂时未确定。
1.3. 问题诊断
由于该问题发生在一套关键业务系统上，快速的解决问题是第一目标，通过远程连接方式连接到存在问题的数据库主机上进行问题的诊断，情况如下：
1.4. 问题处理
经和客户商量后，通过手工杀会话的方式来进行紧急的问题处理，当手工杀掉产生 direct path read temp 这2个会话后，该业务程序自动重新连接到了数据库，之前业务办理缓慢的问题消失，业务运行恢复正常，至此问题得以快速解决。
整个问题处理过程看似简单粗暴，但是其中却蕴含了 ORACLE 的基础知识掌握的熟练度以及实战经验的丰富度。
1.5. 问题回顾
该案例从客户反馈问题到快速的解决问题，全程共计花费10分钟左右，解决问题的过程也看似比较简单，过程大概为确定问题现象、推测问题原因、尝试快速的解决问题、解决问题。
在生产系统的运维工作中，可以将问题处理概括为一句话‘将问题消灭在萌芽状态、并快速的消除问题’，这个和我们平时学习 ORACLE 知识的时候处理态度不一样。
ORACLE 学习过程中，我们注重的是将原理研究清楚。
DBA 应急响应时，我们注重的是快速解决问题。
在日常的 ORACLE 学习过程中，我们通过学习理论知识和做相应的实验来提升自己的 ORACLE 基础知识能力，而在实际的生产环境操作中就在不断的积累实际经验。这个过程就像练武的人先完成内、外双修，最终再走向江湖。
在此问题的处理上，某种程度上可以说是是幸运的，幸运的是问题点就出在数据库内，幸运的是熟悉 ORACLE 性能方面的知识，幸运的是对该业务类型有一定的实际维护经验，故根据目前数据库里面的会话现象就快速的找到了引起问题的原因，并将问题进行了快速的消除。
生产环境往往是比较复杂、具有强关联关系的，如下图：
在现在的 DBA 时代，对我们 DBA 的要求也越来越高，不仅是需要我们有擅长领域的深度，还需要我们有整个IT架构的广度。故 DBA 拥有一套应急响应流程显得格外的重要，如下是一个 DBA 应急响应的简要任务表：
1.6. 问题详解
当我们快速的处理完问题，业务也恢复了正常运行后，那么问题是不是就算彻底解决了呢？客户是否完全满意快速的问题解决方案呢？答案不一定都是 YES。
以刚刚分享的案例为例子，从问题的解决完整性上来看，刚刚只是临时快速的填平了一个坑，但是由于未真正找到出现坑的原因，故在前方则可能还存在未爆炸的雷，只要雷未彻底的排除掉，则其就存在还会因为相同原因而引爆的隐患。
此案例客户会问如下问题：
引起该问题的根本原因是什么
为什么性能突然就下降了呢
彻底解决该问题的方法是什么
接下来我们对该问题中的一些关键点进行进一步的详细分析：
1）问题的准确定位
在定位问题的过程中，往往有如下典型的情况：
问题定位明显，环境中能有诸如问题反馈精确、资源利用异常、I/O 错误、网络错误或大量的同一业务(SQL_ID)异常等能明显定位问题的表象。
问题定位模糊，不能明确的判定出出现的问题是因还是果，只能依据现有的一些信息进行推测。
在该案例中，虽然数据库主机的资源利用率正常、主机和数据库日志无异常报错以及数据库中无大量的异常消耗资源的等待事件，但是通过少量的可能存在问题的SQL和业务侧的问题现象匹配便精确的定位出了问题点的所在，为后续的问题快速处理赢得了很多的时间。
2）数据库侧问题定位的分析
由于数据库中瞬时活动会话的等待事件小于10个，故很快的找出了可能存
在性能问题的2个会话，这2个会话产生的等待事件为 direct path read temp，该等待事件是指将数据从临时表空间中读取到 PGA 中，其通常和排序、HASH JOIN、临时表操作或者 BUG 等有关。
3）会话的分析
分析会话的连接和 SQL 执行情况，如下：
从 V$SESSION 中的 LOGON_TIME 和 STATUS 字段的值可以推断出该程序对应的连接类型为长连接。
从 V$SQL 中的执行次数、执行计划值、第一次和最后一次加载到共享内存中的时间和 WRH$_SQL_PLAN 中的执行计划值可以推断出该 SQL 的执行计划未发生变化，且平均每次执行的时间为0.4秒左右，但是由于出问题的时候客户反馈该SQL是超过1分钟无响应的，故0.4这个平均响应时间只能说明业务正常的时候或者绝大部分的执行时间相比于1分钟来说是很小的，即小于0.4秒每次。
从 ASH 数据 (DBA_HIST_ACTIVE_SESS_HISTORY) 中查询产生 direct pathread temp 等待事件的2个会话(即被 kill 掉的会话)的资源使用情况，可以得知存在问题的会话从11:56开始执行，到下午15点都没有执行完成，且其消耗了17G的 TEMP 空间；由于被杀掉的会话读取文件的地址在不停的变化，说明该会话并没有夯住，只是执行速度慢。
DBA_HIST_ACTIVE_SESS_HISTORY 的关键字段解释如下：
4）SQL 的分析
通过产生 direct path read temp 等待事件的 SQL_ID，找到对应的 SQL，SQL 文本如下( SQL 中的表名做了部分处理)：
其执行计划如下：
分析该 SQL 的执行计划，快速分析执行计划的时候我们主要关注点有如下：
表的连接方式
返回的行数
谓词信息
上面的案例中有如下连接方式：
嵌套循环(NESTED LOOPS)
嵌套循环的关键点是知道驱动表(外部表)返回的结果集行数就是被驱动表(内部表)需要被扫描的次数，靠近 NESTED LOOPS 关键字的就是驱动表。
半连接(SEMI JOIN)
半链接是指2个表做连接，只返回其中一个表的数据，此 SQL 中存在in子查询，且采用的是 HASH 半连接连接方式。
怎么来分析这个执行计划呢，这里提供如下几个方面的思考点：
1. 粗略的分析执行计划的效率
从上面的执行计划可以看出 CBO 评估出来的每步操作返回的最大行数不超过12行，假设该评估值是准确的，则无论是采用 HASH 还是嵌套循环的连接方式，理论上该 SQL 的执行时间应该是毫秒级别的。
但实际上该 SQL 的执行效率很低，故推测是 CBO 评估的行数是不准确的且执行计划是低效的。
2. 推测该执行计划效率低的原因
执行计划中的5到13步都是采用嵌套循环连接方式，嵌套循环连接方式的缺点就在于当驱动表的数据量越大，其循环扫描被驱动表的次数越多，故问题可能会出现嵌套循环的连接中。
再看在 SQL PLAN ID=4 的时候是将 SQL_PLAN_ID=5 和 SQL_PLAN_ID=32 做了一个 HASH 半连接的算法，当 SQL 在做 HASH 连接的时候会将结果集划分为多个 hash 桶，并将其尽量放在 PGA 中，如果 PGA 中不能存放则会使用TEMP空间，由于 SQL_PLAN_ID=32中 A_QUERY_ACCT_ATTR 表的数据量为72行， 但却消耗了17G 的临时表空间，故可能第5步返回的结果集会比较大，如果第5步返回的结果集大，由于其之前是通过嵌套循环获取的结果集，则其存在消耗大量时间资源的可能。
3. 利用 ASH 数据来查找问题点
有了第2点的执行计划效率低的推测后，我们再次通过 ASH 的数据来进行辅证，如下：
从 ASH 的数据中可以看到会话 90%以上的都是在进行 SQL_PLAN_LINE_ID=31 即 <TABLE ACCESS BY INDEX ROWID | DDSERV_ATTR>
由于31步后紧接着就是和第6步产生的结果集做第5步中的嵌套循环，可以推测第6步的结果集中返回了大量的数据，或第31步的 DDSERV_ATTR 也返回了大量的数据，当一个大数据量的结果集做驱动表去和大数据集的内表做嵌套循环计算时，其性能是非常低下的。
4.推测执行计划是否可以优化
a. 当遇到2个大数据量的结果集做连接时，我们一般会考虑采用 HASH 连接的方式来替代嵌套循环的连接方式，但此处表的连接数较多，且有明确的数据限定条件，故暂时不考虑此种方法。
b. 注意执行计划中的 Filter
执行计划中的 id=1和 id=3 是 2个 filter 操作，其对应 SQL中的如下子查询部分:
以及谓词访问部分：
可以看出该 SQL 涉及的2个标量子查询中，对于表 DDSERV_SUBSRIBER 涉及的标量子查询未发生子查询展开的情况，其对应于 id=3 这个 filter，且该 filter 是对 id=4 和 id=33 的这2个结果集进行一个类似嵌套循环的操作；
对于表 DDSERV_LOCATION 涉及的标量子查询其采用的是子查询展开的方式来执行，其对应于id=1这个 filter，其在执行计划的最后一步才进行数据过滤。
如果采用子查询和表优先进行连接的方式，并进行子查询数据处理的方式来过滤数据，假如过滤后的数据变得比较少，则执行的速度理论上会大幅提升。
5. 验证之前的分析结果
为了确认上述分析产生的疑问，需要跟踪执行速度慢的 SQL，获取其变量值，根据实际的数据分布情况来查找该原因。
幸运的是该系统中除了刚刚杀掉2个会话外，还有一个会话同样也产生了 direct path readtemp 等待且 SQL 和刚刚杀掉的会话所产生的 SQL 类似，通过对该会话做跟踪后，获取到了执行速度慢时的实际值，其为：
将如上变量值代入 QUERY 程序中存在问题的那个 SQL中，加上 gather_plan_statistics 提示做 SQL 性能测试，得到的结果如下：
从实际的执行计划中可以看出，问题开始于 id 为 24/25，Oracle 评估返回1条记录，而实际上返回了56条，这里其实可以通过 NVL(G.EXP_DATE, SYSDATE) =(SELECT MAX(NVL(EXP_DATE, SYSDATE)) FROM SERV_LOCATION WHERE SERV_ID =A.SERV_ID) 过滤条件来过滤出大量重复数据，但是 ORACLE 把其放在了 id=1 中去进行过滤 (FILTER)。后面的26/27步问题就越发严重了，实际返回行数达到了30000以上，但是 ORACLE 却将过滤条件放在了第3步来进行过滤 (FILTER)。到后面的第30步，返回的数据多达3000多万条，再做嵌套循环时，速度会较慢。
经过测试，该 SQL 执行超过2分钟仍然没有执行完成，故终止了此测试。
通过上面的分析，可以提出如下优化点：
采用子查询不展开且优先处理子查询表中的数据的方式来过滤数据。即是为子查询添加提示 /*+ no_unnest push_subq*/ 来强制前置过滤步骤以及关闭子查询展开，其中 no_unnest表示希望子查询不展开，push_subq 表示希望优先执行子查询中表的数据。
补充一下 HINT 的相关知识：
HINT 通俗的理解就是一种手工改变 ORACLE 执行计划的方法，它有固定的格式和固定的名字，格式为 /*+ hint 名字 hint 名字 …*/，hint 名字的值是同义词 V$SQL_HINT 中的 name 字段值，其一般是跟踪 select 关键字后面，需要注意的是即使 HINT 在语法和语义上是有效的其不一定在 SQL中会生效。
该案例中，修改前的语句为
修改后的语句为：
调整后的执行计划为：
调整后的执行计划中的 id=17 到 id=21,id=24 到 id=28 表示该 SQL 已经按照我们希望的子查询不展开且先进行子查询表数据处理的方式来进行连接。
调整后的 SQL 从之前的2分钟未执行完成到目前35毫秒便执行完成。
故可以通过如下方法来进行 SQL 优化：
通过 ORACLE 的 sql profle 脚本来调整此执行计划，调整后的SQL使用采用了 /*+ no_unnest push_subq*/ 提示的表连接方式来执行数据获取:
整个问题的主要处理过程如下：
该案例涉及到的主要知识点有如下：
DBA 应急处理流程
怎么准确的获取故障现象
日志文件诊断
数据库主机负载诊断
数据库等待事件诊断
ASH 数据诊断
执行计划诊断
HINT 的含义与使用方法
稳定执行计划的方法
通过上面案例的分享，是否还需要做如下思考呢？
该 SQL 的执行计划没有改变，那么是什么原因导致的 SQL 性能突然下降
呢?
从通过 sql profile 来调整 SQL 执行计划的解决方案来看，该问题是由于数据分布不均匀和表连接方式不适合该数据分布的情况引起的性能问题，那么该解决方案是不是一种通用的解决方式呢？
还需要刨根问题的去分析数据分布不均匀的原因么？
该问题再次爆发的可能性？
该案例中的解决方案的通用性怎样，你会怎么给客户介绍一些更好的后续改进建议?
你觉得该案例中比较有意思的地方在哪里呢？
有机会还可以做如下探讨：
1）分享你的问题处理流程与原则
2）你有调优强迫症么？
3）分享你的 SQL 优化那些事
4）分享你的执行计划调整方法，比如 out line, sql profile, sql tuning, spm, dbms_stats 包，改写 SQL，索引调整，应用设计调整等等针对 SQL 的优化调整方法
自上而下的业务系统的好用与否和应用使用人员、应用程序、中间件、网络、主机、数据库和存储等密切相关。在问题处理中不仅需要我们具有全局的广度，还需要我们具有专业方向的深度，IT服务之路漫长而富有挑战，与大家共勉。
搜索盖国强（Eygle）微信号：eyygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。
zData一体机 - 分布式存储解决方案数据架构Oracle DB2 MySQL NoSQL专项服务：架构 / 安全 / 高可用 / 容灾 / 优化 / SQL 质量管控运维服务：运维服务 | 代维服务人才培养：个人认证 | 企业内训软件产品：SQL审核 - Z3 | 监控 - Zone | 数据恢复 - ODU应用架构
应用软件开发：数据建模 | SQL审核和优化 | 中间件服务
业务架构
电子渠道（网络销售）分析系统 | 数据治理
恩墨学院