15年老司机的DPM数据库性能分析产品研发之路

邹德裕

轻维软件首席专家

 

• DBAplus社群联合发起人，OraZ产品作者、Oracle OCM。

• 15年运维管理经验，在数据库诊断、故障排除、优化、架构设计等方面具有丰富的经验。

主题简介：

本次我给大家带来的主题分享为《15年老司机的DPM数据库性能分析产品研发之路》。

我将通过Oracle在实际生产中常见的运维场景及问题处理案例，解析如何直击、解决DBA的痛点，最后为大家揭秘DPM数据库的台前幕后。

先简单介绍下我的个人从业经历。

大学期间，我曾给系里为区政府做网站，当时正在推行“政府上网工程”， 一个暑假用asp就赚了一个学期的生活费。

毕业后在IT圈混了近10几年，先后做了开发、销售（卖的还是劳斯莱斯发电机）干了快半年，实在卖不动又回去搞开发，并用Java给公司做了个“7号信令”的解码软件（现在再让我回去写那些算法，估计写不出来了）。

再后来，我在开发中接触了Oracle数据库，便深深地喜欢上严谨的甲骨文，就去考了OCP，转型DBA。一路磕磕碰碰走过来，回首过往，才发现这段开发经验始终贯穿我工作的十几年。

现在的数据库管理员必须拥有前所未有的渊博知识，最好具有设计、开发、系统管理背景，与数据库有关的一切工作都可以看作是DBA的职责范围。DBA随时都要承受很大压力，不仅要完成应用和数据库的更改、快速解决问题，还要防止数据的丢失和损坏。

我们来了解下数据库DBA的主要职责：

1. 保证数据的安全如备份方案，容灾方案；

2. 数据库的可用性；

3. 日常故障处理、问题诊断 ；

4. 性能分析处理；

5. 数据库升级（打补丁）、改造、优化、补丁修复等。

下面通过5个场景，从SQL优化、后台常见错误定位分析、锁堵塞应急处理、如何规范完整地收集问题诊断信息，以及如何实现巡检报告的工具化、平台化，逐步展开对DPM性能分析平台的了解。

为了消除故障、缩短故障历时，提高设备运行性能，每月（或节假日）对数据库的运行情况进行巡检，以及时发现生产数据库时已经存在或潜在的问题，同时提交系统巡检报告，对系统存在的问题提出整改建议。而对于DBA来讲，数据库巡检是件繁琐的事情，但又不得不做。

以下为巡检报告目录，总共近65页（还一半目录未列出），以一个二线DBA为例，做一套库最快也要半天，且该项工作重复且枯燥，以下为巡检报告示例目录：

1）通过DBAplus社群工具OraZ实现的深度巡检，实现对系统配置、CRS、操作系统、补丁等软件环境运行配置检测、并提供原厂解决方案，该自动化巡检功能整合到自动化运维平台，一键体检实现主动扫描整个系统以及系统的操作系统、CRS、数据库、高可用等层面中的已知问题，提升系统的稳定性，检查并提示环境变化是否违反最佳实践，升级前后的验证评估、环境检测；

检测结果： Oswatcher未安装的明细：

平台将巡检收集的系统性能数据、容量信息、配置信息收集保存在平台，为后续系统维护、升级、扩容提供决策数据支持，实现巡检自动化。

作用：

1. 对于系统中存在的风险提供简单化和合理化的诊断分析建议；

2. 对系统中存在的风险提供对应专业的解决方案；

3. 评估结果量化评分，直观判断健康度；

4. 检测积累的最佳实践应用是否启用；

5. 数据入库统计分析：高风险漏洞统计、安全趋势分析、整体最佳实践应用评估、分析问题出现趋势、报告结果对比等。

2）DPM一键巡检模块

DPM巡检模块为Oraz巡检的升级和延伸，增加360式的一键巡检功能，支持数据库一键式深度健康检查，覆盖面更广，包括了配置、性能、安全（包含最新的比特币勒索检测）、对象审核、集群等150多个指标，并提供按巡检模板导出word功能，并生成巡检报告。

