业务高峰期超高逻辑读分析与优化

作者简介:戴秋龙,拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化,优化经验,具备丰富的行业服务背景。对Oracle数据库有深刻的理解,擅长数据库故障诊断,数据库性能调优。

背景

客户某系统系统业务高峰期CPU压力高达90%根据现场DBA反应主要因为逻辑读过高导致

分析


取出业务高峰期半小时,AWR报告。

很明显R_CR_RECORD对象占用逻辑读的34%,高达37亿。说明查询该表的SQl逻辑读需要优化。查询该表基本信息,该表按照日期(notify_time)做范围分区,一天一个分区,平均每个分区150M最大分区200M。该表索引信息

有问题的SQL

有好几个SQL有问题,但是问题都一样,分析有代表性的SQL。该SQl半小时执行14万次,每次4779个逻辑读

执行计划

执行计划中使用索引PK_R_CR_RECORD(notify_time,cr_id)<范围扫描,根据SQl业务是取1天数据. 尝试用RCD_CR_IDX(cr_id)索引。只有8个逻辑读。用RCD_CR_IDX(cr_id)索引效果明显比PK_R_CR_RECORD(notify_time,cr_id)效果好很多。为什么CBO走错索引?

寻找根本原因

回到SQL中,SQL逻辑是取一天数据,取整个分区的数据,根据其索引列(notify_time,cr_id),所以引导列所有数据都满足要求。虽然是范围扫描,但是范围太大了,有100%的数据,然后再过滤cr_id列。原因2,举例说明比如正常情况晚间收集,正好表又是1天1个分区,也就是说到白天业务查询时候,正好之前分区统计信息是收集过的,今天的统计信息没有更新。因此CBO会认为这边没有数据,但实际很多数据

综上所诉,统计信息延迟收集,导致CBO认为时间段的数据很少,因此走错索引,造成大面积的范围扫描,引起性能问题。实际走cr_id的单列索引效果好的多。

解决方案

  • 把主键 PK_R_CR_RECORD primary key (NOTIFY_TIME, CR_ID) local;换成

针对原先范围扫描,扫描大部分(甚至100%)数据的问题。调整后针对CR_ID字段范围扫描,而且CR_ID选择性很好,范围扫描只扫描1%甚至更小。总之CR_ID列的选择性远比NOTIFY_TIME好得多。

  • 选择之前某个分区的统计信息,设置现在和未来分区的统计信息。

针对统计信息收集不及时,造成CBO估算rows错误问题。因为表是1天1个分区,从实际数据量看每个分区都大致相等,因此设置后表的统计信息比较合理。

同样其他SQL优化效果:

半小时执行3万次, 每次1900逻辑读。优化后每次3个逻辑读。

半小时执行240次, 每次25逻辑读。优化后每次3.2万逻辑读。

半小时执行1070+次,每次20万逻辑读。优化后每次5逻辑读。

半小时执行 2540次, 每次16万逻辑读。优化后每次5逻辑读。

未经允许不得转载:Oracle一体机用户组 » 业务高峰期超高逻辑读分析与优化

相关推荐