| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Recommendations for import data sizes?
Andrew Williamson wrote:
>
> Thanks for your quick response. I am already in noarchivelog mode.
>
> The idea about creating indexes after database creation sounds good so I'll
> try that.
>
> All temporary/log stuff I have already done.
>
> Sort area is 5mb. I thought that having a lot of buffers might help sort
> speed/hits, but logically a bigger sort_area would be better.
>
> Will give all this a try and may post some figures for others interest.
>
> Thanks again
>
> Andrew
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message
> news:954754928.3265.0.pluto.d4ee154e_at_news.demon.nl...
> > The issues you mention all have a marginal effect on speed of import.
> > If this is a new database do the following:
> > - make sure the database is running in NOARCHIVELOG (this will already
> save
> > an enormous amount of I/O)
> > - make sure you use the parameters
> > buffer=8192000 commit=y and indexes=n
> > on the imp command line
> > (For the last option make sure you should you have a prior run of
> > imp indexfile=<any filename>)
> > - increase the sort_area_size to anything when creating indexes
> > (If you really are the only user you could use some 4 Mb)
> > - make sure your temporary segments are adequately sized and have LARGE
> > extents
> > (otherwise you'll loose time on extent allocation)
> > - make sure your temporary segments are in a temporary tablespace.
> > - Look into the size of your online redo log files (they are used anyway),
> > make sure they are at least 5M, 4 of them
> > - Make sure your log_checkpoint_interval parameter is bigger than the size
> > of your redolog files, so you'r not checkpointing too often.
> > - As you are INSERTing only, the value of db_block_buffers is not really
> > important
> > - There is no ratio between sort_area_size and db_block_buffers
> > - By design the sort_area_size is allocated in the PGA, the PGA is outside
> > the SGA, in the context of the user backend process, unless you are using
> > MTS (which you probably don't)
> >
> > Hth,
> > Sybrand Bakker, Oracle DBA
> >
> >
> >
> > Andrew Williamson <andrewweb_at_my-deja.com> wrote in message
> > news:954752609.11079.0.nnrp-14.c30bdde2_at_news.demon.co.uk...
> > > Hi
> > >
> > > I'm building a copy of a database on a test system from a full export on
> > > live. It works OK, but I've been playing around with db_block_buffers
> and
> > > sort_area_size to try and speed the whole deal up. Using the usual
> > > 'with-users' sizes, the import took around 1.5 days. The machine is
> 196mb
> > > and I'm importing around 21gb import file.
> > >
> > > My question is, is it more appropriate for an import to dedicate more
> > memory
> > > to sort_area_size to speed up index creation, or perhaps to
> > db_block_buffers
> > > to increase data hits? Some sort of recommended ratio/balance between
> the
> > > two?
> > >
> > > If anyone has any recommendations on how I can optimize this, I'd be
> > > grateful. I have done 4 imports so far, but I'm wondering where I should
> > be
> > > using this memory - in the SGA or PGA?. I'm hoping to go right up
> against
> > > that 196mb of memory so there is as little (pref. none) swapping going
> on.
> > > The machine is dedicated to this, no other users/db's on it.
> > >
> > > Thanks in advance
> > >
> > > Andrew
> > >
> > >
> >
> >
The following may assist:
http://www.oracledba.co.uk/tips/import_speed.htm --
We are born naked, wet and hungry...then things get worse Received on Mon Apr 03 2000 - 05:14:22 CDT
 |
 |