Question:

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.

Answer 1:

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