oracle执行存储过程时有时候会超时，平时不会

[baidu_16106355]

求大神指点，不明白会超时的原因，因为一般会正常运行
[15-3-23 10:18:10:308 CST] 00000052 WorkflowsCmd I 执行任务【基础层到高级层】开始
[15-3-23 10:18:30:346 CST]00000052 WorkflowsCmd I 执行任务【基础层到高级层】成功
以上为正常
[15-4-1 10:17:21:038 CST] 00000052 WorkflowsCmd I 执行任务【基础层到高级层】开始
[15-4-1 10:29:11:957 CST] 00004bb0 ThreadMonitor W WSVR0605W: 线程“WebContainer : 10”（00000052）已保持活动状态 739067 毫秒，此线程可能已挂起。在服务器中共有 1 个线程可能处于挂起状态。
以上为超时
下面是代码，代码相同为什么执行时会出现不同结果

procedure BASIC_TO_ADVANCE(v_i out int, fileDate in varchar2) is

begin

merge into advance_loanback a
using
(
select
b.accnum ACCNUM,
b.loanbalance LOANBALANCE,
b.highestbalance HIGHESTBALANCE,
b.remainmonth REMAINMONTH,
b.backdate BACKDATE,
b.lastdate LASTDATE,
b.nowback NOWBACK,
b.factback FACTBACK,
case
when
to_date(d.bois_maturity_date,'yyyymmdd')=to_date(fileDate,'yyyymmdd')+1
and i.curovelimit=0 and d.curovelimit>0 /*and b.nowback=0*/
then to_number(nvl(c.ove,0))
else
decode(sign(b.curovetime-nvl(c.ove,0)),1,b.curovetime,-1, nvl(c.ove,0),b.curovetime)
end OVERTIME,
-- b.overtime OVERTIME,

case
when
to_date(d.bois_maturity_date,'yyyymmdd')=to_date(fileDate,'yyyymmdd')+1
and i.curovelimit=0 and d.curovelimit>0 /*and b.nowback=0*/
then 0
else
b.curovetime
end CUROVETIME,
case
when
to_date(d.bois_maturity_date,'yyyymmdd')=to_date(fileDate,'yyyymmdd')+1
and i.curovelimit=0 and d.curovelimit>0 /*and b.nowback=0*/
then 0
else
b.curovelimit
end CUROVELIMIT,
case
when
to_date(fileDate,'yyyymmdd')>to_date(d.bois_maturity_date,'yyyymmdd')
then to_number(c.renegetimes)
when
to_date(d.bois_maturity_date,'yyyymmdd')=to_date(fileDate,'yyyymmdd')+1
and i.curovelimit=0 and d.curovelimit>0 /*and b.nowback=0*/
then
to_number(nvl(c.renegetimes,0))

when f.interval='04' and mod(substr(fileDate,1,6)-substr(d.bois_last_unpaid_due_date,1,6),3)=0 and b.curovetime>0
then nvl(c.renegetimes,0)+1
else
decode(sign(b.curovetime),1,nvl(c.renegetimes,0)+1,nvl(c.renegetimes,0))
end RENEGETIMES,

case
when d.bois_current_bue_money = 0
then 0
when
(d.accountstate='52'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>30
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))<61)
or
(f.back_plan = 'B'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>30
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))<61)
then
to_number(d.bois_current_bue_money)
else
sum(
case
when
f.back_plan in ('M','G') and d.accountstate!='52'
and (to_date(b.collect_date,'yyyymmdd')-to_date(g.based_to_date2,'yyyymmdd')>30 and to_date(b.collect_date,'yyyymmdd')-to_date(g.based_to_date2,'yyyymmdd')<61)
then
g.txn_amt2-g.collected_amt2
else
0
end
)
end padpd1,

case
when d.bois_current_bue_money = 0
then 0
when
(d.accountstate='52'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>60
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))<91)
or
(f.back_plan = 'B'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>60
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))<91)
then
to_number(d.bois_current_bue_money)
else
sum(
case
when
f.back_plan in ('M','G') and d.accountstate!='52'
and (to_date(b.collect_date,'yyyymmdd')-to_date(g.based_to_date2,'yyyymmdd')>60 and to_date(b.collect_date,'yyyymmdd')-to_date(g.based_to_date2,'yyyymmdd')<91)
then
g.txn_amt2-g.collected_amt2
else
0
end)
end padpd2,

