基于裸数据的异地数据库性能诊断与优化

作者介绍

杨江, 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性能

解决方案

  1. 建立FEETYPE, SUBMITDATE组合索引,执行SQL执行时长缩短到10S以内
  2. 表按SUBMITDATE分区,数据按月存放数据

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

原文发表时间:2017-11-15

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java Edge

MySQL各种存储引擎介绍与适用场景1.引擎的介绍第三方存储引擎:InfobrightTokuDBXtraDB、PBXT2.常用两种引擎的选择

5206
来自专栏北京马哥教育

为 Zabbix 优化 MySQL

Zabbix 和 MySQL 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO。 考虑到这一点,我将提...

2533
来自专栏大数据平台TBDS

spark sql简单查询千亿级库表导致的问题

根据常理判断,简单的 select * limit 不会造成内存溢出的。因此,我们用hive原生sql查询,发现不存在这个问题。

1542
来自专栏Linyb极客之路

MySQL锁机制及优化

总的来说,MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。

942
来自专栏黑白安全

SQL(结构化查询语言)注入

SQL注入(也称为SQLI)是一种常见的攻击媒介,它使用恶意SQL代码用于后端数据库操作,以访问不打算显示的信息。此信息可能包括任何数量的项目,包括敏感的公司数...

5602
来自专栏用户画像

mysql模拟题三

  9、找回mysql服务器root密码的很重要的一步是跳过权限表的检查启动mysql,该命令是(D)(2分)

942
来自专栏跨界架构师

C#和NewSQL更配 —— CockroachDB入门(可能是C#下的全网首发)

  CockroachDB(https://www.cockroachlabs.com)是Google备受瞩目的Spanner的开源模仿,承诺提供一种高存活性、...

845
来自专栏北京马哥教育

优化 SQL SELECT 语句性能的 6 个简单技巧

SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循帕累托原则。20%的努力很可能会给你带来80%的性能提升,而为了获得另外20%的性能提升你可能...

43211
来自专栏数据和云

常识之外:全表扫描为何产生大量 db file sequential read 单块读?

编辑手记:在理解Oracle技术细节时,我们不仅应该读懂概念,还要能够通过测试验证细节,理解那些『功夫在诗外』的部分,例如全表扫描和单块读。 开发人员在进行新系...

3459
来自专栏Rgc

项目中记录影响性能的缓慢数据库查询

如果程序性能随着时间推移不断降低,那很有可能是因为数据库查询变慢了,随着数据库规模的增长,这一情况还会变得更糟。优化数据库有时很简单,需要在程序和数据库之间加入...

35011

扫码关注云+社区