作者介绍

邓秋爽

云和恩墨技术专家，擅长于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写法出现问题也不会出现热块争用。

End.

运行人员：中国统计网小编（微信号：itongjilove）

微博ID：中国统计网

中国统计网，是国内最早的大数据学习网站，公众号：中国统计网