“表姐”单表600条记录delete优化前1小时优化后1分钟

作者简介:惠星星,现就职于北京海天起点,持有OCP 10g、OCP 11g、OCM 11g证书,并有长达8年电力行业业务维护、数据库维护服务经验,擅长Oracle数据库性能优化、故障处理及数据可视化技术研究。

今天业务侧”表姐”反映了一个奇怪的SQL性能问题,单表600条记录,删除其中部分数据执行了1个小时未执时成功,

试了好几次均执行失败,该工作下班前必须完成,”表姐”很崩溃,最终只能请求DBA进行问题定位及优化,本着”表姐”的问题就是整个DBA组的问题的原则,展开以下分析,

查看后台进程状态:

问题1:看到等待事件db file scattered read,初步怀疑为单表数据量较大且全表扫描,所以长时间未执行成功。

查看执行计划:

问题2:

1、为什么执行计划是主键索引唯一扫描,INDEX UNIQUE SCAN?

2、为什么执行计划很快,但执行时间特别长,统计信息缺失或不准有可能造成该问题?

检查统计信息:

统计信息准确,100%收集,记录数595条,非分区普通表。

问题3:

1、数据库全表扫描查询哪些表?

2、为什么查询这些表?

10046 event:

使用10046 event查看数据库进程在查询哪些表?

日志显示进程在执行以下语句:

问题4:通过以上分析,验证了数据库正在进行批量全表扫描,但为什么执行以上查询呢? 是外键约束吗?是外键未创建索引吗?

检查业务表外键约束关系:

经过查询发现以上表外键未创建索引,所以在被引用表上执行全表扫描。

到这里问题就比较清晰,该表存在23张表的外键引用约束,由于外键未创建索引,所以删除任何一条记录都会对原表执行全表扫描,并删除对应数据。

通过检查以上23张表数据量为5.88G,删除键83个,所以需要扫描数据5.88*83=488G。

小结:

单表595条记录,删除其中83条主键记录,由于被外键引用的23张表外键未创建索引,因此需要对23张表

执行83次全表扫描,合计扫描数据量488G,长时间未执行成功。

解决方案:

1、数据量超过1G的引用表,外健创建索引。

2、碎片整理:经与业务沟通23张表中,批量清除过历史数据,水位较高,进行表级碎片整。脚本如下,

注意:move操作会造成索引失效,需要对相应索引进行重建,经检查总blocks数降低为之前的1/2,性能提升2倍。

3、参数优化:由于该操作批量使用多块读,检查多块读参数db_file_multiblock_read_count 16,建议该session设置为128

全表扫描为多块读操作,设置该参数后单次IO最多读取128个块,较之前性能提升8倍

4、操作分解:根据现场大数据量操作规范要求,对删除操作进行拆分,如

优化效果:

优化前1小时未执行成功,优化后1分钟执行完成。

1小时(优化前)>>>>1分钟(优化后),SQL优化后”表姐”很开心。

未经允许不得转载:Oracle一体机用户组 » “表姐”单表600条记录delete优化前1小时优化后1分钟

相关推荐