超实用运维经验:TEMP表空间不足、热块竞争经典案例

作者介绍

邓秋爽

云和恩墨技术专家,擅长于SQL tuning、troubleshooting

系统运行过程中可能遇见各种各样的性能问题,如果仅仅是当前系统的性能问题,我们可以通过查询Oracle的数据字典视图、hanganalyze、systemdump、event等方法来分析性能问题。

而如果是历史的性能问题,则可以通过awr、ash、addm等诊断工具。但是有些瞬间出现的性能问题可能在awr、addm的报告中并不明显或者时间太短导致采样粒度无法获取,此时则可以通过历史的ash相关的数据来获取。

下面以历史temp表空间不足和热块故障的两个案例来展示如何对历史和当前的性能问题来分析。

案例之temp表空间不足

一、 故障现象和描述

客户一套核心的系统,在运行某个核心业务时总是报出temp表空间不足问题,相关业务也会因为该异常导致无法运行完毕,经和相关人员沟通了解到该业务是包含存储过程、匿名块还有某些单独运行的SQL,而他也无法知道是哪个部分运行导致temp表空间消耗殆尽而抛出错误。

二、 故障分析—警告日志

对于该问题我们的常规思路就是登陆系统查看temp表空间使用率,可是登陆系统后查询temp表空间使用率非常低,消耗temp的相关会话和SQL_ID如下:

总共消耗temp才有6MB,而这个数据库的temp容量有32GB,难不成业务反馈的是有问题的,系统没有出现temp不足的问题,既然出现了temp问题,那么数据库的alert日志肯定是会记录的。

业务反馈属实,系统确实曾经出现过ORA-1652: unable to extend temp segment by 256 in tablespace TEMP的错误,接下来就是如何去抓取历史的TEMP问题了。

三、 ASH抓取瞬间出现的TEMP问题

这个故障能否重现了,推测只要这个业务运行了就会出现,但是如果要业务程序再次失败对于业务是有影响的,并且这个ORA-1652: unable to extend temp segment by 256 in tablespace TEMP持续时间也不会特别长,因为一旦抛出这个错误,相关SQL就会运行失败,已经被使用的Temp就会释放掉,也不一定能够能够抓取相关的问题SQL。

此时回到了在上面介绍的如何对历史问题进行诊断,这里要推出oracle的历史性能问题诊断利器ASH,注意这个AWR采样是无发获取到相关信息的,并且AWR其实针对的是数据库整理性能问题,对于这种个别会话出现ORA-1652错误并不一定能够抓取到。

v$active_session_history视图是每秒钟对活动的会话采样一次,而dba_hist_active_sess_history视图是每十秒对活动会话采样一次。此时由于告警日志中记录的ORA-1652错误时间段的v$acitve_session_history视图数据还存在,这里就直接截取了其中故障时段临近几分钟的数据插入到ash0322 临时构造的表中

这里我们使用了v$active_session_history视图中的temp_space_allocated字段来获取了系统每个sample_time下相关SQL_ID的temp表空间资源消耗。

通过上面的查询已经得知就是SQL_ID 9q1kqvzvnzbjc单次消耗就需要16GB的temp表空间,而数据库的Temp表空间容量只有30G,这个SQL只要有并发或者节点之间一起使用则马上就会出现 ORA-1652: unable to extend temp segment by 256 in tablespace TEMP的报错。

四、 剖析SQL 性能、优化改善

temp消耗在这个SQL执行计划中有两部分,一部分是sort order by stopkey;另一个部分则是hash join,Sort order by stopkey是排序时的分页操作,消耗的temp空间是有限的,hash join才是消耗temp的根本原因

从执行计划ID 6得知表es_order_items ot作为驱动表返回的结果集是87M,也就是8千多万的数据行,而我们知道在Oracle的hash join运算时,由于PGA空间有限,如果驱动表返回的数据行较多,则构造hash table可能会在temp表空间也就是磁盘上运行;对于87M的数据量在构造hash table时必须是需要大量使用temp表空间,正是这个hash join导致了数据库的temp表空间消耗殆尽。

sort order by stopkey

