Optimizing Import/Export


  • Set the BUFFER parameter to a high value (e.g. 2Mb — entered as an integer "2000000")
  • Set the RECORDLENGTH parameter to a high value (e.g. 64Kb — entered as an integer "64000")
  • Use DIRECT=yes (direct mode export)
  • 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.


  • 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 STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics.
  • Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
  • Remember to run the indexfile previously created.

Improving Data Pump Export and Import:

Use Parallel option and export to as many files as the degree of parallelism.

expdp username/password directory=dump_dir filesize=1G dumpfile=full%U.dmp logfile=fulllog parallel=16

Increasing pga_aggregate_target to boost import.

Impdp Options

Analyze once after the load – Set analyze=n and analyze with dbms_stats after the load has completed.

Increase recordlength – Many set recordlength to 64k, but it needs to be a multiple of your I/O chunk size and db_block_size (or your multiple block size, e.g. db_32k_block_size).

Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase.  Larger tables may not be suitable for this option due to the required rollback/undo space.

Dedicate a single, large rollback segment – Many professionals create a single large rollback segment and take all others offline during the import.

Set indexes=n – Index creation can be postponed until after import completes, by specifyingindexes=n.  If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table.  Setting indexes=neliminates this maintenance overhead.   You can also Use the indexfile parm to rebuild all the indexes once, after the data is loaded.

Defer CBO stats – Using impdp with the parameter exclude=statistics will greatly improve the import speed, but statistics will need to be re-analyzed or imported later.

Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.

Disable logging – You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.

Further read Oracle data load


One response to this post.

  1. How does RECORDLENGTH affect your exp speed?…

    exp导出工具的几个常用参数想必大家都很熟悉;有时为了加速导出作业我们会加上direct=y,进一步的可能就会设置RECORDLENGTH参数了,Oracle官方对这个参数的定义是:length of IO record;这个解释过于简单了,偶有余暇来探究一下RECORDLENGTH的工作原理: [maclean@rh2 test]$ exp help=y Export: Release – Production on Mon Nov 8 17:26:34 2010 Copyr…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: