Import first 1GB fast, then slows down 9.2.0.4

[ksandbergfl]

I am migrating a 6GB database from 8.1.7.4 to 9.2.0.4. The hardware is Sun v450's running Solaris 9.

I use the 8.1.7.4 EXPORT command to create my dump file. I have no tables with LONG's.

I use the 9.2.0.4 IMPORT command to load the dump file, using the following syntax:

IMP file= full=y ignore=y indexes=n constraints=n analyze=n feedback=5000 commit=n buffer=10000000

The import screams through the first 50 tables... over 1GB worth of data is loaded within 45 minutes, which is pretty good. There are two tables with over 3M rows in this bunch. But then, for no reason I can determine -- IMP gets really slow. Right now, a 170MB table with 1.8M rows has taken over 2 hours, and it's still not done. Again, there are NO LONG's in my data (I know enough not to IMP tables with LONG's). I have disabled all triggers and constraints. The data should simply be zooming right in.

Any ideas what might be going on? My UNDO and my TEMP tablespaces appear to be fine.

Thanks in advance for any help you can provide.

 

[SantaMufasa]

K,

Still information gathering here:
1) Are your target tablespace(s) "extent management local" or dictionary managed? If dictionary managed, what are the "NEXT" and "PCTINCREASE" values in effect for the tables being imported?
2) Are your tablespaces underlying data files in "autoextend" mode? If so, what are the NEXT and MAXSIZE values for the files?

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:02 (14May04) UTC (aka "GMT" and "Zulu"), 16:02 (14May04) Mountain Time)

 

[ksandbergfl]

Hi there. Your post caused me to think about the manner in which I am (was) importing.

Before I did the import, I was running a script (in effect) that would create all my objects in the target database first.... I did this because my tablespace names are different in 9i, and I wanted to ensure that my tables and indexes are in the right place. The tablespaces are LMT... the tables and indexes are all created with an initial extent equal to their current size... so I know space management is not the problem.

Some of my tables have constraints and triggers. My script was creating these objects, then disabling them. However, it appears that the IMPORT was applying the triggers and constraints anyway.

This morning, I created all my objects, as normal, except for the constraints and triggers. My IMPORT is blazing away.... I've already loaded over 3GB in about 90 minutes... it looks like my total load is gonna be about 3 hours.

Should I assume that it is good practice, in my case, to disable all my constraints and triggers in the source database first, BEFORE I do an export??? SO that the IMPORT does not use the triggers when importing?

 

[SantaMufasa]

K,

Certainly, your imports will be faster if they do not perform any trigger code or enforce any constraints. Following the import, you can enable constraints and deal with "disobedient" situations manually, but I'm worried about your triggers...If you disable your triggers, and assuming that your triggers are performing vital business-related activities, how do you accomplish the work of the triggers? Although you can enable the triggers after the fact, they certainly do not go back and "perform themselves" retroactively. You can, however, write a script that accomplishes the work of the triggers in batch mode, but at that point, are the efficiencies and performace going to differ appreciably from running the trigger(s) at import time?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:27 (17May04) UTC (aka "GMT" and "Zulu"), 09:27 (17May04) Mountain Time)

 

[ksandbergfl]

I do not need the triggers to fire in my IMPORT.

The source database will be EXPORT'ed then shut down.

The target database will be IMPORT'ed... procedures/triggers/views re-compiled, then the database opened up to the users.

 

[sbix]

You could disable LOGGING on those tables before importing