case
when d.bois_current_bue_money = 0
then 0
when
(d.accountstate='52'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>90
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))<181)
or
(f.back_plan = 'B'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>90
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))<181)
then
to_number(d.bois_current_bue_money)
else
sum(
case
when
f.back_plan in ('M','G') and d.accountstate!='52'
and (to_date(b.collect_date,'yyyymmdd')-to_date(g.based_to_date2,'yyyymmdd')>90 and to_date(b.collect_date,'yyyymmdd')-to_date(g.based_to_date2,'yyyymmdd')<181)
then
g.txn_amt2-g.collected_amt2
else
0
end
)
end padpd3,

case
when d.bois_current_bue_money = 0
then 0
when
(d.accountstate='52'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>180)
or
(f.back_plan = 'B'
and (to_date(d.file_date,'yyyymmdd') - to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))>180)
then
to_number(d.bois_current_bue_money)
else
sum
(case
when
f.back_plan in ('M','G') and d.accountstate!='52'
and (to_date(b.collect_date,'yyyymmdd')-to_date(g.based_to_date2,'yyyymmdd')>180)
then
g.txn_amt2-g.collected_amt2
else
0
end)
end padpd4,

case
when
b.loanbalance=0 and b.curovelimit=0
then '3'
when
(d.accountstate='52' and (d.nowback-d.loanbalance)=0)

or
(to_date(d.bois_maturity_date,'yyyymmdd')=to_date(fileDate,'yyyymmdd')+1
and i.curovelimit=0 and d.curovelimit>0)
then '1'
when
e.coreaccstate = '4'
or
(d.accountstate='52' and (d.nowback-d.loanbalance)>0)

or
(d.accountstate='54' and d.curovelimit>0)
then '2'
when f.opendate = fileDate
then '1'
else
e.coreaccstate
end accountstate,


case
when f.opendate=fileDate
and b.factback!=0
and b.loanbalance=0
and b.loanbalance=0
then '///////////////////////C'

when
to_date(d.bois_maturity_date,'yyyymmdd')=to_date(fileDate,'yyyymmdd')+1
and i.curovelimit=0 and d.curovelimit>0 and b.factback=0
then
nvl(substr(c.backstatein24,2,23),'///////////////////////')||'*'

when
to_date(d.bois_maturity_date,'yyyymmdd')=to_date(fileDate,'yyyymmdd')+1
and i.curovelimit=0 and d.curovelimit>0 and b.factback>0
then
nvl(substr(c.backstatein24,2,23),'///////////////////////')||'N'


when
(b.loanbalance!=0 and b.curovelimit=0
and b.factback!=0)
or
(d.accountstate='52' and (d.nowback-d.loanbalance)=0 and d.loanbalance!=0
)
then
nvl(substr(c.backstatein24,2,23),'///////////////////////')||'N'

when
substr(c.backstatein24,-1) in ('1','2','3','4','5','6','7')
and to_number(d.curovelimit)!=0 and d.accountstate = '52'
then substr(c.backstatein24,2,23)||decode(to_number(substr(c.backstatein24,-1)),1,2,2,3,3,4,4,5,5,6,6,7,7)

when
substr(c.backstatein24,-1) in ('1','2','3','4','5','6','7')
and to_number(d.curovelimit)!=0
then substr(c.backstatein24,2,23)||decode((sign(ceil((to_date(fileDate,'yyyymmdd')-to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))/30)-7)),-1,decode(ceil((to_date(fileDate,'yyyymmdd')-to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))/30),0,1,ceil((to_date(fileDate,'yyyymmdd')-to_date(d.bois_last_unpaid_due_date,'yyyymmdd'))/30)),1,7,7)


when
substr(c.backstatein24,-1) in ('N','*')
and to_number(b.curovelimit)!=0
and substr(fileDate,1,6)>substr(c.data_date,1,6)
then
substr(c.backstatein24,2,23)||'1'
when
substr(c.backstatein24,-1) in ('N','*')
and to_number(b.curovelimit)!=0
and substr(fileDate,1,6)=substr(c.data_date,1,6)
then
substr(c.backstatein24,1,23)||'1'

when
b.loanbalance !=0
and b.curovelimit=0
and b.factback=0
then
nvl(substr(c.backstatein24,2,23),'///////////////////////')||'*'

