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