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,