Oracle XML大批量数据性能测试问题，请大家帮忙！

Question

存储过程代码如下：
create or replace procedure SP_ASSIGNCASE(num number) is
--分案
cursor c_TMP is select XMLDATA from TMP FOR UPDATE;--案件临时存放表
r_TMP c_TMP%rowtype;numCode_SN number;
strCode_Name nvarchar2(20);numOrg_ID number;
strOrg_Name nvarchar2(20);numZone_ID number;
strZone_Name nvarchar2(20);numProduct_ID number;
strProduct_Name nvarchar2(20);numGroup_Cust_A_SN number;
strGroup_Cust_A_FIELD1 nvarchar2(20);numGroup_Cust_M_SN number;
strGroup_Cust_M_FIELD1 nvarchar2(20);numCust_Level_SN number;
strCust_Level_FIELD1 nvarchar2(20);numCust_Cata_SN number;
strCust_Cata_FIELD1 nvarchar2(20);numAssign_Rule_SN number;
strAssign_Rule_FIELD1 nvarchar2(20);numUser_ID number;
strUser_Name nvarchar2(20);n number;
m number;
begin
     delete from TMP;
     delete from CASE;
     --Insert data into TMP
     m:=1;
     loop
         exit when (m>num);
         n:=mod(m,200)+1;
         insert into TMP(XMLDATA)
         values(sys.xmltype.createXML('<Case><User></User><Code>'||n||'</Code><Org>'||n||'</Org><Zone>'||n||'</Zone><Product>'||n||'</Product><Group_Cust_A>'||n||'</Group_Cust_A><Group_Cust_M>'||n||'</Group_Cust_M><Cust_Cata>'||n||'</Cust_Cata><Cust_Level>'||n||'</Cust_Level><Assign_Rule>'||n||'</Assign_Rule><Collector>'||n||'</Collector><A1></A1><A2></A2><A3></A3><A4></A4><A5></A5><A6></A6><A7></A7><A8></A8><A9></A9><A10></A10><A11></A11><A12></A12><A13></A13><A14></A14><A15></A15><A16></A16><A17></A17><A18></A18><A19></A19><A20></A20><A21></A21><A22></A22><A23></A23><A24></A24><A25></A25><A26></A26><A27></A27><A28></A28><A29></A29><A30></A30><A31></A31><A32></A32><A33></A33><A34></A34><A35></A35><A36></A36><A37></A37><A38></A38><A39></A39><A40></A40><A41></A41><A42></A42><A43></A43></Case>'));
         m:=m+1;
     end loop;
     --设置代码，组织，区域，产品
     open c_TMP;
          loop
                    fetch c_TMP into r_TMP;
                          exit when c_TMP%NOTFOUND;
                          
                          numCode_SN:=r_TMP.XMLDATA.extract('/Case/Code/text()').getNumberVal();
                          select CODE_NAME into strCode_Name from CODE where CODE_SN=numCode_SN;
                          
                          numOrg_ID:=r_TMP.XMLDATA.extract('/Case/Org/text()').getNumberVal();
                          select ORG_NAME into strOrg_Name from ORG where ORG_ID=numOrg_ID;
                          
                          numZone_ID:=r_TMP.XMLDATA.extract('/Case/Zone/text()').getNumberVal();
                          select ZONE_NAME into strZone_Name from ZONE where ZONE_ID=numZone_ID;
                          
                          numProduct_ID:=r_TMP.XMLDATA.extract('/Case/Product/text()').getNumberVal();
                          select PRODUCT_NAME into strProduct_Name from PRODUCT where PRODUCT_ID=numProduct_ID;
                          
                          update TMP
                          set TMP.XMLDATA=
                          updateXML(TMP.XMLDATA,
                          '/Case/Code/text()',strCode_Name,
                          '/Case/Org/text()',strOrg_Name,
                          '/Case/Zone/text()',strZone_Name,
                          '/Case/Product/text()',strProduct_Name
                          )
                          where CURRENT of c_TMP;
          end loop;                    
     close c_TMP;
     
     --自动归户
     open c_TMP;
          loop
                    fetch c_TMP into r_TMP;
                          exit when c_TMP%NOTFOUND;
                          
                          numGroup_Cust_A_SN:=r_TMP.XMLDATA.extract('/Case/Group_Cust_A/text()').getNumberVal();
                          select FIELD1 into strGroup_Cust_A_FIELD1 from Group_Cust_A where SN=numGroup_Cust_A_SN;
                          
                          update TMP
                          set TMP.XMLDATA=
                          updateXML(TMP.XMLDATA,
                          '/Case/Group_Cust_A/text()',strGroup_Cust_A_FIELD1
                          )
                          where CURRENT of c_TMP;
                                   
          end loop;
     close c_TMP;
     --手动归户
     open c_TMP;
          loop
                    fetch c_TMP into r_TMP;
                          exit when c_TMP%NOTFOUND;
                          
                          numGroup_Cust_M_SN:=r_TMP.XMLDATA.extract('/Case/Group_Cust_M/text()').getNumberVal();
                          select FIELD1 into strGroup_Cust_M_FIELD1 from Group_Cust_M where SN=numGroup_Cust_M_SN;
                          
                          update TMP
                          set TMP.XMLDATA=
                          updateXML(TMP.XMLDATA,
                          '/Case/Group_Cust_M/text()',strGroup_Cust_M_FIELD1
                          )
                          where CURRENT of c_TMP;
                                   
          end loop;
     close c_TMP;
     --风险等级设定
     open c_TMP;
          loop
                    fetch c_TMP into r_TMP;
                          exit when c_TMP%NOTFOUND;
                          
                          numCust_Level_SN:=r_TMP.XMLDATA.extract('/Case/Cust_Level/text()').getNumberVal();
                          select FIELD1 into strCust_Level_FIELD1 from Cust_Level where SN=numCust_Level_SN;
                          
                          update TMP
                          set TMP.XMLDATA=
                          updateXML(TMP.XMLDATA,
                          '/Case/Cust_Level/text()',strCust_Level_FIELD1
                          )
                          where CURRENT of c_TMP;
                                   
          end loop;
     close c_TMP;
     --客户分类
     open c_TMP;
          loop
                    fetch c_TMP into r_TMP;
                          exit when c_TMP%NOTFOUND;
                          
                          numCust_Cata_SN:=r_TMP.XMLDATA.extract('/Case/Cust_Cata/text()').getNumberVal();
                          select FIELD1 into strCust_Cata_FIELD1 from Cust_Cata where SN=numCust_Cata_SN;
                          
                          update TMP
                          set TMP.XMLDATA=
                          updateXML(TMP.XMLDATA,
                          '/Case/Cust_Cata/text()',strCust_Cata_FIELD1
                          )
                          where CURRENT of c_TMP;
                                   
          end loop;
     close c_TMP;
     --分案
     open c_TMP;
          loop
                    fetch c_TMP into r_TMP;
                          exit when c_TMP%NOTFOUND;
                          
                          numAssign_Rule_SN:=r_TMP.XMLDATA.extract('/Case/Assign_Rule/text()').getNumberVal();
                          select USER_NAME into strUser_Name from Assign_Rule,User_Info where SN=numAssign_Rule_SN and Assign_Rule.User_Id=User_info.User_Id;
                          
                          update TMP
                          set TMP.XMLDATA=
                          updateXML(TMP.XMLDATA,
                          '/Case/Collector/text()',strUser_Name
                          )
                          where CURRENT of c_TMP;
                                   
          end loop;
     close c_TMP;
     insert into CASE 
     select * from TMP;
     
     --delete from TMP;
     
     commit;
end SP_ASSIGNCASE;