Case1: 执行计划变更引起的性能问题原因分析

“比起解决问题迅速准确的找到问题的原因更为重要！”

数据库的性能问题都是在复杂的环境，由多种复合型的原因引起，并以多样的形态呈现的。MaxGauge就是监控并且分析这些多样原因的一款工具。在PracticalTroubleshooting这个专栏中我们就针对这些性能问题中最常见的一些的问题进行具体的分析和讲解。在这一部分，我们针对正常运行的批处理程序及批处理sql在批处理窗口中突然不能正常执行的现象，及由于争用（LOCK等）引起的性能问题进行进行详细的分析。通过具体的分析，希望各位工程师们可以了解MaxGauge性能分析的方法论.

下面我们来看看第一个事例

Case1. 执行计划变更引起的性能问题原因分析

概述

CBO优化器更加适合现在数据量大，用户多，且大量的用户以不同的方式查询这些数据的环境 。但是不能忽视的是CBO容易出现执行计划(execution plan)变更的问题。当然，执行计划变更有时候时会得到好的结果，不过大多都是与之相反的。针对这个问题，Oracle在11g之后的版本中都提供 SPM (SQL Plan Management)来进行执行计划的管理。但是，实际上由于执行计划变更而导致的性能问题依旧是经常困扰DBA们的一个问题。所以，我们在这个case中就来介绍一下如何使用MaxGauge来诊断分析执行计划变更引起的性能问题。

分析步骤

运用MaxGauge进行性能问题原因分析步骤如下。在实时监控（real-timemonitoring）功能查看各个会话的SQL响应时间之后，排查出问题会话，然后将问题会话的执行历史在事后分析（Performance Analyzer）功能中进行具体分析。

图 1. 执行计划变更引起的性能问题分析流程简图

分析详解

• Step 1.1：使用Active Session Elapsed Time Frame的实时监控

首先，使用MaxGauge实时监控（RTM）功能中的Active Sessions Elapsed Time Frame功能，确认现在活动会话的SQL语句执行时间（elapsed time）。

图2. Active Sessions Elapsed Time Frame

(!) 确认结果: 可以看到SQL语句的执行时间秒数

• Step 1.2 ：连接ActiveSessions Frame

通过ActiveSessions Frame确认相关会话的详细信息(Program名, SID, SQLID, SQL等)

图3. Active Sessions Frame

(!)确认结果: 现在出现SQL延迟的进程是 [   ], SID是 [ ], SQLID是[ ]. 最近进程[  ]都没有因为延迟的SQL出现性能的问题，突然出现这样问题，那所就需要连接到Performance Analyzer将最近的执行历史调出进行比较分析

• Step 1.3： 连接PA

MaxGauge采取的是One-Stop性能分析方式，拖拽Trend Chart Frame中需要查看的区间就可以直接连接到Performance Analyzer画面。

图4. 拖拽Trend Chart Frame连接到事后分析（PA）

• Step 1.4： 在PA Performance Trend中追踪相应的会话

利用Performance Trend画面下端的Active Sessions中的“SessionList”功能，

追踪问题会话

图5. 连接Session List

 
MaxGauge的 “SessionList”功能可以以秒为单位追踪会话的活动信息

图6. Session List

(!) 确认结果:  该会话从[ 00点00分00秒]开始出现问题SQL, 到现在位置的SQL 执行时间为 [ ]秒。

• Step 1.5：使用PA SQL Detail进行原因分析

出现问题的SQL是最近都在正常范围内执行的SQL。通过最近的执行历史和现在的执行状态的比较可以得知，需要进行性能低下问题的原因分析。所以，接下来就从“Session Detail” 连接到“SQL Detail” 画面

图 7. 连接SQLDetail

图8. SQL Detail

(!) 确认结果:  该SQL的执行时间及IO容量 从[ ]点开始增加，可以得知改时间点的执行计划被变更了。 (SQL PlanHash变更).

结论

由于执行计划变更引起的性能降低的问题一般实际上都是因为不正确的统计数据引起的。我们这里展示的这个例子也不例外。实际这种情况一般都是由于开发人员的失误导致的JOB内部统计信息生成的位置发生错误的变更而导致的。

l  AS WAS: Truncate -> Data Loading -> 生成统计信息

l  AS IS: Truncate -> 统计信息生成-> Data Loading

由于上述的原因，该table别识别为‘0’，所以相应的执行计划就变得没有效率了。该问题通过第一轮的 SQL Tuning解决之后, 排列有问题的Job的进程就被恢复了。

综上所诉，MaxGauge不但能够通过实时监控性能，还提供阶段性的详细的综合性能分析。不仅如此，这里还可以使用一款交租LitePlus的专业 SQL调优软件进行SQL tuning。让业务变得更加的高效

附件1. 使用LitePlus的 SQL Tuning示例

-by:Nicky康