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倍左右，调整优化结束。