现网一后台报表程序如今运行越来越慢，目前竟然需22个小时才能搞定！程序是外包的兄弟开发的，涉及到的性能问题最终找到了我！

[@more@]

原来这个程序的运行机制其实很简单，从数据库中找到数据转换成.txt格式的文件输出，对应的数据库操作语句如下：
SQL> select AcctId,
2 SubsId,
3 Acctstatus,
4 to_char(OpenTime, 'YYYYMMDDHH24MISS'),
5 1,
6 QuotaOrder,
7 to_char(ModifyTime, 'YYYYMMDDHH24MISS'),
8 to_char(EXPIRTIME, 'YYYYMMDD'),
9 nb,
10 fb
11 from (select a.AcctId,
12 b.SubsId,
13 a.Acctstatus,
14 a.OpenTime,
15 1,
16 a.QuotaOrder,
17 b.ModifyTime,
18 a.EXPIRTIME,
19 c.balance-c.freezebalance as nb,
20 c.freezebalance as fb,
21 (row_number() over(order by a.AcctId desc)) RowNumber
22 from T_ACCTINFO a, T_USERACCT b, t_acctbook c
23 where a.AcctId = b.AcctId
24 and a.acctid = c.acctid
25 and to_char(b.ModifyTime, 'YYYYMMDD') <= to_char(sysdate -
1, 'YYYYMMDD'))
26 where RowNumber > :nSequence and RowNumber <= :nSequence + :nMaxNum");
该语句的执行计划如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4887 Card=60112 Byte
s=6612320)

1 0 VIEW (Cost=4887 Card=60112 Bytes=6612320)
2 1 WINDOW (SORT PUSHED RANK) (Cost=4887 Card=60112 Bytes=51
09520)

3 2 HASH JOIN (Cost=4077 Card=60112 Bytes=5109520)
4 3 HASH JOIN (Cost=2564 Card=60112 Bytes=3967392)
5 4 TABLE ACCESS (FULL) OF 'T_USERACCT' (Cost=825 Card
=60112 Bytes=1983696)

6 4 TABLE ACCESS (FULL) OF 'T_ACCTINFO' (Cost=875 Card
=1202272 Bytes=39674976)

7 3 TABLE ACCESS (FULL) OF 'T_ACCTBOOK' (Cost=875 Card=1
202263 Bytes=22842997)
可发现，涉及到的三表完全走的是全表扫描，并且这三表的数据量都在100多万之上，难怪耗时如此之久。
如下是具体的优化步骤：
1、先创建临时表t_tmp_report（设置该表为nologging）和对应的索引（RowNumber列上），从该表中出txt文件；
2、每次生成txt文件前先truncate掉该临时表；
3、执行如下的direct load载入操作：
insert /*+ append */ into t_tmp_report
select /*+rule*/ a.AcctId,
b.SubsId,
a.Acctstatus,
a.OpenTime,
1 as aa,
a.QuotaOrder,
b.ModifyTime,
a.EXPIRTIME,
c.balance-c.freezebalance as nb,
c.freezebalance as fb,
(row_number() over(order by a.AcctId desc)) RowNumber
from T_ACCTINFO a, T_USERACCT b, t_acctbook c
where a.AcctId = b.AcctId
and a.acctid = c.acctid
and to_char(b.ModifyTime, 'YYYYMMDD') <= to_char(sysdate - 1, 'YYYYMMDD');
4、根据rownumber索引从该表中扫描每条数据生成报表。见如下的语句：
select AcctId, SubsId, Acctstatus, to_char(OpenTime, 'YYYYMMDDHH24MISS'), 1, QuotaOrder, to_char(ModifyTime, 'YYYYMMDDHH24MISS'), to_char(EXPIRTIME, 'YYYYMMDD'), nb, fb
from t_tmp_report
where RowNumber > :nSequence and RowNumber <= :nSequence + :nMaxNum");

结论：
经过测试，在采用中间表方案能够大大提升报表的速度。
第一步：清空临时表，速度很快，不到10秒就完成；
第二步：从三个表向临时表中插数据，120万的数据仅需4分钟；
第三步：从临时表查数据生成报表，需要3分钟。
总共耗时加起来不超过15分钟（PK一下22小时），优化效果是惊人的！