oracle存储过程的优化

Question

以下这个存储过程执行比较慢，大家帮忙看看，还能如何优化。
create or replace procedure t_phs_settle(startbilldate         VARCHAR2,endbilldate           VARCHAR2) is
   --strrtn OUT NUMBER,
   sstartbilldate   VARCHAR2 (10) := startbilldate;
   sendbilldate     VARCHAR2 (10) := endbilldate;
   subrate          NUMBER;
   oricarrierid  t_sett_rate.carrier_id%type;
   orirate  t_sett_rate.rate%type;   cursor curSpid is select distinct sp_id from t_sett_rate;
begin
/************************
循环得到SPID
循环里*摊分运营商(取得与sp进行摊分的运营商及比例)
操作对象
T_sett_rate
条件
Sp_id=当前取得的SPID
取得字段
Carrier_id，rate
循环里*原sp运营商(取得sp进行摊分的比例)
操作对象
T_sett_rate
条件
Sp_id=当前取得的SPID
取得字段
1-Sum(rate) rate
循环外*没有配置spid的数据处理
操作对象
T_bill_spe ,T_bill_spe_middle
条件
T_bill_spe_middle.system_id=7
And T_bill_spe_middle.startbilldate=传入参数的开始日期
And T_bill_spe_middle.endbilldate=传入参数的结束日期
And T_bill_spe_middle.spebillno_mid 不在t_bill_spe.billno内
*************************************/
FOR c1 IN curSpid
LOOP   /******************************************************
*摊分运营商
****************************************************/
Select carrier_id,rate Into oricarrierid,orirate From t_sett_rate Where sp_id=c1.sp_id;insert into T_bill_spe(system_id,callingprov,callingarea,calling_entity_id,calling_carrier_id,billing_entity_id,billing_carrier_id,service_id,settletype,payori,paytotalfee,payduration,receiveori,receivetotalfee,payadjust,receiveadjust,receiveduration,billmonth,handleflag,settdate_id,startbilldate,endbilldate,raw_service)
select E.system_id,E.callingprov,E.callingarea,E.calling_entity_id,E.calling_carrier_id,F.entity_id,oricarrierid,E.service_id,E.settletype,E.payori*orirate,E.paytotalfee*orirate,E.payduration*orirate,E.receiveori*orirate,E.receivetotalfee*orirate,E.payadjust*orirate,E.receiveadjust *orirate,E.receiveduration*orirate,E.billmonth,E.handleflag,E.settdate_id,E.startbilldate,E.endbilldate,c1.sp_id||'摊分'
from T_bill_spe_middle E,T_carrier F
where E.raw_service like  c1.sp_id||'%' and F.carrier_id=oricarrierid and E.system_id=7 and E.startbilldate=sstartbilldate and E.endbilldate=sendbilldate;
   /******************************************************
*原SP运营商
****************************************************/
Select (1-sum(rate)) rate Into subrate From t_sett_rate Where sp_id=c1.sp_id;insert into T_bill_spe(billno,system_id,callingprov,callingarea,calling_entity_id,calling_carrier_id,billing_entity_id,billing_carrier_id,service_id,settletype,payori,paytotalfee,payduration,receiveori,receivetotalfee,payadjust,receiveadjust,receiveduration,billmonth,handleflag,settdate_id,startbilldate,endbilldate,raw_service)
select E.spebillno_mid,E.system_id,E.callingprov,E.callingarea,E.calling_entity_id,E.calling_carrier_id,E.billing_entity_id,E.billing_carrier_id,E.service_id,E.settletype,E.payori*subrate,E.paytotalfee*subrate,E.payduration*subrate,E.receiveori*subrate,E.receivetotalfee*subrate,E.payadjust*subrate,E.receiveadjust *subrate,E.receiveduration*subrate,E.billmonth,E.handleflag,E.settdate_id,E.startbilldate,E.endbilldate,E.raw_service                                
from T_bill_spe_middle E
where E.raw_service like c1.sp_id||'%' and E.system_id=7 and E.startbilldate=sstartbilldate and E.endbilldate=sendbilldate;END LOOP;
COMMIT;
   /******************************************************
*没有配置spid的数据处理
****************************************************/
insert into T_bill_spe(billno,system_id,callingprov,callingarea,calling_entity_id,calling_carrier_id,billing_entity_id,billing_carrier_id,service_id,settletype,payori,paytotalfee,payduration,receiveori,receivetotalfee,payadjust,receiveadjust,receiveduration,billmonth,handleflag,settdate_id,startbilldate,endbilldate,raw_service)
--select F.spebillno_mid,F.system_id,F.callingprov,F.callingarea,F.calling_entity_id,F.calling_carrier_id,F.billing_entity_id,F.billing_carrier_id,F.service_id,F.settletype,F.payori,F.paytotalfee,F.payduration,F.receiveori,F.receivetotalfee,F.payadjust,F.receiveadjust,F.receiveduration,F.billmonth,F.handleflag,F.settdate_id,F.startbilldate,F.endbilldate,F.raw_service        
--from T_bill_spe_middle F
--join ((select spebillno_mid from t_bill_spe_middle where system_id=7 and startbilldate=20070101 and endbilldate=20070131)minus(select billno from t_bill_spe)) M
--on F.Spebillno_Mid=M.Spebillno_Mid;
select spebillno_mid,system_id,callingprov,callingarea,calling_entity_id,calling_carrier_id,billing_entity_id,billing_carrier_id,service_id,settletype,payori,paytotalfee,payduration,receiveori,receivetotalfee,payadjust,receiveadjust,receiveduration,billmonth,handleflag,settdate_id,startbilldate,endbilldate,raw_service        
from T_bill_spe_middle
where spebillno_mid in((select spebillno_mid from t_bill_spe_middle where system_id=7 and startbilldate=sstartbilldate and endbilldate=sendbilldate)minus(select billno from t_bill_spe));
--strrtn:=1;
COMMIT;EXCEPTION
 WHEN OTHERS THEN
  ROLLBACK;
--  strrtn:=0;
end t_phs_settle;