Oracle数据库的日常工作之一，当系统出现问题后尽快地定位问题，现场解决一部分常规数据库问题。如出现些600或07445等内部错误或疑难杂症时，需向Oracle support请求专家深入分析，如需在MOS上开SR时提供规范、完整诊断问题的信息。

如何收集信息也是DBA一件头疼的事，特别是开一级SR（故障当前持续中），此时在线专家会不时的要求现场提供对应的问题诊断信息，一来一往挤牙膏式的，往往耽误问题解决，延长业务恢复的时间。

与数据库打交道多年，处理过大量故障，也掌握了不少问题诊断的方法和工具，对每一类问题都可以大体归类出一些诊断方法。无论问题多么复杂，像扒洋葱一样，一层层去掉无关的，留下关键的，同时借助于一些诊断工具，层层深入，最后找到问题的核心。

首先按信息类型建立数据收集标准：

收集分类

• 标准Trace收集/Alert信息收集

RAC收集：

1. 每个节点的Alert.log

2. 每个节点的LMS[0|9] trace file

3. 每个节点的LCK trace file

4. 每个节点的LMON trace文件

5. 每个节点的LMD0 trace文件

6. 每个节点的DIAGA trace 文件

7. Alert log中提到的Trace 文件

非RAC收集：

1. 单Instance的Alert.log

2. Alert log中提到的Trace文件

性能影响：无，只是简单提取数据。

• OS日志信息收集

操作系统对应相关信息如下：

1. Linux： /var/log/messages

2. Solaris： /var/adm/messages

3. HP-UX： /var/adm/syslog/syslog.log

4. Tru64： /var/adm/messages

5. AIX： 每个节点的"errpt" 和"errpt -a"命令输出结果

6. Windows： 通过事件查看器导出系统日志和应用日志

性能影响：无，只是简单提取数据。

• RACDiag信息收集

运行racdiag.sql执行。

在相应的目前下执行这个脚本。

需要注意的这个脚本已经包含如下脚本：

-- 获取Hang Analyze的trace，并会执行一会：

oradebug setmypid

oradebug unlimit

oradebug -g all hanganalyze 3

-- 下面步骤将会执行时间比较长，需监控是否有对应文件生成：

oradebug -g all dump systemstate 267

若运行racdiag.sql这个脚本，就不再需要做sytemdump以及hanganalyze。

sqlplus “/ as sysdba”

sql> @racdiag.sql

性能影响：因为含有systemdump，做执行时间可能会相对较长.其执行时间受systemdump时间限制,之后去bdump以及udmp检查执行时间点产生的文件。

注：篇幅有限，以下的就不一一列出了。

CRS日志收集

1. HangAnalyze信息收集

2. SystemState信息收集

3. AWR/ASH信息收集

4. STATSPACK信息收集

5. Process相关信息收集

6. 诊断工具数据收集：OSWatcher、RDA、Procwatcher、11g ADRCLI、SQLT(SQLTXPLAIN)等工具

然后按故障类别梳理，整理对应故障所需提供诊断信息：

注：每种故障类型列出一个，其它不一一列出。

诊断RAC 问题：

• 实例剔除问题

报错信息为：

INSTANCE EVICITON/ORA-29740： “EVICTED BY MEMBER %S,GROUPS INCARNATION”

Errors in file /opt/bdump/10gR2/bdump/v10gR21_lmon_121396.trc： ORA-29740： evicted by member 0, group incarnation 18 Mon Dec 8 01：52：25 2007 LMON： terminating instance due to error 2974

一个Instance报ORA-29740错误，可能是由于Oracle的软件本身问题也有可能是由于硬件资源问题或者网络问题，对于这种问题，需收集如下信息：标准日志信息、CRS Logs信息、OS 信息、Procwaterh信息、OSW信息。

此外还有可能需要做CRS核心进程Debug（如Oracle后台专家建议）。

• Lmon进程中断问题

报错报错为：

ORA-481： “LMON PROCESS TERMINATED WITH ERROR”

一般是由于 LMON在监视集群软件本身时产生问题，出于结点本身一致性的需要，故需要LMON将此实例中断。

