ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一次sql优化小记

一次sql优化小记

原创 Linux操作系统 作者:oracle_kai 时间:2008-01-17 08:30:37 0 删除 编辑

 

问题提出:某一应用db服务器迁移到idc后,总部基础资料同步到这台db上整个同步耗时在4个小时以上,用户难以接受,观察到这个现象后,即着手对同步基础资料的存储过程做优化调整下面的调整优化以同步中的某一个大表ka_detail为例,列出优化的步骤和方法,最终的调整结果:过程执行时间由2个小时以上,优化为现在的30秒左右。记录文档,供参考。

 

检查同步过程,发现因业务系统的限制,同步时间主要消耗在本地table和通过dblink的远程table的delete,update,insert上,此外远程table上有trigger,也是一个因素;整个同步时间耗时过长,和数据仓库db每天的停机冷备份冲突,最终可能会导致基础资料还没有同步结束的时候,数据仓库db停机做冷备份,进而导致同步过程失败,影响下游系统的作业。

 

一:查看当前状况下同步ka单店过程执行的时间

14:10:44 SQL> exec syn_fenxiao_ka_detail;

start syn_fenxiao_ka_detail time0 is:20071219 14:14:09

start syn_fenxiao_ka_detail time1 is:20071219 14:14:11

insert and update of ka_detail synchronize is unsuccessful!!!-0008-SUG0632

begin syn_fenxiao_ka_detail_before; end;

ORA-01013: user requested cancel of current operation

16:38:34 SQL>

 

同步过程exec syn_fenxiao_ka_detail 执行了2个多小时还没有完成,手工停止。

 

 

:第一次优化过程,用merge语句代替insert,update 语句

查看ka_detail 表的记录数

SQL> select count(*) from ka_detail;

 

  COUNT(*)

----------

     11822

该表有近12000笔记录,为基础资料中最大的一个表。分析同步该表的过程syn_fenxiao_ka_detail ,过程中的update和insert语句可以用merge 语句代替,只需要一次全表扫描,将会较大程度的提高过程的执行效果(oracle 9i 新增的merge 语句,对于数据仓库中经常用的数据批量导入更新,可以较好的提高sql语句的效率),过程修改后的执行情况如下

10:48:53 SQL> exec syn_fenxiao_ka_detail;

start syn_fenxiao_ka_detail time0 is:20071219 10:52:23

start syn_fenxiao_ka_detail time1 is:20071219 11:29:48

start syn_fenxiao_ka_detail time2 is:20071219 11:29:55

start syn_fenxiao_ka_detail time2 is:20071219 11:29:55

syn_fenxiao_ka_detail synchronize successful!!!

可以看到,第一次优化后的执行时间将在40分钟左右,过程的执行效果有了很大提高

 

三:第二次优化过程

再次分析过程syn_fenxiao_ka_detail,对程序进行分段测试,确定40分钟的耗时在每个子程序段的分布情况(可以采用分段测试,增加dbms_output.put_line,来确定过程执行的瓶颈正在那个子程序段上,或者利用操作系统的topas/top,结合v$session,v$process,v$sqltext ,可以确定耗用资源最多的,占用时间最长的sql语句,对此语句进行优化)。

start syn_fenxiao_ka_detail time0 is:20071219 10:52:23

start syn_fenxiao_ka_detail time1 is:20071219 11:29:48

观察上面的测试输出,找到对应的程序段,可以发现执行时间绝大部分为delete语句。

找到对应的语句,分析该语句的执行计划和统计信息,如下

SQL>  delete ka_detail@lfxpub a where not Exists

  2   (select 'x' from ka_detail b where a.ka_detail=b.ka_detail);

 

已删除0行。

 

 

统计信息

----------------------------------------------------------

      23628  recursive calls

          0  db block gets

      23624  consistent gets

          0  physical reads

          0  redo size

        518  bytes sent via SQL*Net to client

        394  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

consisten  read 非常大,这么大的逻辑读是造成同步耗时的主要原因,应考虑优化(需要参考sql的执行计划)。最终优化后的语句的执行计划和统计信息如下

SQL> delete ka_detail@lfxpub where ka_detail  in

  2  (

  3  select ka_detail from ka_detail@lfxpub

  4  minus

  5  select ka_detail from ka_detail

  6  );

已删除0行。

统计信息

----------------------------------------------------------

          6  recursive calls

          0  db block gets

         93  consistent gets

          0  physical reads

          0  redo size

        532  bytes sent via SQL*Net to client

        414  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

可以看到,优化后sql的consisten read 降低了大概200倍,效率有了极大提高。

 

至此,表ka_detail的同步过程优化结束,其他过程,亦可参照处理。

 

SQL> set serveroutput on;

SQL> set time  on;

14:24:59 SQL> exec syn_pesd_ka_detail;

 

insert and update of ka_detail synchronize successful!!!

 

PL/SQL procedure successfully completed

 

14:25:31 SQL>

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10159839/viewspace-149075/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    175130