when
b.curovelimit=0 and b.loanbalance=0 and substr(fileDate,1,6)=substr(c.data_date,1,6)
then
nvl(substr(c.backstatein24,1,23),'///////////////////////')||'C'
when
(b.curovelimit=0 and b.loanbalance=0 and substr(fileDate,1,6)>substr(c.data_date,1,6))
or
(b.curovelimit=0 and b.loanbalance=0 and c.data_date is null)
then
nvl(substr(c.backstatein24,2,23),'///////////////////////')||'C'

else '////////////////////////'
end backstatein24,

'2' data_from,

'' KZ1,

fileDate collect_date

from basic_loanback b

[baidu_16106355]

left join (select m.accnum,m.overtime ove,m.renegetimes,m.backstatein24,m.data_date from message_base_seg_info_history m right join( select accnum,max(data_date) data_date from message_base_seg_info_history group by accnum ) n on m.accnum = n.accnum and m.data_date = n.data_date ) c on b.accnum = c.accnum left join perloannmain_accnum d on b.accnum = d.accnum left join perloannmain_bef_accnum i on b.accnum = i.accnum left join d_coreaccstatemap e on d.accountstate = e.accstate left join m_loanopen f on b.accnum = f.accnum left join rpyv_accnum g on b.accnum = g.accnum where b.data_from = '2' group by b.ACCNUM, b.LOANBALANCE, b.HIGHESTBALANCE, b.REMAINMONTH, b.BACKDATE, b.LASTDATE, b.NOWBACK, b.FACTBACK, b.CUROVETIME, b.CUROVELIMIT, c.ove, RENEGETIMES, d.ACCOUNTSTATE, BACKSTATEIN24, b.DATA_FROM, b.KZ1, b.COLLECT_DATE, d.curovelimit, d.nowback, d.loanbalance, e.coreaccstate, c.data_date, d.file_date, f.opendate, d.bois_maturity_date, i.curovelimit, d.bois_last_unpaid_due_date, d.bois_current_bue_money, f.back_plan, f.interval ) i on (a.accnum = i.accnum) when matched then update set a.LOANBALANCE =i.LOANBALANCE , a.HIGHESTBALANCE=i.HIGHESTBALANCE, a.REMAINMONTH =i.REMAINMONTH , a.BACKDATE =i.BACKDATE , a.LASTDATE =i.LASTDATE , a.NOWBACK =i.NOWBACK , a.FACTBACK =i.FACTBACK , a.OVERTIME =i.OVERTIME , a.CUROVETIME =i.CUROVETIME , a.CUROVELIMIT =i.CUROVELIMIT , a.RENEGETIMES =i.RENEGETIMES , a.PADPD1 =i.PADPD1 , a.PADPD2 =i.PADPD2 , a.PADPD3 =i.PADPD3 , a.PADPD4 =i.PADPD4 , a.ACCOUNTSTATE =i.ACCOUNTSTATE , a.BACKSTATEIN24 =i.BACKSTATEIN24 , a.DATA_FROM =i.DATA_FROM , a.KZ1 =i.KZ1 , a.COLLECT_DATE =i.COLLECT_DATE when not matched then insert ( a.ACCNUM , a.LOANBALANCE , a.HIGHESTBALANCE , a.REMAINMONTH , a.BACKDATE , a.LASTDATE , a.NOWBACK , a.FACTBACK , a.OVERTIME , a.CUROVETIME , a.CUROVELIMIT , a.RENEGETIMES , a.PADPD1 , a.PADPD2 , a.PADPD3 , a.PADPD4 , a.ACCOUNTSTATE , a.BACKSTATEIN24 , a.DATA_FROM , a.KZ1 , a.COLLECT_DATE ) values ( i.ACCNUM , i.LOANBALANCE , i.HIGHESTBALANCE , i.REMAINMONTH , i.BACKDATE , i.LASTDATE , i.NOWBACK , i.FACTBACK , i.OVERTIME , i.CUROVETIME , i.CUROVELIMIT , i.RENEGETIMES , i.PADPD1 , i.PADPD2 , i.PADPD3 , i.PADPD4 , i.ACCOUNTSTATE , i.BACKSTATEIN24 , i.DATA_FROM , i.KZ1 , i.COLLECT_DATE ) ; begin commit; exception when others then dbms_output.put_line('ERROR'); rollback; end; delete from advance_loanback where substr(backstatein24,-2) = 'CC'; compute_padpd(fileDate); begin commit; exception when others then dbms_output.put_line('ERROR'); rollback; end; end BASIC_TO_ADVANCE;