ITPub博客

首页 > Linux操作系统 > Linux操作系统 > pl/sql存储过程优化一例

pl/sql存储过程优化一例

原创 Linux操作系统 作者:oracle_kai 时间:2008-01-21 09:06:40 0 删除 编辑

问题提出:

etl中有一过程qmsressummt0_test,用来抽取各地xxx应用的资料,目前处于测试阶段,暂时存放2个厂区一个月的测试资料,目前该过程执行一次要30分钟,按此状况,日后如再抽取其他厂区的资料,预计执行三个小时以上,小组开发人员请求协助优化。

 

优化前的执行情况

9:31:20 SQL> set serveroutput on;

9:31:25 SQL> exec qmsressummt0_test;

 

PL/SQL procedure successfully completed

10:00:54 SQL>

执行一次,耗时在30分钟

调优步骤:

步骤一:调整sql的优化器由rbo变为采用cbo方式

 

查看procedure涉及到table的统计信息三个表都没有统计信息

10:33:35 SQL>  select * from user_tables where table_name in ('QMS310T0','QMS311T0',UPPER('qmsressummt0'));

 

TABLE_NAME      TABLESPACE_NAME    NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE

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

QMS310T0                       QMSS                                                          

QMS311T0                       QMSS                                                           

QMSRESSUMMT0                   QMSS      

 

Oracle9i10gsql优化器优先采用cbo方式,cbo方式下,oracle优化器可以更智能更合理的选择执行计划,从而使sql的执行效率更高,而若相关tableindex没有统计信息的话,则会转而采用传统的rbo方式,在此例子中,任意查看过程中的某个sql,发现都是采用rbo

 

接下来分析该过程涉及到的三个表,收集相关统计信息。

 

对这三个表做分析,收集统计信息

 

10:00:54 SQL> analyze table qms310t0 compute statistics for table for all indexes;

Table analyzed

10:33:11 SQL> analyze table qms311t0 compute statistics for table for all indexes;

Table analyzed

10:33:21 SQL> analyze table qmsressummt0 compute statistics for table for all indexes;

Table analyzed

10:33:35 SQL>                                                  

 

可以试验比对一下,采用rbocbo的执行效率对比情况

10:41:43 SQL> exec qmsressummt0_test;

PL/SQL procedure successfully completed

11:03:37 SQL>

 

可以发现,现在的执行时间在20分钟,在此过程中,效率提高了接近1/3,由此,我们可以看到相同的sql语句,优化器采用cbo比采用rbo效率要提高不少。

 

步骤二:合理利用索引,进一步提高sql执行效率

 

分析proceduretable,相关的表需要建立索引,这一步需要根据以往的经验,建立相关索引(开发人员并没有考虑新增索引,甚至主键都没有),并抽取sql语句,查看索引添加前后的执行计划,最终建立的索引(主键索引)如下

SQL> alter table QMS310t0

add constraint PK1_QMS310T0 primary key (BRANCH_NO, CHECK_NO,vend_id,mtrl_no)

Using Index tablespace indx;

Table altered

SQL> alter table QMS311t0

     add constraint PK1_QMS311T0 primary key (BRANCH_NO, CHECK_NO, SAMPL_NO,mtrl_no, CHK_NO)

     Using Index tablespace indx  ;

Table altered

 

SQL>alter table qmsressummt0 add constraint pk_qmsresssummt0 primary key (yyyymm,branch_no,vend_id,mtrl_no,chk_no)  using index tablespace indx;

Table altered

 

SQL> create index idx_qms310t0_ymd on qms310t0(yymmdd) tablespace indx;

Index created

 

 

 

再次执行过程

11:43:51 SQL> exec qmsressummt0_test;

PL/SQL procedure successfully completed

11:49:34 SQL>

 

时间大约为6分钟。

 

步骤三:合理的改变业务处理方式,亦可提高程序执行效率

对该过程的深入分析可以发现,其中的max 最大值,min 最小值,和count(*) 三个sql语句可以合并成一个sql语句,这样同样可以达到减少sql的执行次数和数据块的io数量,提高效率和降低执行时间的目的。

 

 

修改前的sql语句                     |         修改后的sql语句

                                    

select max(b.item_val)                    |  select max(b.item_val),min(b.item_val),Count(*)                                              
      into v_chk_max                      |        into v_chk_max,v_chk_min,v_cnt_01
      from qms310t0 a, qms311t0 b         |    from qms310t0 a, qms311t0 b
     where a.check_no = b.check_no        |    where a.check_no = b.check_no
       and a.branch_no = b.branch_no      |      and a.branch_no = b.branch_no
       and a.mtrl_no = r1.mtrl_no         |      and a.mtrl_no = r1.mtrl_no
       and a.vend_id = r1.vend_id         |      and a.vend_id = r1.vend_id
       and a.branch_no = r1.branch_no     |      and a.branch_no = r1.branch_no
       and b.chk_no = r1.chk_no;          |      and b.chk_no = r1.chk_no;

                                          |

select min(b.item_val)                |
      into v_chk_min                      |
      from qms310t0 a, qms311t0 b         |
     where a.check_no = b.check_no        |
       and a.branch_no = b.branch_no      |
       and a.mtrl_no = r1.mtrl_no         |
       and a.vend_id = r1.vend_id         |
       and a.branch_no = r1.branch_no     |
       and b.chk_no = r1.chk_no;          |
 
    SELECT count(*)                       |
      INTO v_cnt_01                       |
      FROM qms311t0 a, qms310t0 b         |
     WHERE a.branch_no = r1.branch_no     |
       AND a.mtrl_no = r1.mtrl_no         |
       AND a.chk_no = r1.chk_no           |
       and a.check_no = b.check_no        |
       and a.branch_no = b.branch_no;     |

 

执行的情况:

15:31:13 SQL> exec qmsressummt0_test

PL/SQL procedure successfully completed

15:33:42 SQL>

 

最终,该过程执行的时间<3分钟

 

至此,程序执行效率提高10倍左右,调整优化结束。

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

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

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    173493