从SQL优化到系统级别优化漫谈

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

背景

电信某系统某个语句性能堪忧,但是对于业务又非常重要,需要优化。从业务反应,以及AWR报告中需要1.5小时跑不完。

SQL

SQL语句比较长,

主要是insert select 语句,一般这种主要是select慢导致insert慢。

这里主要分析select语句。

分析

首先两个select字句非常相似,主表OMOOSOS1表只在第一个子句中有。

执行计划:

基本信息:OFFER_SERV 17G,OFFER_MEMBER 4.5G OFFER 4.3G。 OFFER_SPEC,和A_CARD_NUMBER_BLACKLIST表是小表,10M不到。

SQL中有价值的条件OM.CREATE_DT>(select MAX(VERSION) FROM INST.A_CARD_NUMBER_BLACKLIST);

考虑这一步是否可以用索引扫描过滤很多数据?于是查询MAX(VERSION).发现值是3个月前的日期。数据量还是很大回到执行计划中id = 7,20都是对同一个大表扫描2次,而且过滤谓词相同,根据rows判断最终的数据量并不多。所以作为nested loop的驱动表。然后根据索引再扫描其他大表。那调查下这一步的数据量。数据只有8011

读者可能会想到在表 OFFER_SERV(SERV_SPEC_ID,STATUS_CD)上面建立索引,用索引扫描代替全表扫描。 这是不好的,因为生产库不能随便建索引。因为这里2次扫描我们可以通过等价改写合并成1次扫描。我们通过with as 语句把需要的数据放入’临时表’这样可以减少一次大表访问。

SQL:(篇幅问题部分省略)

这里涉及到一个技巧,我们再查询单表时可以开启并行,但是作为nested_loop 的驱动表时需要关闭并行,这里不细说。等下次文档再详细说说并行。等价改写后SQL在292S跑完。

针对这个SQL建议:

  1. 等价改写SQL,把SQL中SELECT PROD_ID FROM INST.OFFER_SERV WHERE SERV_SPEC_ID = 1537 AND STATUS_CD = 12 固定到’临时表中’。
  2. 建立索引OFFER_SERV(SERV_SPEC_ID,STATUS_CDSQL中的并行需要去掉。

改后预计SQL能在30S之内跑完。

思考性能能否进一步提高?

可能读者问1.5小时跑不完的SQL,优化到5分钟,已经满足需求了。我给业务也是2点建议。但是还可以进一步优化,我们看 id =10的谓词信息

执行计划中根据IDX_OFFER_MEMBER_MEMBER_ID访问OM表(实际根据索引中的rowid访问数据),这一步其实就是离散IO,因为不知道rowid在表(磁盘)中的位置,而且也没有规律。再在表中过滤OBJ_TYPE,STATUS_CD,CREATE_DT 字段。 CREATE_DT是最近3个月的数据,可以过滤很多数据。那如果在索引是(MEMBER_ID,CREATE_DT)那直接在索引中可以去掉很多数据, 自然需要回表的数据量也就少很多。那离散IO数量也就少了。

系统级别优化点

  • 用”临时表”改写方案减少1次访问大表, 减少数据访问次数。
  • 我们建立的索引,就是把需要的数据按照一定规律组织到索引中, 改变数据的访问方式提高效率。( 例如本案例中建议建立的索引,我们把数据有规律的集中在索引中,自然访问数据时就变成范围扫描了,其效率用全表扫描的方式要高)
  • 还说到把过滤性强的数据放入索引中,减少索引回表带来的离散IO。

未经允许不得转载:Oracle一体机用户组 » 从SQL优化到系统级别优化漫谈

相关推荐