暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

Oracle 优化案例之热块竞争

原创 邓秋爽 2020-02-12
2933

案例之热块竞争

一、故障现象和描述

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

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

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

select a.sid, a.username, a.terminal, a.machine, a.module, a.event, a.status, b.spid, c.sql_id, to_char(LAST_CALL_ET) as seconds from v$session a, v$process b, v$sqlarea c where a.paddr = b.addr(+) and a.sql_hash_value = c.hash_value(+) and a.sql_address = c.address(+) and a.type = 'USER' and event not like 'SQL*Net message from client' order by c.sql_id, a.machine /

4.png

  • 数据库主要等待事件为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剖析,到底是执行计划的那几部消耗了较多的逻辑读,然后进行优化。

select * from (select row_.*, rownum rownum_ from (select /*+use_nl(a b) index(a IDX_PM_PROM_RANK_REWARD_3)*/ a.REWARD_RELA_ID as rewardRelaId,。。。 b.OFFERING_STATUS as offeringStatus from PM_PROM_RANK_REWARD a LEFT JOIN PM_OFFERING b on a.RANK_OFFERING_ID = b.OFFERING_ID where b.EXPRIED_DATE IS NULL OR b.EXPRIED_DATE > SYSDATE and ((a.REWARD_OFFERING_ID IN (:p1, :p2, :p3, :p4, :p5, :p6) and a.BE_ID IN (:p7, :p8) and b.OFFERING_STATUS = 'R')) ORDER BY rewardRelaId ASC) row_ where rownum <= :p9) where rownum_ > :p10

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

进一步分析执行计划14到18这几部:
1、 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然后回表。
2、 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)的处理方式:

sys@PUBDB>select * from dual where 1=1 or 1=2 and 2=3; D - X 1 row selected. sys@PUBDB>select * from dual where 1=2 or 2=2 and 2=3; no rows selected

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

select /*+use_nl(a b) index(a IDX_PM_PROM_RANK_REWARD_3)*/ a.REWARD_RELA_ID as rewardRelaId,。。。 b.OFFERING_STATUS as offeringStatus from PM_PROM_RANK_REWARD a LEFT JOIN PM_OFFERING b on a.RANK_OFFERING_ID = b.OFFERING_ID where b.EXPRIED_DATE IS NULL OR b.EXPRIED_DATE > SYSDATE and ((a.REWARD_OFFERING_ID IN (:p1, :p2, :p3, :p4, :p5, :p6) and a.BE_ID IN (:p7, :p8) and b.OFFERING_STATUS = 'R')) ORDER BY rewardRelaId ASC

对于处理条件A
b.EXPRIED_DATEISNULL
6.png
7.png
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的正确业务逻辑和写法应该是
select * from (select row_.*, rownum rownum_ from (select /*+use_nl(a b) index(a IDX_PM_PROM_RANK_REWARD_3)*/ a.REWARD_RELA_ID as rewardRelaId,。。。 b.OFFERING_STATUS as offeringStatus from PM_PROM_RANK_REWARD a LEFT JOIN PM_OFFERING b on a.RANK_OFFERING_ID = b.OFFERING_ID where (b.EXPRIED_DATE IS NULL OR b.EXPRIED_DATE > SYSDATE) and (a.REWARD_OFFERING_ID IN (:p1, :p2, :p3, :p4, :p5, :p6) and a.BE_ID IN (:p7, :p8) and b.OFFERING_STATUS = 'R') ORDER BY rewardRelaId ASC) row_ where rownum <= :p9) where rownum_ > :p10

综述:

  1. 当我们遇见实时性能问题时,我们可以直接去查询系统的动态性能视图,找到相关的等待事件、SQL_ID等,然后对相关SQL进行分析。
  2. SQL代码要严格审核,尽量简洁化,而且也要尽可能的少用hint,除非对该SQL的业务逻辑非常清晰,因为如果SQL出现变化,而原先的hint可能就不一定适用这个SQL,这个case中如果没有NL连接的hint,则即使SQL写法出现问题也不会出现热块争用。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论