Oracle Import Utility & Log Sync Waits

Few days before I had to import about 3M records and it took me lot more time then I thought it would - about an hour.
My parameter file looked like:

userid=system/oracle
file=exp_USER2.dmp
log=imp_USER2.log
fromuser=(USER2)
touser=(USER1)
buffer=10485760
commit=y
grants=n
feedback=10000

In my parameter file I've used commit=y parameter because I wanted to avoid "ORA-01562: Failed to extend rollback segment" error. I could do the import with commit=n and hope that my rollback/undo tablespace is sized enough, but mentor suggested me to use commit=y and be sure that everything will work as it should.



From documentation:
COMMIT (default: n)
Specifies whether Import should commit after each array insert. By default, Import commits only after loading each table, and Import performs a rollback when an error occurs, before continuing with the next object.
Specifying COMMIT=y prevents rollback segments from growing inordinately large and improves the performance of large imports. Specifying COMMIT=y is advisable if the table has a uniqueness constraint. If the import is restarted, any rows that have already been imported are rejected with a recoverable error.
If a table does not have a uniqueness constraint, Import could produce duplicate rows when you reimport the data.
For tables containing LONG, LOB, BFILE, REF, ROWID, UROWID, or DATE columns, array inserts are not done. If COMMIT=y, Import commits these tables after each row.


Later I've made small test case - monitored execution and elapsed time.

TEST CASE

commit=n

userid=system/oracle
file=exp_TABLE2.dmp
log=imp_TABLE2.log
fromuser=(USER2)
touser=(USER1)
commit=n
grants=n
indexes=n
tables=(CUSTOMFIELDVALUE,FIELDCONFIGSCHEME,FIELDCONFIGURATION,FIELDLAYOUTITEM,TABLE1ACTION)
feedback=10000

. importing TABLE2's objects into TABLE1
. . importing table "CUSTOMFIELDVALUE"
................................
328422 rows imported
. . importing table "FIELDCONFIGSCHEME"
109 rows imported
. . importing table "FIELDCONFIGURATION"
109 rows imported
. . importing table "FIELDLAYOUTITEM"
417 rows imported
. . importing table "TABLE1ACTION"
...........................
275187 rows imported
Import terminated successfully without warnings.

Import took 4:34 minutes to complete.

commit=y

userid=system/oracle
file=exp_TABLE2.dmp
log=imp_TABLE2.log
fromuser=(USER2)
touser=(USER1)
commit=y
grants=n
indexes=n
tables=(CUSTOMFIELDVALUE,FIELDCONFIGSCHEME,FIELDCONFIGURATION,FIELDLAYOUTITEM,TABLE1ACTION)
feedback=10000

. importing TABLE2's objects into TABLE1
. . importing table "CUSTOMFIELDVALUE"
................................
328422 rows imported
. . importing table "FIELDCONFIGSCHEME"
109 rows imported
. . importing table "FIELDCONFIGURATION"
109 rows imported
. . importing table "FIELDLAYOUTITEM"
417 rows imported
. . importing table "TABLE1ACTION"
...........................
275187 rows imported
Import terminated successfully without warnings.

Import lasted for 12:45 minutes what was three times slower than with commit=n.
I've made small monitoring and "log sync waits" were noticeable cause of frequent commits. After every commit LGWR is writing the contents of the redo and all process are waiting for this write to complete. To speed up this you should reduce number of commits or place redo logs on faster disks.
So commit=y parameter slowed import significaly.


Then I tried the same thing with setting buffer=10485760 (10M) parameter.
With using larger buffer parameter import could do more work on commit before another disk access and that should speed up things for a bit.

Documentation:
Default: operating system-dependent
The integer specified for BUFFER is the size, in bytes, of the buffer through which data rows are transferred.
BUFFER determines the number of rows in the array inserted by Import. The following formula gives an approximation of the buffer size that inserts a given array of rows:
buffer_size = rows_in_array * maximum_row_size

commit=y,buffer=10485760

userid=system/oracle
file=exp_TABLE2.dmp
log=imp_TABLE2.log
buffer=10485760
fromuser=(USER2)
touser=(USER1)
commit=y
grants=n
indexes=n
tables=(CUSTOMFIELDVALUE,FIELDCONFIGSCHEME,FIELDCONFIGURATION,FIELDLAYOUTITEM,TABLE1ACTION)
feedback=10000

. importing TABLE2's objects into TABLE1
. . importing table "CUSTOMFIELDVALUE"
................................
328422 rows imported
. . importing table "FIELDCONFIGSCHEME"
109 rows imported
. . importing table "FIELDCONFIGURATION"
109 rows imported
. . importing table "FIELDLAYOUTITEM"
417 rows imported
. . importing table "TABLE1ACTION"
...........................
275187 rows imported
Import terminated successfully without warnings.

And still it took about 12 minutes. Again "log sync waites" showed.


Conclusion would be that you should use COMMIT=N anytime you can. You will have significant performance improvement. Only if you have really large tables, then commit=y should be used cause of undo/rollback possible space shortage.

It's obvious that having rollback/undo sized enough is very important to have. Saving disk space with undersized rollback/undo tablespace shouldn't be your practice nowadays when disk storage isn't very expensive.

NOTE!!
I've seen that some DBA's use NOLOGGING or _disable_logging=TRUE parameter to speed up import. You should be very careful about this cause if instance crashes when these parameters are enabled (during Import) your database could suffer serious damage. If you still decide to use nologging - take cold backup and be sure that youre restore works.

Some tricks about speeding up Import/Export from Pythian:
Speeding Up Oracle Import/Export


RELATED DOCUMENTS:
Import