Re: Recommendations for import data sizes?

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
> >
> >
>
>
Received on Mon Apr 03 2000 - 05:03:13 CDT