committ=y and import performance

[engiri]

I am sorry.. I thought you have given commit=y Pando. Just revoke
my post.. here

[stmontgo]

Thanks all,
I was not able to see an appreciable gains with commit=y, just curious why the docs would say that and if others had different experiences with seeing gains from commit=y.

I little more background. We are moving our data center and in the process going from windooze to unix. In our go-live weekend every minute counts if you consider backup to tape, ship tape, retore etc...

Anyhow, our db dump is 30gb so what I did was split up the export in to several parts. We have one app user (PeopleSoft) who is sysadm so I did the following. Once I produced multiple export files and ran 6 import sessions in parallel my import went from 4 hours to 45 minutes.

Thanks

Steve

CREATE OR REPLACE DIRECTORY export_dir AS 'c:\temp';

CREATE OR REPLACE PROCEDURE export_for_refresh
AS
v_counter NUMBER :=0;
v_logfile_number NUMBER :=1 ;
v_output_file1 utl_file.file_type;
BEGIN
v_output_file1 := utl_file.fopen('EXPORT_DIR', 'export_for_refresh_file_' || v_logfile_number || '.txt', 'W');
FOR cursor_table IN (SELECT table_name FROM all_tables WHERE owner = 'SYSADM')
LOOP
utl_file.put_line (v_output_file1, cursor_table.table_name);
IF v_counter =1000 THEN
v_counter:=0;
v_logfile_number:= v_logfile_number+1;
v_output_file1:= utl_file.fopen('EXPORT_DIR', 'export_for_refresh_file_' || v_logfile_number || '.txt', 'W');
END IF;
v_counter := v_counter+1;
END LOOP;
utl_file.fclose_all;
END;
/