针对日终时间较长的问题，做了一下分析，以下是从日终时间段的statspack报告中获取的信息：
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
142,148,687 1 142,148,687.0 11.1 1986.08 6166.47 3208775729
Module: stat_bonus@ECTIP (TNS V1-V3)
call CCB_STAT_CUSBONUS(to_date(:b0,'YYYYMMDD')) into :b1
141,236,813 1 141,236,813.0 11.1 2175.60 5926.47 2278049678
Module: stat_BcLog@ECTIP (TNS V1-V3)
call CCB_CTOH_BC_LOG(to_date(:b0,'YYYYMMDD')) into :b1
100,595,351 1 100,595,351.0 7.9 1496.40 1543.58 1889229293
Module: stat_bonus@ECTIP (TNS V1-V3)
UPDATE BC_CUSBONUS A SET A.MODI_DATE = :B1 , A.BONUS = ( A.BONUS
+ NVL ((SELECT SUM (NVL (B.AWARD, 0) * DECODE (B.FLAG,'0', 1,'1
',-1,0)) FROM BM_LOG_AWARD B WHERE A.USERID = B.USERID AND A.BRA
NCH_CODE = B.BRANCH_CODE AND B.STATUS = '1' AND B.TRAN_DATE >= :
B1 AND B.TRAN_DATE < :B2 GROUP BY B.USERID, B.BRANCH_CODE), 0 )
34,733,395 2,378,691 14.6 2.7 738.46 2185.12 1487635544
Module: stat_BcLog@ECTIP (TNS V1-V3)
INSERT INTO BC_LOG_HIS (CREDIT_NO,PTXCODE,LOG_CODE,TXCODE,TRAN_D
ATE,BRANCH_CODE,USERID, IP_ADD,LOG_STR,ERR_CODE,ERR_MSG,STATUS,D
AC,OID,TRAN_MONTH) VALUES ( :B15 ,:B14 ,:B13 ,:B12 ,:B11 ,:B10 ,
:B9 , :B8 ,:B7 ,:B6 ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 )
其中INSERT INTO BC_LOG_HIS。。。这条sql执行了2,378,691次，耗时2185.12秒，查看耗时最长的2个函数，发现这条sql是在函数CCB_CTOH_BC_LOG里执行的，
这个函数的功能是将bc_log表里的数据插入到bc_log_his中，并删除bc_log中的记录，在该函数内容如下：
CREATE OR REPLACE FUNCTION "CCB_CTOH_BC_LOG" (statDate DATE) RETURN NUMBER IS
/*
** BC_LOG倒数到BC_LOG_HIS分段提交
*/
nNum NUMBER;
nCommitNum CONSTANT NUMBER :=100000 ;
v_error_code NUMBER; --error code
v_error_message VARCHAR2(255);--error message
v_next_day date;
v_halfmonth_day date;
v_seven_day date;
------------------------------------------------------------------
v_CREDIT_NO BC_LOG.CREDIT_NO%TYPE;
v_PTXCODE BC_LOG.PTXCODE%TYPE;
v_LOG_CODE BC_LOG.LOG_CODE%TYPE;
v_TXCODE BC_LOG.TXCODE%TYPE;
v_TRAN_DATE BC_LOG.TRAN_DATE%TYPE;
v_BRANCH_CODE BC_LOG.BRANCH_CODE%TYPE;
v_USERID BC_LOG.USERID%TYPE;
v_IP_ADD BC_LOG.IP_ADD%TYPE;
v_LOG_STR BC_LOG.LOG_STR%TYPE;
v_ERR_CODE BC_LOG.ERR_CODE%TYPE;
v_ERR_MSG BC_LOG.ERR_MSG%TYPE;
v_STATUS BC_LOG.STATUS%TYPE;
v_DAC BC_LOG.DAC%TYPE;
v_OID BC_LOG.OID%TYPE;
v_TRAN_MONTH NUMBER;
------------------------------------------------------------------
CURSOR ccb_trans_C IS -- b2c cursor
SELECT
CREDIT_NO,PTXCODE,LOG_CODE,TXCODE,TRAN_DATE,BRANCH_CODE,USERID,
IP_ADD,LOG_STR,ERR_CODE,ERR_MSG,STATUS,DAC,OID,to_char(tran_date,'mm')
FROM BC_LOG
WHERE tran_date
------------------------------------------------------------------
------------------------------------------------------------------
BEGIN
LOOP
--
nNum := 0;
OPEN ccb_trans_C;
LOOP
FETCH ccb_trans_C INTO
v_CREDIT_NO,v_PTXCODE,v_LOG_CODE,v_TXCODE,v_TRAN_DATE,v_BRANCH_CODE,v_USERID,
v_IP_ADD,v_LOG_STR,v_ERR_CODE,v_ERR_MSG,v_STATUS,v_DAC,v_OID,v_TRAN_MONTH;
EXIT WHEN ccb_trans_C%NOTFOUND;
INSERT INTO BC_LOG_HIS
(CREDIT_NO,PTXCODE,LOG_CODE,TXCODE,TRAN_DATE,BRANCH_CODE,USERID,
IP_ADD,LOG_STR,ERR_CODE,ERR_MSG,STATUS,DAC,OID,TRAN_MONTH)
VALUES
(
v_CREDIT_NO,v_PTXCODE,v_LOG_CODE,v_TXCODE,v_TRAN_DATE,v_BRANCH_CODE,v_USERID,
v_IP_ADD,v_LOG_STR,v_ERR_CODE,v_ERR_MSG,v_STATUS,v_DAC,v_OID,v_TRAN_MONTH
);
DELETE FROM BC_LOG WHERE CURRENT OF ccb_trans_C ;
nNum := nNum + 1;
END LOOP;
CLOSE ccb_trans_C;
COMMIT;
EXIT WHEN nNum=0 OR nNum< nCommitNum - 1;
--
END LOOP;
COMMIT;
RETURN (1);
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
ROLLBACK;
if ccb_trans_C%isopen then
close ccb_trans_C;
end if;
INSERT INTO BM_PROC_ERR(func_name,err_stat,err_time,err_table,err_msg,err_code) VALUES ('CCB_CTOH_BC_LOG',' ',SYSDATE,'BC_LOG_HIS',v_error_message,v_error_code) ;
COMMIT;
RETURN (0);
END CCB_CTOH_BC_LOG;
可以看出，执行insert和delete的操作时，是采用循环的方式逐条实现，并批量提交。这相当于对于每条符合条件的记录，执行一次insert和delete操作，这种方式的效率比较低下。
测试如下：
SQL> insert into test1 select * from bc_log_bak where rownum<10000;
9999 rows created.
SQL> commit;
Commit complete.
SQL> insert into test2 select * from bc_log_bak where rownum<10000;
9999 rows created.
SQL> commit;
Commit complete.
SQL> set timing on
SQL> delete from test1 ;
9999 rows deleted.
Elapsed: 00:00:00.43
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> begin
2 for i in 1 .. 10000 loop
3 delete from test2 where rownum<2;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.75
可见，采用逐条删除的方式消耗的时间明显长于一次性删除。
如果开发人员能优化一下日终处理程序，那么耗时会有效的降低。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-469950/ ，如需转载，请注明出处，否则将追究法律责任。
