FYI, learned during test CMS data migration with 5 tables having timestamp columns.
The symptom is top wait event " log file sync".
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 3,164 95.2
log file sync 1,409,922 3,105 2 93.4 Commit
log file parallel write 1,409,992 3,036 2 91.3 System I/O
db file parallel write 1,463 17 11 0.5 System I/O
control file parallel write 1,254 9 7 0.3 System I/O
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 3,164 95.2
log file sync 1,409,922 3,105 2 93.4 Commit
log file parallel write 1,409,992 3,036 2 91.3 System I/O
db file parallel write 1,463 17 11 0.5 System I/O
control file parallel write 1,254 9 7 0.3 System I/O
Cause
For a number of data types, imp will insert one row at a time.
This is expected behavior. If table has following datatypes ,
LOBs, LONGs, ADTs (user-defined types), ROWID/UROWIDs and the timestamp.
"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."
Solution
This issue is same as Bug 2797580 which is closed as not a bug and
subsequently an enhancement request Bug 3452820 was logged. "
Work around to this issue is to use Commit=N parameter while import.
After I comment out "commit=y", I got the import done within 6 mins vs 161 mins with "commit=y"
