【恩墨学院】基于裸数据的异地数据库性能诊断与优化

【恩墨学院】基于裸数据的异地数据库性能诊断与优化

杨江

作者介绍

杨江， 6年Oracle工作经验，4年Oracle数据库专业服务经验，擅长性能优化、性能问题诊断、故障排查、GOLDENGATE。

影响数据库性能的因素有很多，从大的方面可以分为硬件和软件。硬件包括CPU、内存、存储、网络设备等，软件方面包括操作系统版本、操作系统参数、数据库版本、数据库参数、数据库架构、运行的SQL代码等。

以上因素中，运行的SQL代码可单独归为一类，这部分内容多变，可控性较低，与业务强关联，动态影响，难以准确捕获，问题此消彼长难以根除。通过我们处理的故障类型统计，80%的性能问题来自于不良的SQL语句编写。

生产环境常做访问控制，管理生产环境DBA忙于日常事务无法顾及数据库性能。本文介绍一次性从生产库上获取分析性能SQL相关的数据，拿到本地环境分析诊断生产性能问题。

较详细分析一个SQL的性能，需要的内容包括执行计划信息、表的基础信息、索引基础信息、SQL写法问题等等。这些内容都存放在数据字典中。

1、创建相关的表，语句参考：

注：（第三条、第四条红框处，没有* 是因为这两个视图里面有long类型，不支持create as ct操作，实际操作过程中，未获取long类型的数据，只选取了必要的列）

2、通过数据泵导出上述创建的表

3、导出AWR裸数据

$ORACLE_HOME/rdbms/admin/awrextr.sql 

4、本地导入创建的表

5、通过数据泵导入AWR裸数据

$ORACLE_HOME/rdbms/admin/awrload.sql

  裸数据初析

1、执行时段为10~12点，15~17点，平均执行时长超过1秒的SQL统计。多个采样期间都有执行的，取执行次数最多的采样期间。

2、执行结果部分展示如下：

3、生成这获取这此SQL的SQLAWR数据脚本(取前20)

4、生成结果放入命令窗口执行

注：红框为格式化操作

5、生成结果展示如下

  案例解析

NEW_TOP_PHYSICAL_16_awr_sqlrpt_dqdx4x39x2x7m.html

SQL 文本

SELECT COUNT(1)

  FROM GPCXXXXXXXX A

 WHERE A.VALIDDATE < :B1

   AND A.SUBMITDATE < :B1

   AND A.SUBMITDATE >SYSDATE - 40

   AND A.FEETYPE IN ('307')

   AND A.PLANSTATUS = 'N'

   AND ROWNUM = 1;

执行情况

1.  小时内还未执行完一次，但占用整个采样期间8.21%的物理读，并伴有严重的IO等待，对采样期间数据库整体性能有较大影响

2.  执行计划中存在全表扫描操作

3.  语句简单易懂

解析

表基础信息

近3亿行，未分区，平均行长149，理论占用空间大小为 296815739*149*1.17/1024/1024/1024=48G，实际占用约50G空间（从MY_DBA_SEGMENTS中获取），知此表碎片并不严重或不存在碎片。

SQL绑定变量分析

结合绑定变量和条件看，大范围上，只查询40天以内的数据。

条件列数据分布情况

回顾下SQL条件：

WHERE A.VALIDDATE < :B1

  AND A.SUBMITDATE < :B1

  AND A.SUBMITDATE > SYSDATE - 40

  AND A.FEETYPE IN ('307')

  AND A.PLANSTATUS = 'N'

  AND ROWNUM = 1;

结合条件和上述查询结果，分析如下：

FEETYPE,PLANSTATUS是等值关联，VALIDDATE是开区间范围关联，SUBMITDATE是闭区间范围关联。已知此表中SUBMITDATE保留3年数据，在数据分布平均的情况下，此SQL查询的数据量约为(296815739/3/365)*40/25/2=21.7W，约占整个表的0.07%。理论上适合使用索引，不必要全表扫描。

索引情况分析

1.  此表当前存在3个组合索引4个单列索引

2.  其中前三个索引实则过滤性极差，索引的NDV值仅2个或者3个，除非值严重分布不均，同时又经常选取值少的部分，不然这类索引没有存在的必要

3.  结合本例子SQL，涉及的列上均没有索引，建立FEETYPE, SUBMITDATE两列组合索引，理应提升SQL性能

解决方案

a.  建立FEETYPE, SUBMITDATE组合索引，执行SQL执行时长缩短到10S以内

b.  表按SUBMITDATE分区，数据按月存放数据

恩墨学院隶属于云和恩墨（北京）信息技术有限公司，致力于提供专业高水准的oracle数据库与大数据培训服务，挖掘培养大数据与数据库人才。恩墨学院提供包括个人实战技能培训、个人认证培训、企业内训在内的全方位大数据和数据库技术培训。ACE级别超强师资，配备专业实验室，沉浸式学习与训练，专业实验室、配备专业助教指导训练。能迅速融入专家圈子，业内资源丰富，迅速积累职场人脉。oracle数据库课程包括：Oracle DBA实战班、Oracle OCM考试、Oracle OCP考试等。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28530558/viewspace-2152923/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28530558/viewspace-2152923/