delete相关的pl/sql调优(r4笔记第87天)

今天开发找到我,说有个问题想征求一下我的意见。 问题的大体意思是,对目前环境中的两个表,我们就叫做表a,表b吧,他说根据一个时间字段去判断是否为5天前的记录,但是这个字段不是索引列字段。 想问我是否需要添加索引会能极大地提高性能。 对于这个问题没有肯定的回答,还是取决于数据和表的特性,首先我看了下表a,表b,根据我的印象,这两个表的数据量都不大,简单验证了一下,一个在300万,一个就10万左右。所以从数据的角度来看,走全表扫描也是合理的,不需要再创建一个索引了,因为新增加的索引可能会对现有的一些sql语句造成印象,还需要做一些评估才可以。 其次,这两个表都是核心层的表,这种表的结构式不能轻易修改的,表中的索引,结构都是在很多项目中反复验证通过的。所以也是不建议直接增加索引的。 开发的同事得到了希望的答案,过一会我就收到了另外一封邮件。 我一看就发现和开发的同事问我的问题有些关联。 这一段脚本是做一个清理相关的操作。最后还尝试用到了分段提交。

DECLARE
  CNT_REC NUMBER(13);
  tot_rec number(13);
BEGIN
  CNT_REC := 0;
  tot_rec := 0;
  FOR C1 IN (select *from bpm_context_inst
              where objid in (select context_inst2context_inst from bpm_proc_inst where objid in 
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and  END_TREATMENT_DATE <= SYSDATE -1 ))) 
    LOOP
    BEGIN
      delete from bpm_context_inst t where t.objid = c1.objid;
    END;
    CNT_REC := CNT_REC + 1;
    tot_rec := tot_rec + 1;
 
    IF CNT_REC = 100 THEN
      COMMIT;
      CNT_REC := 0;
    END IF;
 
  END LOOP;
  COMMIT;
END;

这段脚本让我有些纠结,因为从这个开发同事的角度来看,这段pl/sql脚本中一旦确定了coll_entity_history 不需要做索引扫描,可以走全表扫描,他们认为就万事大吉了。但是我一看到上面的cursor中的那段代码,就开始担心了。 因为他问我的问题似乎还是和这个问题有一定的差距。表bpm_context_inst,bpm_proc_inst这两个表都是数据量近亿的大表。如果从coll_entity_history中得到了太多的数据,外层关联两个大表,相比性能也会好不到哪去。如果数据量比较大,直接通过cursor得到缓存数据集,做delete还是有一定的性能瓶颈的。对此我也是心有余悸。可以参见我之前碰到的一个问题。http://blog.itpub.net/23718752/viewspace-1172818/ 如果数据量较大,可以尝试调优一些这段pl/sql。为了能够准确地评估这段Pl/sql的执行情况,我申请从备份库中导出了这几个表的dump,在测试库中进行测试。 导出很顺利,但是导入的时候表bpm_context_inst是相当慢,等了半个小时多,还是没有什么反应。 以下是通过imp导入的一些数据,执行了15分钟的时候,值导入了60多万行,按照这个进度,今天是不用干别的了。 SQL> select 620540/15/60 from dual; 620540/15/60 ------------ 689.488889 按照之前的测试impdp效率也好不到哪去,为什么呢,我一查看表结构恍然大悟,原来这个表中含有clob字段。 好了想模拟测试数据,看来按照目前的情况还是不现实的了。 我们来显分析一下数据。既然是需要按照每隔5天来做数据的清理,来看看这些天来的数据情况。 以下是我分析得到的近50天来的数据情况,每天的相关数据量其实还是很小的。

65788

2-Mar-15

30787

7-Feb-15

7987

11-Mar-15

33929

6-Feb-15

29370

10-Feb-15

53211

26-Feb-15

62

23-Mar-15

3382

17-Mar-15

4443

4-Mar-15

12095

13-Feb-15

8789

12-Feb-15

3074

13-Mar-15

7598

20-Feb-15

2239

8-Mar-15

2627

14-Mar-15

7726

24-Feb-15

6330

22-Feb-15

6537

19-Feb-15

7158

21-Feb-15

2872

7-Mar-15

7419

18-Feb-15

5104

9-Mar-15

46094

27-Feb-15

18377

4-Feb-15

7571

16-Feb-15

4145

6-Mar-15

4006

16-Mar-15

20

24-Mar-15

7578

17-Feb-15

29062

9-Feb-15

6821

15-Feb-15

886

18-Mar-15

9221

25-Feb-15

3822

10-Mar-15

953

20-Mar-15

62115

5-Mar-15

2484

15-Mar-15

26311

8-Feb-15

6997

14-Feb-15

16572

28-Feb-15

68

26-Mar-15

11202

11-Feb-15

9251

1-Mar-15

5056

22-Mar-15

2869

21-Mar-15

29508

5-Feb-15

1

25-Mar-15

493

19-Mar-15

55572

3-Mar-15

7469

23-Feb-15

5008

12-Mar-15

按照这个数据量,原本存在性能隐患的那段pl/sql看起来也顺眼多了。但是细细看来,还是有个硬伤。 就是cursor定义的部分,根据pl/sql的实现目标,没有用到clob字段,所以是不相关的。可以在cursor的部分直接过滤掉。

C1 IN (select *from bpm_context_inst
              where objid in (select context_inst2context_inst from bpm_proc_inst where objid in 
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and  END_TREATMENT_DATE <= SYSDATE -1 ))) 

直接改为select objid from bpm_context_inst就可以了 最后我给出了两种意见,第一种是上面的pl/sql完全可以通过一句delete语句来完成,至于他们关注的分段提交,其实在这个场景中,影响是忽略不计,实际上一次提交性能还要好于分批提交。 即

delete bpm_context_inst
              where objid in (select context_inst2context_inst from bpm_proc_inst where objid in 
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and  END_TREATMENT_DATE <= SYSDATE -1 ))

另外一种就是把cursor定义的部分做一些改动,去除clob字段。对于cursor的提升是还是很大的。 对于这两种意见,开发同事说自己确实考虑得不够周到,不过为了避免给客户造成更多的困扰,还是选用第二种方案。 这个问题带给我的启示就是可能大家问你的问题是一个场景,但是和另外一个场景联系起来,原来的一些推论就不是那么肯定了。就比如这个问题中,开发确定不需要索引对于查询这个表性能影响不大,但是并不意味着在和其他的大表关联时没有问题。如果那种情况发生,还需要做一些额外的优化工作。根据数据量最后得知,变更的数据量很小,所以这个也算是化险为夷吧。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-03-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

扫码关注云+社区