-
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
I'm stmontgo and I approve of this message
-
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 remember when this place was cool.
-
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:
Specifying COMMIT=Y ... improves the performance of large imports
Hmmmm, another Oracle myth I wonder? I think I might try bench marking this too.
-
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
I'm stmontgo and I approve of this message
-
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
I remember when this place was cool.
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|