Slow Import

Cameron,

I would add to your command line, the following :

 buffer=1024000 commit=yes

If you have the memory, make the buffer bigger - I have seen 8M recommended just this week on this NG.
That way, you won't hit a possible problem of running oput of rollback segment space. At present you are committing once, at the very end of the import. This will be quicker than every 'buffersize', but needs a lot of RBS.

Other tints and hips :

I presume you are importing into a blank user (lack of ignore=yes on command line) so dropping indexes woun't help here. If the users tables were simply truncated, lose the indexes too, so you don't update them with the table data during the import. I have read that imports go much much quicker if you :

• import a dummy run with indexfile=myindexes.sql (no data is imported, and a script to build your indexes is created)
• import the data only with indexes=no
• run the myindexes.sql agains the schema when the import completes

To me, it seems 'silly' that three passes would be quicker than just one, however, until I try it properly, I'm not going to dismiss it.

The constraints on your tables will be built as part of the import. I suspect putting a primary key on your big table will take time. Are there a lot of stored procedures, packaged etc - they get imported as well.

Check out your sort parameters in initSID.ora - they might be too small and the sorting required for building indexes is taking place on disc.

Do you have the export file on the same discs/controller as the database datafiles/temp files ?
If so, not a good idea.

Did the export specify compress=y, again, not a good idea if you have carefully set up the tablespaces to have equal initial and next extents. However, if the exported database has something like an initial = next = 40Kb, then there will be a lot of dynamic extension going on as each extend is required and allocated.

I'm sure someone else will have other suggestions, but I hope this lot helps.

Oh, exports always ruin much quicker than imports :o)

Regards,
Norman.

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com
------------------------------------------------------------------------

-----Original Message-----
From: Cameron Abbott [mailto:cvabbott_at_altavista.com] Posted At: Friday, November 02, 2001 2:07 PM Posted To: server
Conversation: Slow Import
Subject: Slow Import

Hi All,

Is there any way to spped up a database import? Here is the situation.

I did an export of a Oracle 7.3.4 database, it took 49 minutes using the direct option. The database contained 274 tables and the export file is 1.5
gigabytes. I am trying to import this data into a new database, Oracle 8.1.5. After 18 hours of importing it has only imported 116 tables. The
116th table has 8.5 million records which seems to be taking alot of time to
import.

Is there an option like direct that I can use to speed up the import?

My import command is

imp user/password fromuser=user_id touser=user_id file=exp.dmp

Thanks

Cameron Received on Fri Nov 02 2001 - 09:31:35 CST