Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, November 2, 2007

Improve imp and exp performance

orafaq.com has a very good information on imp and exp here. I am reproducing what they have written on improving imp and exp performance:

How can one improve Import/ Export performance?

EXPORT:
  • Set the BUFFER parameter to a high value (e.g. 2M)
  • Set the RECORDLENGTH parameter to a high value (e.g. 64K)
  • Stop unnecessary applications to free-up resources for your job.
  • If you run multiple export sessions, ensure they write to different physical disks.
  • DO NOT export to an NFS mounted filesystem. It will take forever.
IMPORT:
  • Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
  • Place the file to be imported on a separate physical disk from the oracle data files
  • Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
  • Set the LOG_BUFFER to a big value and restart oracle.
  • Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
  • Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
  • Use COMMIT=N in the import parameter file if you can afford it
  • Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
  • Remember to run the indexfile previously created

No comments: