2 Replies Latest reply on Jan 5, 2017 10:45 PM by Cashy

    performance issues with the bompexpl.exploder_userexit procedure after upgrading from R11.5.10 to R12.1

    Cashy

      Hi All,

       

      I am currently having big performance issues with the bompexpl.exploder_userexit procedure after upgrading from R11.5.10 to R12.1

       

      We have approximately 1500 items to explode which we loop through in a cursor which calls the bompexpl.exploder_userexit. 

       

      I then call another cursor which loops through the bom_explosion_temp table and inserts the results into a custom table.

       

      Below is a snippet of the code that is causing issues.

       

      FOR items_r IN items_c --loop through the 1500 items
        LOOP 
         bompexpl.exploder_userexit (verify_flag =>  0
                                     ,org_id =>    p_org_id 
                                     ,order_by  =>  2
                                     ,grp_id =>   x_group_id  
                                     ,session_id  =>  -1111
                                     ,levels_to_explode  => 20
                                     ,bom_or_eng  => x_explode_bom 
                                     ,impl_flag  => 1
                                     ,plan_factor_flag => 2
                                     ,explode_option  => 2
                                     ,module  =>    2
                                     ,cst_type_id => 0
                                     ,std_comp_flag => 0
                                     ,expl_qty =>   1
                                     ,item_id   =>  items_r.inventory_item_id
                                     ,alt_desg =>  '' 
                                     ,comp_code  => ''
                                     ,rev_date  =>  NULL
                                     ,unit_number => ''
                                     ,release_option=> 0
                                     ,err_msg   =>  v_err_msg
                                     ,error_code => v_err_code);
        END LOOP;  
      
        DELETE FROM bom_lists WHERE sequence_id = x_list_seq_id;
      
        -Delete the previous load
      
        FOR bom_r IN bom_c (x_group_id) --SELECT * FROM bom_explosion_temp and insert into custom table
        LOOP
          INSERT INTO xxmel_cpe_boms
      
      
      
      
      

       

      The custom table will have around 500,000 rows after the program has finished.

       

      When we run the program in 11.5.10 the program takes about 15 minutes to run.  After upgrading, it takes over an hour which is causing issues with many custom processes.

       

      After running a few tests in R12, I can see the 500,000 rows have been inserted after around 15 minutes which is the same as R11 but cant see what it is doing for next hour as the program should exit after the last insert.

       

       

      Has anyone had a similar experience.

       

      Any help would be appreciated,

      Chris

        • 1. Re: performance issues with the bompexpl.exploder_userexit procedure after upgrading from R11.5.10 to R12.1
          mdtaylor

          Hi Chris,

           

          I have used explosion_report instead on 12.1.3 and have not observed your hang/pause behavior.

           

          Are you saying it inserts half a million rows into xxmel_cpe_boms then you cannot tell what it is doing for an hour?  Did you also upgrade the database with your R12 upgrade?  It could be doing some index maintenance for that time.  You can try disabling any indexes then rebuild manually after the insert, or at least see if that is the source.

           

          There is a patch which updates the exploder for performance beyond what 12.1.3 provides:

           

          13901371:R12.BOM.COracle Bills of Material: Patch
          After applying this patch, 'Bill of Material Loop Report GUI' will complete in reasonable time.
          1 person found this helpful
          • 2. Re: performance issues with the bompexpl.exploder_userexit procedure after upgrading from R11.5.10 to R12.1
            Cashy

            Thanks for the reply...

             

            I have used explosion_report instead on 12.1.3 and have not observed your hang/pause behavior.

            we have tried using both and have similar performance issues with both

             

            Are you saying it inserts half a million rows into xxmel_cpe_boms then you cannot tell what it is doing for an hour?

            That's pretty much the issue yes. We can see that the program has done all of the required inserts after about 15 minutes so can't see what it is causing it to run for over an hour which is the confusing bit. It used to complete within 15 minutes before the upgrade so would expect the same in R12. I was wondering if there is some sort of trace or debug setting somewhere that we may need to disable??

             

             

              Did you also upgrade the database with your R12 upgrade?

            we didn't upgrade the database. We are still on a 10g platform.

             

              It could be doing some index maintenance for that time.  You can try disabling any indexes then rebuild manually after the insert, or at least see if that is the source.

             

            Thanks for the advice. I will look into recompiling the indexes and read up on the patch.

             

            Thanks for the help.

             

            Chris