Errors in file /opt/bdump/10gR2/bdump//v10gR21_lmon_9944.trc： ORA-481： LMON process terminated with error Thu Sep 25 03：46：56 2008 LMON： terminating instance due to error 481

需收集如下信息：标准日志信息、CRS Logs信息、OS 信息。

• LCK进程中断问题

• RAC节点自动重启问题

• GES Potential blocker问题

• IPC Send Timed Out问题

诊断性能问题：

• 数据库慢问题

根据数据库的版本对应信息，一般情况下需收集信息如下：

1. ASH数据；

2. AWR：如果故障时间短，需要短时间频率的AWR时间连续性报告多份，如故障时间长，需要长时间频率的AWR 时间连续性报告多份；

3. OS信息、RDA信息；

4. Statspack信息(9i库)；

5. 进程信息数据如10046 and Error stack and Process dump；

6. Hanganalyze和System State信息。

• 数据库Hang/锁的问题

• 内存4031问题

• 内存Latch问题

诊断非RAC问题

• ORA-600问题

• ORA-7445问题

ORA-07445 ： exception encountered： core dump [%s] [%s]

需收集如下信息：

1. 标淮的Alert文件及Alert log提到的Trace文件；

2. 如果生成很多文件，建议把最初的Trace文件提供，如果有生成不同的Trace文件，把每一个的Trace文件第一个上传；

3. 在一些情况下，如果Trace文件没有生成，如果这样的话，core文件会对文件解释有用，并且需要用操作系统的debug工具进提供。

• Instance Crash问题

• Listener问题

• 数据库启动问题

DPM平台诊断信息快速收集解决方案：

1）整合自动化运维平台的脚本管理模块，通过脚本管理、SSH端口、远程命令调用等功能将诊断信息收集标准化，并整合进平台，帮助DBA在处理相关问题时进行快速信息搜集，熟练的故障信息收集能够减少故障处理的时间，这些必要的信息对于原因查找以及故障诊断和分析都是非常必要的。

2）故障出现时，通常DBA处理数据库系统的性能问题时遇到最大的困难就是在现象发生的时候去收集必要的诊断信息。
 

首先，必要的诊断信息一般很少能被收集到，因为在问题发生时去定位问题、决定收集哪些诊断信息、考虑如何去收集这些诊断信息会花费一些时间。

更多的情况却是，问题已经过去了，或者我们不得不关闭数据库来解决这些问题。这就迫使用户不得不等待问题再次发生时去快速收集信息。

DPM通过内部核心专业算法采集上百个内置指标和实时性能数据，已避免当数据库发生性能问题的时候，我们并没有机会来收集足够的诊断信息，比如system state dump或者hang analyze，甚至问题发生的时候DBA根本不在场，我们才能在事后根据DPM收集的信息来分析问题的原因。

如何快速发现并解决ORA-XXX错误信息和分析思路、解决方法

数据库后台日志错误类型很多，这里以ORA-00600数据库内部致命错误来展示分析过程：

报错原因

1）Ora-00600错误的发生，本质上是因为Oracle RDBMS程序代码在运行过程中发生了程序意外(program exception)，它属于Oracle的内部错误。

2）Ora-00600错误通常是由于Oracle BUG引发的，当然，其它一些情况也有可能引发，比如操作系统资源不够，或者硬件出现问题时，或者不正确的操作也有可能引起。

报错参数

1）在出现Ora-00600错误时，通常伴随具体的报错参数，每个参数被方括号包围，格式如下：ORA-00600 internal error code, arguments： [string], [string], [string], [string], [string], [string], [string], [string]

2）第一个参数表示了发生意外的Oracle代码的位置，它对定位问题起关键作用；剩下的参数提供更进一步的报错信息。

解决手段

1）通常在出现ORA-00600时，都会在USER_DUMP_DEST或BACKGROUND_DUMP_DEST目录下(11g的目录结构不同)产生一些trace文件，同时在alert<SID>.log文件中也会产生报错信息，这些信息和trace文件可以用来帮助分析错误原因；

2）还可以根据报错前数据库的日志，以及询问现场操作人员，分析数据库在报错之前进行了哪些操作，分析是不是因为不正确的操作引发了ORA-00600错误；

