pl/sql存储过程优化一例

问题提出：

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      

 

Oracle9i，10g的sql优化器优先采用cbo方式，cbo方式下，oracle优化器可以更智能更合理的选择执行计划，从而使sql的执行效率更高，而若相关table和index没有统计信息的话，则会转而采用传统的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>                                                  

 

可以试验比对一下，采用rbo和cbo的执行效率对比情况

10:41:43 SQL> exec qmsressummt0_test;

PL/SQL procedure successfully completed

11:03:37 SQL>

 

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

 

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

 

分析procedure和table，相关的表需要建立索引，这一步需要根据以往的经验，建立相关索引（开发人员并没有考虑新增索引，甚至主键都没有），并抽取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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10159839/viewspace-154887/