Re: import of BLOBs slow?

"koert54" <koert54_at_nospam.com> wrote in message news:<ZXAB8.61699$Ze.9706_at_afrodite.telenet-ops.be>...
> do you have commit = Y set ? if so - set commit = N
> if I'm not mistaking, batch commits are not performed on types like long and
> lobs ... so setting commit = Y would mean that for each lob insert a commit
> is issued - that's
> 50% redo wastage and high I/O on redologs (commit flushes the redo buffer).
> With commit = N only one commit is issued after the import of the entire
> table - get a large rollback segment for this ...
> check your waits on redo related resources ...
>

OK,
Found a couple of things:

1. Yeah, by default i had commit=y in all of my 'template' scripts so from http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=93763.1 Import basically does 'insert into tabX values (',,,,,,,')' for every row in your database, so the rollback generated for insert statements is only the rowid for each row inserted.
2. I tried disabling logging on LOBs, but that made things even worse!!! 'control file parallel write' waits and such became 70% of the waits.. Apparently Oracle is busy keeping track which files are not 'recoverable' Another great article by Steve Adams: http://www.ixora.com.au/tips/event_10359.htm Suprise!! even mentioned on Metalink! Too bad stinking events need a db reboot.
3. there was a bug that was (apparently) fixed in 8.1.7, but has been around since early versions of 8 http://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=731848

So, right after this last import, I shall do everything right :)

Thanx. Received on Sat May 18 2002 - 05:05:21 CDT