3）还可以通过MOS(My Oracle Support)来更深入的查找报错信息的说明，MOS还提供了ORA-600/ORA-7445 Troubleshooter功能，可以查找相同ORA-00600错误的案例；

4）如果没有关于您的报错信息的说明，需向Oracle support请求支持，比如在MOS上开SR。

数据库后台日志实时告警

DPM“日志分析”模块通过大数据技术实时提取和分析数据库后台日志，DBA可以通过界面形式直观展示数据库日志：

现象描述

数据库大量锁异常等待，系统资源消耗高，cpu负载高 （针对大量'TX,TM,DX'等类型的锁造成的大量异常等待）。

影响因素

多个事务争用造成。

传统解决方法

以下语句列出是谁造成了阻塞

 column event format a30 

  column sess format a20

  set linesize 250

  set pagesize 0

  break on id1 skip 1

select decode(request,0,'Holder：',' Waiter：') || s.inst_id || '：' || s.sid||','|| s.serial# sess,

      id1, id2, lmode, request, l.type, ctime, s.username,s.sql_id, s.event

-- ,s.service_name

  from gv$lock l, gv$session s

  where (id1, id2, l.type) in

    (select id1, id2, type from gv$lock where request>0

    )

   and l.sid=s.sid

   and l.inst_id=s.inst_id   

  order by id1, ctime desc, request

/

按照这个语句多查询几次，如果Holder持续不变，则跟开发商确认是否异常（如应用侧无法回滚或提交，则数据库端终止该进程以释放锁）。操作前记录相关日志。

DPM平台解决方案

通过DPM的“堵塞与等待”模块分析，实现数据库锁阻塞的智能分析及一键式解锁功能（涉及安全该功能暂时隐藏，后续修改成直接生成终止会话和进程的语句，由DBA自行判断是否能终止），包括锁的历史情况均可回溯，堵塞源头信息一目了然。

数据库80%以上的问题都为性能问题，而SQL优化的本质就是：1、缩短响应时间；2、提升系统吞吐量；3、提升系统负载能力。

要使用多种手段，在提升系统吞吐量和增加系统负载能力、提高单个SQL效率之间寻求一种平衡。就是要尽量减少一条SQL需要访问的资源总量，比如走索引更好，那就不要使用全表扫描。

SQL优化过程图：

SQL优化步骤：

1、获取SQL制定优化目标：从AWR、ASH、ORA工具、SQL CHECK SCRIPTS等主动发现有问题的SQL、用户报告有性能问题DBA介入等，通过对SQL的执行情况进行了解，先初步制定SQL的优化目标。

2、检查执行计划：explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。

3、检查统计信息：Oracle使用DBMS_STATS包对统计信息进行管理，涉及系统统计信息、表、列、索引、分区等对象的统计信息，统计信息是SQL能够使用正确执行计划的保证。

4、检查高效访问结构：重要的访问结构，诸如索引、分区等能够快速提高SQL执行效率。表存储的数据本身，如碎片过多、数据倾斜严重、数据存储离散度大，也会影响效率。

5、检查影响优化器的参数：optimizer_mode、optimizer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable等对SQL执行计划影响较大。

