Re: Recommendations for import data sizes?

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 - 04:41:21 CDT