Export Import Performance Optimization Tips

We can improve Export-Import performance optimization by considering following points.

Export Optimization tips:

  • Set the BUFFER parameter to high value (eg 2M)
  • Set the RECORDLENGTH parameter to a high value (e.g. 64K)
  • Stop unnecessary applications to free-up resources for your job.
  • DO NOT export to an NFS mounted filesystem.
  • Big temporary tablespace should assign to user who performing Export.
  • The drive receiving the export file should be separate from the disk drive where the database files reside.
  • User direct path export: Specifying DIRECT=y causes Export to extract data by reading the data directly, bypassing the SQL command-processing layer (evaluating buffer). This method can be faster than conventional path Export.

Syntax:
exp system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log

 

Import Optimization Tips:

  • Exported dump file should be on different disk then new database disk location.
  • Increase DB_CACHE_SIZE in initSID.ora file.
  • Set bigger LOG_BUFFER and bounce oracle database.
  • Stop redolog archiving. ALTER DATABASE NOARCHIVELOG;
  • Use COMMIT=N
  • Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements.

Syntax:
imp system/manager full=y recordlength=65535 file=exp_full.dmp log=imp_full.log

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply