使用bulk collect插入数据，效率很低，求助大神解决

Question

create or replace procedure bill_import_fj
as
 v_bill_id              number(8);
-- v_acct_id              number(9);
-- v_serv_id              number(9);
-- is_exist_flag          number(4);
 v_sql                  varchar2(2048);
 v_region_code          number(4);
 v_item_id              number(9); cursor mycur is select acct_id,serv_id,svc_type,sys_svc_type,region_id,subject_id,subject_fee,start_date,end_date,status from OCS_P_BILL
 where status = 0 and acct_id > 0 and serv_id > 0 order by acct_id; TYPE ROW_TYPE IS TABLE OF mycur%ROWTYPE INDEX BY BINARY_INTEGER;
 arrays ROW_TYPE;begin 
open mycur;
loop 
---优化2012-11-16
---fetch mycur into v_acct_id,v_serv_id;
---当表里的数据少于10000时，则没用
fetch mycur bulk collect into arrays limit 1000;
  exit when mycur%notfound;
  
----------检验关系是否存在--------------  
     ---------------导入替换关系-------------
     --for l_r in (
     ----    select acct_id,serv_id,svc_type,sys_svc_type,region_id,subject_id,subject_fee,to_number(to_char(start_date,'yyyymmdd')) as start_date ,to_number(to_char(end_date,'yyyymmdd')) as end_date,status from OCS_P_BILL 
    -- select acct_id,serv_id,svc_type,sys_svc_type,region_id,subject_id,subject_fee,start_date,end_date,status from OCS_P_BILL 
    --     where acct_id = v_acct_id and serv_id = v_serv_id 
   --  ) loop 
       for l_r in arrays.FIRST .. arrays.LAST loop 
     
     select nvl(max(a.bill_id),0)+1 into v_bill_id FROM bill a,billdetail b where a.bill_id = b.bill_id ;
     dbms_output.put_line('bill_id = ' || v_bill_id);
 
      ----在这里添加上区号转换关系---
      --A  0591 福州
     --B  0592 厦门
     --C  0595 泉州
     --D  0596 漳州
     --E  0593 宁德
     --F  0594 莆田
     --G  0599 南平
     --H  0598 三明
     --I  0597 龙岩
     --Z  0590 福建省
     
     select decode(''||arrays(l_r).region_id||'','A',591,'B',592,'C',595,'D',596,'E',593,'F',594,'G',598,'H',599,'I',597,'0') into v_region_code
     from dual;
     dbms_output.put_line('region_code = ' || v_region_code);
     
     ----在这里添加上科目转换关系---
     select max(strategy_id) into v_item_id from item_codeocs2bss  t where t.subject_id = arrays(l_r).subject_id ;     --------插入账单数据--------
     v_sql :='insert /*+ append */ into bill (acct_id,serv_id,bill_id,start_date1,end_date1,start_date2,end_date2,region_code,waif_flag)' ||
            'values('||arrays(l_r).acct_id||','||arrays(l_r).serv_id||','||v_bill_id||','||arrays(l_r).start_date||','||arrays(l_r).end_date||',0,0,'||v_region_code||',0)';       
     
     Importbill(v_sql,'autocfg');
     
     v_sql :='insert /*+ append */ into billdetail (bill_id,item_id,primal_fee,cdr_discount,discount_fee,primal_fee1,cdr_discount1,discount_fee1,primal_fee2,cdr_discount2,discount_fee2)' || 
              'values('||v_bill_id||','||v_item_id||',0,0,0,'||arrays(l_r).subject_fee||',0,0,0,0,0)';
              
     Importbill(v_sql,'autocfg');         
          
     update  OCS_P_BILL set status = 1 where acct_id  = arrays(l_r).acct_id and serv_id = arrays(l_r).serv_id ;
       
    commit;
   end loop;
end loop;
    commit;
    
close mycur;
exception
   when others then
   ---  dbms_output.put_line(to_char(pp));
     dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD hh:mm:ss')||'sqlcode: '||to_char(sqlcode)||' sqlerrm: '||sqlerrm);
     rollback;end bill_import_fj ;
其中OCS_P_BILL表的数据大概在800W条左右。求大神们给予优化方法，谢谢 ~~ 急啊

Answer 1

我的建议是：
部分sql放在循环外面，程序尽量用上绑定变量

Answer 2

create or replace procedure bill_import_fj
 as
  v_bill_id              number(8);
 -- v_acct_id              number(9);
 -- v_serv_id              number(9);
 -- is_exist_flag          number(4);
  v_sql                  varchar2(2048);
  v_region_code          number(4);
  v_item_id              number(9);
 
 cursor mycur is select acct_id,serv_id,svc_type,sys_svc_type,region_id,subject_id,subject_fee,start_date,end_date,status from OCS_P_BILL
  where status = 0 and acct_id > 0 and serv_id > 0 order by acct_id;
 
 TYPE ROW_TYPE IS TABLE OF mycur%ROWTYPE INDEX BY BINARY_INTEGER;
  arrays ROW_TYPE;
 
begin 
  select nvl(max(a.bill_id),0)+1 into v_bill_id FROM bill a,billdetail b where a.bill_id = b.bill_id ;
      dbms_output.put_line('bill_id = ' || v_bill_id);
  
       ----在这里添加上区号转换关系---
       --A  0591 福州
      --B  0592 厦门
      --C  0595 泉州
      --D  0596 漳州
      --E  0593 宁德
      --F  0594 莆田
      --G  0599 南平
      --H  0598 三明
      --I  0597 龙岩
      --Z  0590 福建省
      
  select decode(''||arrays(l_r).region_id||'','A',591,'B',592,'C',595,'D',596,'E',593,'F',594,'G',598,'H',599,'I',597,'0') into v_region_code
  from dual;
  dbms_output.put_line('region_code = ' || v_region_code);
open mycur;
 loop 
 ---优化2012-11-16
 ---fetch mycur into v_acct_id,v_serv_id;
 ---当表里的数据少于10000时，则没用
 fetch mycur bulk collect into arrays limit 1000;
   exit when mycur%notfound;
 end loop;
 ----------检验关系是否存在--------------  
      ---------------导入替换关系-------------
      --for l_r in (
      ----    select acct_id,serv_id,svc_type,sys_svc_type,region_id,subject_id,subject_fee,to_number(to_char(start_date,'yyyymmdd')) as start_date ,to_number(to_char(end_date,'yyyymmdd')) as end_date,status from OCS_P_BILL 
     -- select acct_id,serv_id,svc_type,sys_svc_type,region_id,subject_id,subject_fee,start_date,end_date,status from OCS_P_BILL 
     --     where acct_id = v_acct_id and serv_id = v_serv_id 
    --  ) loop 
    for l_r in arrays.FIRST .. arrays.LAST loop 
      ----在这里添加上科目转换关系---
      select max(strategy_id) into v_item_id from item_codeocs2bss  t where t.subject_id = arrays(l_r).subject_id ;
 
     --------插入账单数据--------
      v_sql :='insert /*+ append */ into bill (acct_id,serv_id,bill_id,start_date1,end_date1,start_date2,end_date2,region_code,waif_flag)' ||
             'values('||arrays(l_r).acct_id||','||arrays(l_r).serv_id||','||v_bill_id||','||arrays(l_r).start_date||','||arrays(l_r).end_date||',0,0,'||v_region_code||',0)';       
      
      Importbill(v_sql,'autocfg');
      
      v_sql :='insert /*+ append */ into billdetail (bill_id,item_id,primal_fee,cdr_discount,discount_fee,primal_fee1,cdr_discount1,discount_fee1,primal_fee2,cdr_discount2,discount_fee2)' || 
               'values('||v_bill_id||','||v_item_id||',0,0,0,'||arrays(l_r).subject_fee||',0,0,0,0,0)';
               
      Importbill(v_sql,'autocfg');         
           
      update  OCS_P_BILL set status = 1 where acct_id  = arrays(l_r).acct_id and serv_id = arrays(l_r).serv_id ;
     commit;
    end loop;
 close mycur;
 exception
    when others then
    ---  dbms_output.put_line(to_char(pp));
      dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD hh:mm:ss')||'sqlcode: '||to_char(sqlcode)||' sqlerrm: '||sqlerrm);
      rollback;
 
end bill_import_fj ;

Answer 3

把查询的两个sql放到外面就违背我的初衷了。
查询的话，对整体性能影响应该也不是很大