delete相关的pl/sql调优

简介:   今天开发找到我,说有个问题想征求一下我的意见。 问题的大体意思是,对目前环境中的两个表,我们就叫做表a,表b吧,他说根据一个时间字段去判断是否为5天前的记录,但是这个字段不是索引列字段。

  今天开发找到我,说有个问题想征求一下我的意见。
问题的大体意思是,对目前环境中的两个表,我们就叫做表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 ))
    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 c oll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and  END_TREATMENT_DATE )) 
直接改为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 )
另外一种就是把cursor定义的部分做一些改动,去除clob字段。对于cursor的提升是还是很大的。
对于这两种意见,开发同事说自己确实考虑得不够周到,不过为了避免给客户造成更多的困扰,还是选用第二种方案。
这个问题带给我的启示就是可能大家问你的问题是一个场景,但是和另外一个场景联系起来,原来的一些推论就不是那么肯定了。就比如这个问题中,开发确定不需要索引对于查询这个表性能影响不大,但是并不意味着在和其他的大表关联时没有问题。如果那种情况发生,还需要做一些额外的优化工作。根据数据量最后得知,变更的数据量很小,所以这个也算是化险为夷吧。
目录
相关文章
|
6天前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量:数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中,变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”,用于存储程序运行时的动态数据,通过`DECLARE`定义,可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”,一旦设定值便保持不变,用`CONSTANT`声明,提供程序稳定性和易维护性。通过 `%TYPE`、`NOT NULL`等特性,可以更高效地管理和控制变量与常量,提升代码质量。善用两者,能优化PL/SQL程序的结构和性能。
|
1月前
|
SQL Perl
PL/SQL经典练习
PL/SQL经典练习
13 0
|
1月前
|
SQL Perl
PL/SQL编程基本概念
PL/SQL编程基本概念
13 0
|
1月前
|
SQL Perl
PL/SQL Developer 注册机+汉化包+用户指南
PL/SQL Developer 注册机+汉化包+用户指南
16 0
|
1月前
|
SQL 存储 弹性计算
GaussDB SQL调优:建立合适的索引
GaussDB SQL调优:建立合适的索引
12 0
|
6天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性:数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘,提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态,`%ROWCOUNT`记录处理行数,`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程,提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
|
6天前
|
SQL XML 前端开发
sql 性能优化基于explain调优(二)
sql 性能优化基于explain调优(二)
12 0
|
6天前
|
SQL Oracle 安全
Oracle的PL/SQL循环语句:数据的“旋转木马”与“无限之旅”
【4月更文挑战第19天】Oracle PL/SQL中的循环语句(LOOP、EXIT WHEN、FOR、WHILE)是处理数据的关键工具,用于批量操作、报表生成和复杂业务逻辑。LOOP提供无限循环,可通过EXIT WHEN设定退出条件;FOR循环适用于固定次数迭代,WHILE循环基于条件判断执行。有效使用循环能提高效率,但需注意避免无限循环和优化大数据处理性能。掌握循环语句,将使数据处理更加高效和便捷。
|
6天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL条件控制:数据的“红绿灯”与“分岔路”
【4月更文挑战第19天】在Oracle PL/SQL中,IF语句与CASE语句扮演着数据流程控制的关键角色。IF语句如红绿灯,依据条件决定程序执行路径;ELSE和ELSIF提供多分支逻辑。CASE语句则是分岔路,按表达式值选择执行路径。这些条件控制语句在数据验证、错误处理和业务逻辑中不可或缺,通过巧妙运用能实现高效程序逻辑,保障数据正确流转,支持企业业务发展。理解并熟练掌握这些语句的使用是成为合格数据管理员的重要一环。
|
6天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式:数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式,体验数据的魔法公式。表达式结合常量、变量、运算符和函数,用于数据运算与转换。算术运算符处理数值计算,比较运算符执行数据比较,内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序,助你驾驭数据,揭示其背后的规律与秘密,成为数据魔法师。