The Oracle (tm) Users' Co-Operative FAQ

Why is my import so much slower than my export ? ?

Author's name: Jonathan Lewis

Author's Email:

Date written: 5th Sept 2002

Oracle version(s): up to 9.2

One of my exports takes about an hour, the matching import takes about five hours - why ?

Back to index of questions

If you switch on sql_trace whilst the import is running, then you will find that an import is typically executing a large number of array inserts to get your data into the database, and then creating indexes and adding constraints. In other words, it is doing a lot of very ordinary updates to the database.

When imp creates indexes, it may do so in nologging mode, but otherwise, all the work you do will be generating undo and redo. This is much heavier work than simply reading the data (by tablescan) and dumping it to a flattish file, which is almost all that exp does, so imp is almost invariably slower than exp. Things may be made worse by the fact that imports tend to be large, which means that when the indexes are being created, they are scanning tables with lots of dirty blocks back from disk, so they generate even more redo doing delayed block cleanout.

Note - there are always special cases - if you have lots of procedures and packages, and very little data in an exported schema, then the work done by Oracle to build the export (i.e. reconstruct your code) may be similar in complexity and volume to the work done by imp in reloading them, so some imports will not be dramatically slower than the export.

Damage limitation methods:

You might consider setting the commit parameter on the import. This causes imp to issue a commit every N rows, and for large imports could reduce the amount of delayed block cleanout that has to take place. This is generally NOT a good idea because it could leave you in an awkward state if something goes wrong - especially if you were using export/import to add data to existing tables.

Set a large buffer size for the import, this increases the array size that Oracle can use for its array insert. It can help.

Make sure that the sort_area_size is set to a large value to reduce disk activity when creating the indexes. In later versions of Oracle you could create a database logon trigger for the importing account that does an execute immediate to set the sort_area_size.

An import is a big job, check the size of your redo log files. It may be that you are doing lots of redo log switches, which results in lots of checkpoints as Oracle tries to flush all the current dirty data to disk. Increasing the redo log file size may help. (Check also the log_checkpoint_interval and log_checkpoint_timeout parameters, as these could have a similar effect).

If you are loading extra data on top of existing data, and therefore updating existing indexes, then the cost of those index updates may be large. You might consider dropping (some of) the indexes before the import. This may be particularly revelant for bitmap indexes which can degrade and grow quite dramatically as new data is added to the tables.

On import, Oracle will always build the indexes needed to enforce enabled primary key and unique key constraints, but you could stop it from creating all the other indexes and create them manually, in parallel, by hand later on. To stop excess indexes being created, you use the indexes parameter to the imp command, and if you want to generate a script that lists the indexes that would have been created (so that you can hack it and run it under your own control) you can use the indexfile parameter.

Further reading: N/A

Back to top

Back to index of questions