6、优化器新特性、BUG：如11g的ACS(自适应游标共享）、automatic serial direct path（自动串行直接路径读）、extended statistics、SQL query result cache等。有的新特性会导致问题，所以需要谨慎使用。

7、SQL语句编写问题：SQL语句结构复杂、使用了不合理的语法，比如UNION代替UNION ALL都可能导致性能低下。

8、优化器限制：无法收集或得到准确的统计信息、无法正确进行查询转换操作等，如semi join、anti join与or连用会走FILTER操作。

9、其他：主要涉及设计问题，如应用在业务高峰期运行，实际上可以放到较空闲状态运行。表、索引、分区等设计不合理。

以上几点，是我们进行优化时需要考虑的地方，可以逐步检查。当然，80%到90%的纯SQL性能调整，通过建立索引，收集正确统计信息，避免改写优化器限制，就已经能够解决了。

优化的示例这里就不列出了，大家可以参考丁大师在社群分享过的《看了此文，Oracle SQL优化文章不必再看！”》一文。下面我们来看看通过DPM的优化解决方案。

应用自动化工具DPM优化解决方案

通过上面的SQL优化的过程和步骤看，从发现问题SQL、获取执行计划、检查执行计划、对比执行计划、检查统计信息、获取访问对象结构信息到检查SQL语句编写等问题，如以手工方式做优化分析，需要资深DBA优化专家才能熟练完成，且耗时耗力，下面我们看看通过DPM工具快速发现问题并解决的案例。

整体实例性能诊断

通过数据库内部核心专业算法，为每个数据库构建一个单独的指标繁忙度，通过这一个指标度量数据库的健康情况，相比传统数据库监控数十个监控指标更为直观，快速发现并跟踪定位问题数据库：

获取和发现待优化SQL

1）第一种方法是通过DPM的慢SQL模块消耗占比高SQL，可以从CPU时间、执行次数、IO读、IO等待时间4个模块汇总分析，通过消耗占比高的语句制定优化的SQL语句目标。

2）第二种方法是通过DPM的性能分析模块，该模块采集数据库实时性能数据，构建数据库性能运行趋势图，通过图表的形式快速展示数据库性能状态，以及找到指定负载高时间段内消耗情况，点对点形式快速定位数据库性能瓶颈，也可通过拖动中间的标尺查看对应时段的性能数据，来展示对应时段内的顶级活动事件、顶级活动SQL、顶级活动会话，选择消耗百分比占比高的作为SQL优化目标。

获取执行计划

点击“慢SQL”或“性能分析”模块SQL_ID下钻详细对应SQL页面，直观展示SQL语句文本、消耗情况、执行次数、历史执行情况、执行计划等：

访问对象信息获取

点击“对象统计”获取对应访问结构涉及对象信息，若对应表或对象存在统计信息过旧问题（由“统计信息分析”模块发现的统计信息过旧对象）则会自动标红。

获取优化建议

快速定位SQL性能原因，对于问题SQL提供一键式优化建议和解决方案，并评估解决方案的提升率，下图为平台优化结果示例：

整体而言，我们提供的是企业级端到端的应用性能管理整体解决方案，拥有国内第一个真正实现了从应用层、中间件到数据库事务级的自动化识别和自动关联分析的APM应用性能管理平台，DMP既可以作为APM一个模块，也可以独立部署，作为数据库性能管理工具使用。与此同时，APM、DPM也可以与我们的AMP自动化运维产品和Ivory大数据日志分析平台深度集成，提供敏捷运维“产品+定制+服务”的企业级交付能力。

Q1：OraZ目前只支持Oracle？

A1：是的，目前还只支持Oracle。

Q2：目前支持的数据库版本？

A2：除Oracle外还支持MySQL、DB2。其中，Oracle需要10g版本以上。

Q3：这些图标可以下钻到什么程度？

A3：点击sql_id后可以下钻获取SQL语句文本、消耗情况、执行次数、历史执行情况、执行计划，进一步可以切换查看访问相关的统计信息和优化建议。

Q4：DPM模块是内嵌在OraZ里面的吗？

A4：DPM是全新开发的，与OraZ是独立的，而且OraZ工具应该是面向社区的工具，DPM是平台级，B/S架构的。

Q5：是否提供下载试用？上面提到的，逐层钻取，分析，对于一线DBA还是很有用的。

A5：暂未发出试用版，如需试用可以联系我，可安排poc。

Q6：DPM指的是什么？

A6：DPM是数据库性能分析平台的简称。

Q7：新的补丁出来多久打合适？

A7：按Oracle官方工具Orachk检查会是最近一个季度版本，具体可以从mos下载最新的检测看报告结论。

Q8：对比SQL monitor，OraZ与DPM在SQL优化方面有哪些优势或者便利？

A8：SQL monitor更多是针对SQL实时监控，超过5秒的cpu/io时间就会被记录，并分析，具体差别比较大，而OraZ是对全库的ash数据采集分析，OraZ与DPM相似，最大区别是面向个人和企业级。

原文发布时间为：2016-12-29

本文来自云栖社区合作伙伴DBAplus