how can I reduce time of insert to database when many records?

this is my code and Thousands of records insert to table but time of execution is very long (about 15 minute), how can I do to reduce this time? thank you

BEGIN
  delete from pre_percapita_accords t where t.dat_capit = DATACTUL;
  FOR REC2 IN FORMUL_ID LOOP
    FOR rec1 in FND_Bunit loop
        FOR REClkp1 IN EMPLY(rec1.cod_busun,rec1.lkp_cod_dput_busun) LOOP
          v_result_param1 := Fnd_Formula_Pkg.SET_PARAM_VALUE_FUN(REC2.FRML_ID,
                                                                 'EMPL_ID',
                                                                 to_char(REClkp1.num_prsn_emply));
          v_result_param2 := Fnd_Formula_Pkg.SET_PARAM_VALUE_FUN(REC2.FRML_ID,
                                                                 'DAT_ACCORD',
                                                                 to_char(DATACTUL));
          fnd_formula_set_param_prc(REC2.FRML_ID);
          resultFun := Fnd_Formula_Pkg.GET_PARAM_VALUE_FUN(REC2.FRML_ID,
                                                           'NUM_RESULT');
          resultFun := trunc(resultFun, 3);
          if REClkp1.NUM_PRSN_EMPLY is not null and resultFun is not null and
             DATACTUL is not null then
            INSERT INTO pre_percapi开发者_C百科ta_accords
              (FRMLS_FORMUL_STEP_ID,
               dat_capit,
               num_capit,
               emply_num_prsn_emply,
               lkp_status_capit)
            VALUES
              (rec2.formul_step_id,
               DATACTUL,
               resultFun,
               REClkp1.NUM_PRSN_EMPLY,
               '3'); 
          end if;
        END LOOP;
      END LOOP;
  end loop;

You have 3 nested cursor loops, within which you are calling several functions at the lowest level and performing a single-row insert. There are many possibilities here to improve things:

1) Function Fnd_Formula_Pkg.SET_PARAM_VALUE_FUN is called twice but the results are never used (in the code you have shown) - can these calls simply be removed?

2) The second call to Fnd_Formula_Pkg.SET_PARAM_VALUE_FUN doesn't use any data from the EMPLY or FND_Bunit cursors, so it could be moved outside of those 2 loops resulting in it being called less.

3) You could save the results into arrays and then use bulk inserts of e.g. 1000 rows at a time.

4) Ideally, the whole thing could be re-written without cursors as a single INSERT...SELECT statement. I cannot say for sure that this is possible here though.

Instead of guessing, you might try sql trace and tkprof for some better metrics of whats happening. My guess is the function calls are slow.

If that seems too daunting, you can also debug by adding some timing log statements (using timestamps), testing against a dev db of course, and see where the time is spent (a bit like the old printf statement debugging). At least you will have a better idea of what is taking most of the time.

Try to use collections and insert with FORALL: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/forall_statement.htm