超实用运维经验: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 条评论
登录 后参与评论

相关文章

来自专栏牛客网

阿里 Java研发工程师[内推] 电话面试

3月20号 阿里巴巴 Java研发工程师[内部推荐] 电话面试 1. 自我介绍(问了我哪里人、去杭州工作有没有问题、什么时间可以去实习) 2. 介绍一下自己做过...

3969
来自专栏程序员的SOD蜜

TOP语句放到表值函数外,效率异常低下

在XXX系统中,有一个获取客户数据的SQLSERVER 表值函数,如果使用管理员登录,这个函数会返回150W行记录,大概需要30秒左右,但如果将TOP语句放到表...

1998
来自专栏腾讯技术工程官方号的专栏

免费开放阅读 | 数据库管理系统的事务原理(上)

作者介绍: 那海蓝蓝,腾讯技术工程事业群计费平台部金融云TDSQL数据库T4级专家,熟悉PostgreSQL、MySQL、Informix等数据库内核技术,著有...

4818
来自专栏dotnet core相关

Use Generic Replacements of 1.X Framework API Classes 用泛型替换Framework 1.X版本的API类

说起来,我是没接触过Framework 1.X版本的程序,12年毕的业(算算时间也一年多了,依旧一事无成,汗),毕业之后到公司实习,然后转正,做项目,都是直接基...

471
来自专栏Java学习网

书写高质量代码之状态维护

状态之始 我们第一眼接触新事物所触发的思考方式,决定了以后我们看待这样事物的角度,进而影响更深层次的理解和行为。 编程相对于人类历史的进程而言,不过是个六七岁孩...

3435
来自专栏京东技术

闲话高并发的那些神话,看京东架构师如何把它拉下神坛

高并发也算是这几年的热门词汇了,尤其在互联网圈,开口不聊个高并发问题,都不好意思出门。高并发有那么邪乎吗?动不动就千万并发、亿级流量,听上去的确挺吓人。但仔细想...

1474
来自专栏禁心尽力

Java设计模式之模板方法设计模式(银行计息案例)

       不知道为什么,这几天对Java中的设计模式非常感兴趣,恰巧呢这几天公司的开发任务还不算太多,趁着有时间昨天又把模板方法模式深入学习了一下,做了一个...

1838
来自专栏张善友的专栏

WCF 消息帧格式

在TCP/IP协议栈中,当数据通过协议栈向下流动时,每一层都要给数据增加控制信息用于确保正确的传递。控制信息放置在被传送数据的开始,称之为包头,这种在协议栈中每...

1768
来自专栏腾讯技术工程官方号的专栏

Elasticsearch调优实践

2154
来自专栏LanceToBigData

OOAD-设计模式(二)之GRASP模式与GOF设计模式概述

一、GRASP模式(通用责任分配软件模式)概述 1.1、理解责任   1)什么是责任     责任是类间的一种合约或义务,也可以理解成一个业务功能,包括行为...

16110

扫描关注云+社区