Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: import of BLOBs slow?

Re: import of BLOBs slow?

From: koert54 <koert54_at_nospam.com>
Date: Mon, 06 May 2002 19:37:29 GMT
Message-ID: <ZXAB8.61699$Ze.9706@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 ...

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3cd6cbc6$0$8509$cc9e4d1f_at_news.dial.pipex.com...
> 800mb/hour just sounds hideously slow. it may well be worth testing this
out
> in a test database without the load. the other obvious thing to check is
how
> your redo IO is going.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> "NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message
> news:3cd6c411.330602070_at_news.globix.com...
> > That's good stuff, but
> > a) it doesn't answer my question,
> > b) restoring from hot backups would not make sense to use to restore a
> > single table
> > c) I did not indicate that we're not using such backups
> > d) recovering from hot backups wouldn't make recovering this table
> > faster
> >
> >
> >
> > On Sat, 4 May 2002 05:11:20 -0500, "Steve Ashmore"
> > <sashmore_at_neonramp.com> wrote:
> >
> > >You may wish to reconsider your backup strategy.
> > >exp/imp is good for a secondary strategy or good for very small
> databases.
> > >For large databases you shold turn on
> > >archive logging and impliment hot backups.
> > >
> > >Stephen C. Ashmore
> > >Brainbench MVP for Oracle Administration
> > >http://www.brainbench.com
> > >
> > >Author of 'So You Want to be an Oracle DBA?'
> > >
> > >"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message
> > >news:3cd2b7c7.65312023_at_news.globix.com...
> > >> We had a minor crash of one of our applications yesterday, which was
> > >> writing to some BLOBs in the database.
> > >>
> > >> Given that it was almost impossible to fix the BLOBs we decided to
> > >> recover from last night's backup (exp).
> > >>
> > >> Would be nice if Oracle had a query option for the imp command as
well
> > >> :)
> > >>
> > >> Anyway, the export of 60K rows or so of about 800MB in total size,
> > >> took about an hour. Given, that this is the first application we are
> > >> using that requires the use of BLOBs, and that we expect this table
to
> > >> grow to gigs of data few months from now, it worries me that the
> > >> recovery times might become too long down the line. Maybe I am just
> > >> being paranoid, since the import occured at high load times (almost
> > >> 100% cpus were utilized), but I'd like to hear if anybody had similar
> > >> experiences and had any interesting findings.
> > >>
> > >> Right off the bat, I probably should have disabled logging on the
> > >> LOBs, and if there is no easy solution, consider partitioning the
> > >> table in the future..
> > >>
> > >> Any other suggestions would be appreciated.
> > >>
> > >> Thanx.
> > >> .......
> > >> We use Oracle 8.1.7.3 on Solaris 2.7 boxes
> > >> remove NSPAM to email
> > >
> > >
> >
> > .......
> > We use Oracle 8.1.7.3 on Solaris 2.7 boxes
> > remove NSPAM to email
>
>
Received on Mon May 06 2002 - 14:37:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US