Forum Stats

  • 3,838,237 Users
  • 2,262,343 Discussions
  • 7,900,552 Comments

Discussions

Why APPEND_VALUES hint is slower than APPEND hint in FORALL insert?

Andy Cheong
Andy Cheong Member Posts: 1 Green Ribbon

I was trying to build a simple testing program to test which database hint to be used in my FORALL..INSERT statement. I have created a table with logging option. Then, I try to insert bulk records using the FORALL statement. The program is running slow and create a lot of redo logs. After searching from the internet, it is suggested to use APPEND or APPEND_VALUES hint with the table nologging option turned-on. Since, I'm using the bulk collect method, I need to use the FORALL insert to submit a batch of records for insertion. Therefore, APPEND_VALUES hint is more suitable in my case. However, I found that the APPEND_VALUES hint is running much slower than the APPEND hint. Can anyone let me know what has gone wrong?

create table andy_test (c1 int primary key, c2 varchar(4000)) nologging;

declare

 lv_err_msg  VARCHAR2(500);

 l_start   NUMBER;

 v_trx_count NUMBER := 0;

 v_trx_limit NUMBER := 500;


 cursor cur is

  with rws as (

   select level x from dual

   connect by level <= 6000000

  )

   select x c1, 'testing'||x c2

   from rws;

    

 type cur_rec is table of cur%rowtype

  index by pls_integer;


 recs cur_rec := cur_rec(); --declare and initialize the array

  

 Procedure disable_tbl_constraint(p_tbl VARCHAR2) 

 is

 BEGIN

   FOR c IN

   (SELECT c.owner, c.table_name, c.constraint_name

    FROM user_constraints c, user_tables t

    WHERE c.table_name = t.table_name

    AND c.table_name = UPPER(p_tbl)

    AND c.status = 'ENABLED'

    AND NOT (t.iot_type IS NOT NULL AND c.constraint_type in ('P','R'))

    ORDER BY c.constraint_type DESC)

  LOOP

   dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);

  END LOOP;

 END disable_tbl_constraint;

  

 Procedure enable_tbl_constraint(p_tbl VARCHAR2) 

 is

 BEGIN

  FOR c IN

  (SELECT c.owner, c.table_name, c.constraint_name

   FROM user_constraints c, user_tables t

   WHERE c.table_name = t.table_name

   AND c.status = 'DISABLED'

AND c.table_name = UPPER(p_tbl)

   ORDER BY c.constraint_type)

  LOOP

   dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);

  END LOOP;

 END enable_tbl_constraint;  

  

begin

 l_start := DBMS_UTILITY.get_time;

  

 disable_tbl_constraint('FORALL_TEST');

 open cur;

 LOOP

  BEGIN

  fetch cur bulk collect into recs limit v_trx_limit;

  EXIT WHEN recs.Count = 0;


  FORALL i IN 1 .. recs.count 

   insert /*+ APPEND_VALUES */ into forall_test

   values recs( i );

  commit;

 EXCEPTION

  WHEN OTHERS THEN

       lv_err_msg := 'Error in bulk insert return: '||SUBSTR(SQLCODE||':'|| SQLERRM,1,500);

       dbms_output.put_line(lv_err_msg);

  END;

  v_trx_count := v_trx_count + recs.count;

 END LOOP;

 close cur;

  

 enable_tbl_constraint('FORALL_TEST');

  

 recs.delete;   --deleting the PLSQL index table data after used.

 dbms_session.free_unused_user_memory;

 DBMS_OUTPUT.put_line('Finished processed data: ' ||(DBMS_UTILITY.get_time - l_start)/100|| ' seconds');

 dbms_output.put_line('No of Record processed ='||to_char(v_trx_count));

end;

/

Finished processed data: 45.91 seconds

No of Record processed =6000000

Elapsed: 00:00:45.988

  Statistics

-----------------------------------------------------------

        0 user rollbacks

      87236 enqueue releases

        0 global enqueue get time

        0 physical read requests optimized

        0 ka wait calls attempted

        0 redo blocks checksummed by FG (exclusive)

        0 redo blocks checksummed by LGWR

        2 redo log space requests

        0 redo write broadcast ack time

        0 redo write broadcast ack count


If I change to use the "APPEND" hint, the result as follow:

Finished processed data: 16.69 seconds

No of Record processed =6000000

Elapsed: 00:00:16.731

  Statistics

-----------------------------------------------------------

        0 user rollbacks

      43514 enqueue releases

        0 global enqueue get time

        0 physical read requests optimized

        0 ka wait calls attempted

        0 redo blocks checksummed by FG (exclusive)

        0 redo blocks checksummed by LGWR

       43 redo log space requests

        0 redo write broadcast ack time

        0 redo write broadcast ack count