这个虽然是排序,也需要将所有的数据取出来后来排序,但是消耗的内存或者temp空间是有限的,因为Oracle不需要将所有数据取出来全部一起排序,而是可以分批进行,每次保证在排序区域只需要有固定行数的数据。

这个我们可以认为这个排序区域只有10行数据,然后每次新的数据进入到排序后就进行比较,发现这条数据是否最小,如果不是最小就将排序区原来10条数据中的create_time最小那条数据踢出去,这个排序算法是我个人认为理解。

Oracle的算法应该更加科学点,不过可以先这么理解,总之排序分页环境下temp消耗是有限的,大家可以手动创建一个非常小的temp tablespace关闭自动扩展,构造一个亿数量级的大表来排序,但是只取其中的前10行数据,看是否temp报出空间不足,也可以用gather_plan_statistics来查看SQL实际的运行的temp资源消耗等方法都可以验证。

如何优化改善:

  • 由于该SQL有rownum限制,其实相当于一个分页取数据的方式,如果能够去掉order by o.create_time desc的排序,则该SQL能够走nested loop的方式,并且在nested loop阶段就走counter stopkey,既不用排序也不用扫描所有表段或者索引段后来做表关联。
  • 如果业务上必须排序,也可以在表es_order上创建create_time索引,不过需要注意的是必须创建global级别索引才能保证该SQL返回的数据是有序的(因为这里没有走分区裁剪,是扫描了多个分区,如果local索引在跨分区索引范围扫描时并不能保证取出的数据是有序的,Oracle不会走nested loop的执行计划),这样该SQL在表关联阶段就会走nested loop的方式。
  • 添加更高效的过滤条件,控制驱动表返回的数据量。

综述:

  • 当遇见历史问题时,需要结合警告日志、进程跟踪文件、业务日志等综合分析,在数据库层面上ASH提供丰富的Session历史数据。
  • ASH中的两个视图v$active_session_history、dba_hist_active_sess_history,通过对ASH数据挖掘,找到相关的等待事件、SQL_ID等信息深入分析。

案例之热块竞争

一、故障现象和描述

客户反馈核心系统的cpu使用率达到了90%以上,业务超时严重,需要尽快介入处理。

二、故障分析介入等待事件

对于实时的性能问题,我们优先考虑Oracle的V系动态视图,实时登陆系统发现数据库出现大量的latch:cache buffer chains、latch free等待。

  • 数据库主要等待事件为latch:cache buffer chains和latch free两种等待,等待相关的SQL不相同,但是实际都是同一业务类型的SQL。
  • Latch用于保护SGA中共享内存结构。Latch就像是一种快速被获取和释放的内存锁,用于防止共享内存结构被多个用户同时访问。
  • latch:cache buffer chains等待是一个热块现象,该等待根本原因是在高并发环境下的高逻辑读请求导致。
  • Latch free需要根据具体的latch#编号来分析,在Oracle 10G之前所有的latch等待都被记录为latch free等待。

三、回归SQL优化

出现latch:cache buffer chains、latch free等待的SQL都是同一业务类型发起的,那么最终还是要回归到SQL优化。

latch:cache buffer chains是热块竞争,高并发模式下的高逻辑读导致,那么就需要对该SQL剖析,到底是执行计划的哪部分消耗了较多的逻辑读,然后进行优化。

分析执行计划,根据cost成本消耗来看cost消耗最大的在执行计划id 14到18这个nested loop outer上面,cost核算这部分执行计划成本是8238K Cost。

分析执行计划14到18:

  • SQL语句中使用了hint的 /*+use_nl(a b) index(a IDX_PM_PROM_RANK_REWARD_3)*/,而IDX_PM_PROM_RANK_REWARD_3索引对应列是的a.REWARD_OFFERING_ID,这个地方是全索引扫描IDX_PM_PROM_RANK_REWARD_3然后回表。
  • PM_PROM_RANK_REWARD A表驱动表返回的数据优化器估算是2954K Rows,然后做Nested loops链接,被驱动表PM_OFFERING b走唯一索引扫描然后回表,优化器估算这个Nested loops成本最后达到了8239K Cost,正式由于驱动表返回数据较多,导致被驱动表需要进行多次唯一索引范围扫描然后回表,从而导致这个NL连接Cost较高。

