Oracle 处理巨大的XML文件时的性能问题

我在处理具有超过10k记录的xml文件时看到性能问题，如果文件具有100记录，则可以正常工作。下面是我正在使用的示例过程 ..

++++++++++++++++
CREATE OR REPLACE PROCEDURE process_xml_file(errbuf       OUT  varchar2
                                            ,retcode      OUT  number
                                            ,b_file       IN   varchar2 ) IS
   l_stmt                number;     --use FOR error tracking
   l_program_name        varchar2(200) := 'MDM_EXTRACT_DATA';
   l_user                fnd_user.user_id%TYPE;
   l_err_msg             varchar2(2000);
   l_err_code            varchar2(200);
   l_request_id          number := 0;
   l_err                 varchar2(2000);
   l_sqlcode             varchar2(40);
   l_sqlerrm             varchar2(4000);
   
 CURSOR cur_product IS       
    SELECT ID,              
           Product_Name,
           Product_Long_Description,
           Product_Status,
           PID_ID,
           Primary_Flag,
           Available_Kit_Version,
           Product_Category_Code, 
           SGID
    FROM (WITH t AS (SELECT XMLTYPE(bfilename('NCR_NCRX_BFILES_DIR', b_file), nls_charset_id('UTF8')) xmlcol
                     FROM dual)
          SELECT ExtractValue(value(Product),'Product/ID') ID,
                 ExtractValue(value(Product),'Product/Product_Name') Product_Name, 
                 ExtractValue(value(Product),'Product/Product_Long_Description') Product_Long_Description,
                 ExtractValue(value(Product),'Product/Product_Status') Product_Status,
                 ExtractValue(value(PID),'/PID/PID_ID') PID_ID,
                 ExtractValue(value(PID),'/PID/Primary_Flag') Primary_Flag,
                 ExtractValue(value(PID),'/PID/Available_Kit_Version') Available_Kit_Version,
                 ExtractValue(value(PID),'/PID/Product_Category_Code') Product_Category_Code,
                 ExtractValue(value(Assignments),'/Assignments/SGID') SGID                 
          FROM t,
          TABLE(XMLSequence(extract(t.xmlcol,'/Products/Product'))) Product,
          TABLE(XMLSequence(extract(value(Product),'/Product/PIDS/PID'))) (+) PID,
          TABLE(XMLSequence(extract(value(Product),'/Product/Assigned_Solution_Group/Assignments'))) (+) Assignments
          );
 BEGIN
   NCR_APPL_INFO_PKG.SET_MODULE('INFX', l_program_name);
   --l_request_id := p_request_id; 
   l_user       := 80660;
   
   l_stmt := 10;
   
   IF b_file LIKE 'PMDM_Product%' THEN  
    
    FOR product_rec IN cur_product LOOP
     BEGIN
       l_stmt := 20;          
       INSERT 
       INTO  infx.ncr_mdm_item_initial
             (item,              
              description,
              creation_date,             
              created_by,                
              last_update_login,         
              last_update_date,          
              last_updated_by, 
              long_description,
              item_status,
              product_id,
              primary_flag,
              solution_assignment,
              product_flag,
              attribute1,
              attribute2)
       SELECT UPPER(product_rec.id),              
              product_rec.product_name,
              sysdate,
              l_user,
              null,
              sysdate,
              l_user,
              product_rec.product_long_description,
              product_rec.product_status,
              UPPER(product_rec.pid_id),
              product_rec.Primary_Flag,
              UPPER(product_rec.SGID),
              'Y',
              product_rec.Available_Kit_Version,
              product_rec.Product_Category_Code
       FROM dual;  
                    
     EXCEPTION when others THEN
        l_sqlcode := NULL;
        l_sqlerrm := NULL;
        
        l_sqlcode := sqlcode;
        l_sqlerrm := sqlerrm;
        dbms_output.put_line (' Error While Processing the file : '||l_sqlcode || ' :: '||l_sqlerrm );
     END;
    END LOOP;
    COMMIt;
   END IF;
END; 
/
++++++++++++++++++++++

DECLARE
 l_errbuf VARCHAR2(100);
 l_retcode  NUMBER;
BEGIN
  PROCESS_XML_FILE (l_errbuf,l_retcode,'PMDM_Product_4500817163558623_10292017040025.xml');
END; 
 /

++++++++++++++++++
复制

如果通过处理文件中的10k记录，您的意思是要插入10,000行，那么问题几乎肯定会出现在这里:

FOR product_rec IN cur_product LOOP
复制

循环很慢，嗯!

更重要的是，我没有看到任何特定的理由在这里使用循环。在游标中插入select语句的输出，根据需要增加。例如:

INSERT INTO infx.ncr_mdm_item_initial...
  SELECT ID,              
           Product_Name,
           Product_Long_Description,
           Product_Status,
           PID_ID,
           Primary_Flag,
           Available_Kit_Version,
           Product_Category_Code, 
           SGID
    FROM (WITH t AS (SELECT XMLTYPE(...)) ...
复制

如果用单个选择替换后，您发现跟踪代码以查看其功能仍然很慢!

您可以在以下位置找到有关说明:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

如果你仍然停留在这一点上，让我们知道你的发现的结果，我们会看看我们能提供什么帮助。