问题提出：
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数量，提高效率和降低执行时间的目的。
把3次的查询合成在一个语句。
修改前的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倍左右，调整优化结束。
如何需要，还可以根据需要创建函数索引。
来源：http://space.itpub.net/10159839/viewspace-154887