首先这里先明确一个要点:Oracle在处理where 条件A or 条件B and 条件C是选择条件A or (条件B and 条件C)的处理方式。

那么这里的PM_PROM_RANK_REWARD a表为什么会估算返回2945K了,这个地方是因为红色部分字体的where条件:

对于处理条件A:

IDX_PM_PROM_RANK_REWARD_3的全索引扫描后回表,由于A表没有其他where条件,只能返回2954K Rows,也就造成了Nested loop模式下被驱动表由于扫描次数较多消耗较多的逻辑读,从而出现热块争用latch:cache buffer chains等待(latch free部分先没有关注,应该先解决大比例的latch:cache buffer chains)

四、核实业务逻辑

  • 业务核实代码业务逻辑存在问题,对于b.EXPRIED_DATE时间字段的处理条件是(b.EXPRIED_DATE IS NULL OR b.EXPRIED_DATE > SYSDATE)
  • 业务重新修改SQL后执行计划、资源消耗回归合理范围,latch:cache buffer chains热块等待现象消失,latch free等待也没有再出现。
  • 那么这个SQL的正确业务逻辑和写法应该是:

综述:

  • 当我们遇见实时性能问题时,我们可以直接去查询系统的动态性能视图,找到相关的等待事件、SQL_ID等,然后对相关SQL进行分析。
  • SQL代码要严格审核,尽量简洁化,而且也要尽可能的少用hint,除非对该SQL的业务逻辑非常清晰,因为如果SQL出现变化,而原先的hint可能就不一定适用这个SQL,这个case中如果没有NL连接的hint,则即使SQL写法出现问题也不会出现热块争用。

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

原文发表时间:2017-10-16

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

和开发同学讨论的一个技术问题(r8笔记第73天)

今天下午的时候,有一位开发同事找我,说有一个技术问题想请教一下。 当然正如他所说,这个问题比较奇怪,而且已经影响了他的测试流程,他说有一个表查看对应的表空...

2735
来自专栏逍遥剑客的游戏开发

游戏配置序列化

2674
来自专栏Albert陈凯

Hbase二级索引

二级索引与索引Join是多数业务系统要求存储引擎提供的基本特性,RDBMS早已支持,NOSQL阵营也在摸索着符合自身特点的最佳解决方案。这篇文章会以Hbase做...

3304
来自专栏大闲人柴毛毛

java处理高并发高负载类网站的优化方法

一:高并发高负载类网站关注点之数据库 没错,首先是数据库,这是大多数应用所面临的首个SPOF。尤其是Web2.0的应用,数据库的响应是首先要解决的。   ...

4456
来自专栏程序猿DD

阿里巴巴硅谷 Istio 专家解读Istio 1.0 发布

Istio 1.0 于北京时间8月1日0点正式发布!虽然比原本官网公布的发布时间晚了9个小时,但这并未影响到Istio在社区的热度。

813
来自专栏IT派

Python爬虫框架Scrapy实战 - 抓取BOSS直聘招聘信息

参考翻译文档的安装教程:http://scrapy-chs.readthedocs.io/zh_CN/latest/intro/install.html

943
来自专栏友弟技术工作室

awesome-go:很全的go语言资源合集

前面发过关于awsone-python, awsone django, flask。最近在学习golang,所以找到awsone-go

1421
来自专栏xcywt

程序员需要知道的十个操作系统的概念

说明:我之前在网上看到这篇文章觉得非常好,于是把它翻译了下来。当然很多地方翻译的很渣,见笑了。温馨提示,文章有点长。

521
来自专栏Java Web

SpringBoot技术栈搭建个人博客【前台开发/项目总结】Bootstrap or Vue?前台页面分析后台页面分析项目总结

1603
来自专栏Java进阶架构师

「mysql优化专题」视图应用竟然还可以这么优化?不得不收藏(8)

视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。视图并不在数据库中以存储的数据值集形式存在,而是存在于实际引用的数据...

583

扫码关注云+社区