一次成功的優化案例

現網一後臺報表程式如今執行越來越慢，目前竟然需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小時），優化效果是驚人的！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/38542/viewspace-921562/，如需轉載，請註明出處，否則將追究法律責任。