Monday, February 18, 2008

Tuning Import process

We sync four of our System Testing DBs with Prod DBs data once in two months. I have come up witht he below notes based on my experience and also from the notes I have collected from various sites and forums.

Before starting import:
  • Change DB to No Archive Log Mode.
  • Create large redo log files and drop existing redo logs. ( 2gb each or more..)
  • Increase the size of LOG_BUFFERS, DB_CACHE_SIZE considerably in the init$SID.ora file.
  • Place the dump files to be imported on a separate physical disk drive from the oracle data files and Redo log files.
  • Create a large Undo tablespace.
  • Identify the Big tables and drop Indexes.
  • Disable logging by setting the hidden parameter _disable_logging = true to reduce redo. ( The DB will be unrecoverable if DB crashes in the middle as the redo log files won't have the required data to do crash recovery. Take full Db backup to recover before setting this parameter.)

Start the Imp process with the below options:

  • BUFFER ( set a larger buffer )
  • RECORDLENGTH = 64k ( This needs to be set according to your I/O chunk size)
  • COMMIT = N (This option provides a significant performance increase. Make sure you have large UNDO/ROLLBACK segment)
  • INDEXES=N (Create an index file so that you can create indexes AFTER you have imported data)
After Import:
create the indexfile and create indexes for the big tables in parallel mode.

Review the below metalink article for more information on tuning import process:
Doc ID: Note:93763.1 Subject: Tuning Considerations When Import Is Slow