committ=y and import performance

Hola,
Working on getting an import to scream along. Just curious why Oracle docs suggest to set commit=y to improve the performance of a large import? I mean I can do test case after test case to show why one would not frequently commit in btach processing, what makes import so special?

Thanks

Steve

Hello Steve

I don't know why commit=y should improve performance either, but I believe that there is an exception to this rule. When importing LONG,LOB,BFILE,REF,ROWID or type columns array inserts aren't done, so a commit is performed after each row. They say that for tables with these kind of columns it is preferable to set commit=n (and hope your rollback segments are big enough!). I'm going to be working on similar project to yours this week, and i'm going to benchmark LONG column imports with commit=y and commit=n. I'll let you know what I find out.

Also, you might find this free tool of interest (I've not used it myself):

http://pepi.sourceforge.net/#who

Commit=y is good, it is your friend!

If you do not use commit=y then Oracle (by default) will try to import the ENTIRE table before issuing a commit. This can be a vewy ( Elmer Fudd influence) bad thing if you have large tables, it can kill your RBS or redo, or undo, or whatever the hell you want to call it.

I usually use commit=y and buffer=4000000 (give of take) this way oracle will commit after every 3MB or so. You can make the buffer bigger if you like, that way you will have less frequent commits.

MH

I don't think that commit=y will make the import faster - it will probably slow it down. However it would give you a better chance of the import actually completing successfully without running out of rollback space.

From the Oracle 8i docs:

Quote:

---

Specifying COMMIT=Y ... improves the performance of large imports

---

Hmmmm, another Oracle myth I wonder? I think I might try bench marking this too.

Quote:

---

Originally posted by hacketta1
From the Oracle 8i docs:



Hmmmm, another Oracle myth I wonder? I think I might try bench marking this too.

---

from the utilities guide
"Specifying COMMIT=y prevents rollback segments from growing inordinately large and improves the performance of large imports"

I benchmarked this and it did not see an appreciable difference.

Just wondering if others had any thoughts

Steve

To speed up IMPORT

- Use COMMIT=N. This will cause import to commit after each object (table), not after each buffer. This is why one large rollback segment is needed.


from http://metalink.oracle.com/metalink/...T&p_id=93763.1

Basically you need to know how large your tables are. If they are HUGE, put in a commit, it ain't gonna kill you and the impact will be marginal.

MH

If you really want to speed up your import, indexes=no. Take the creation indexes script an increase your sort_area_size, do in parallel ...

Anyway import with commit=no. Look for your biggest table and create or increase your rbs to at least this size.

Cheers

Quote:

---

Originally posted by pando
To speed up IMPORT

- Use COMMIT=N. This will cause import to commit after each object (table), not after each buffer. This is why one large rollback segment is needed.


from http://metalink.oracle.com/metalink/...T&p_id=93763.1

---

It is not correct..
During a large table import ..

Exporting from one session:=
14:54:37 SQL> host exp80 XXXXxx/asdf@asdf file=d:\ordrdetl.dmp direct=y constraints=n tables=ordrdetl

14:55:54 SQL> select count(*) from ordrdetl;

COUNT(*)
---------
874350

real: 2610
Importing the same into another user.
14:56:14 SQL> host imp80 backup/xxx@test file=d:\ordrdetl.dmp commit=y full=y

From ANother session of the backup user
I parallely run the count.. It increase ..gradually depending on the Buffer value we have
given

14:57:03 SQL> select count(*) from ordrdetl;

COUNT(*)
---------
33250

real: 16

14:58:13 SQL> /

COUNT(*)
---------
90972

real: 31
14:58:16 SQL> /

COUNT(*)
---------
115178

real: 31
14:58:21 SQL> /

COUNT(*)
---------
147630

real: 32
14:58:27 SQL> /

COUNT(*)
---------
165319

real: 47
14:58:30 SQL> /

COUNT(*)
---------
181146

real: 31
14:58:31 SQL> /

COUNT(*)
---------
182875

real: 63
14:58:33 SQL> show user
USER is "BACKUP"

But during commit=n you cannot even decribe the table..in the other user

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

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;
/