committ=y and import performance

[stmontgo]

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

[hacketta1]

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

[Mr.Hanky]

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

[slimdave]

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.

[hacketta1]

From the Oracle 8i docs:

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

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

[stmontgo]

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

[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

[Mr.Hanky]

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

[aarroyob]

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

[engiri]

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