Re: Improve "import" performance on an Oracle 8.1.7 SID ?????????????????????????

alu_at_dairynet.com (Wood Butcher) wrote in message news:<c4A38.20635$Xs4.4352799_at_feed.centurytel.net>...
> Hi,
>
> What tricks can I do to improve "import" performance on an Oracle 8.1.7 SID ??
>
> I want to decrease my import window.
>
> Thanks for any suggestions.
>
> Art

There are a number of things one might be doing that would give different answers to this question.

Are you doing a full import? If not, maybe your import could be more selective, say by table. This is especially true if you are refreshing a data set where only a few tables actually change.

Are you in archivelog mode? You may need to use different tuning parameters while importing. I just recently had a problem where an application is divided up into test and production schemata, where the test may need to be reloaded periodically but the production almost never. Limitations on disk space would make this a real problem, as it would generate many gigabytes of archive logs more than normal ops, which then would need to be backed up (not to mention what it would do should a restore be necessary to production). Answer? Split test into another instance, not in archivelog mode. After all, don't need to restore transactions if it screws up - just re-import.

I've seen cases where it is much faster to restore a cold backup than import.

Sometimes SQL*Loader can be faster, especially non-recoverable. But again, it depends what you are doing and what you have to work with.

Are your tablespaces fragmented? If you are doing something like importing into schemata where tables already exist or have existed, I've seen scenarios where the fragmentation just gets continued. Even if you drop all tables, you may need to coalesce if your pctincrease is 0. I've noticed an odd thing - even after coalescing and dropping/recreating a user, with multiple datafiles it sometimes will start filling the tablespace in the last datafile, then wrap to the first datafile. I hadn't noticed this before now because I always had a habit of recreating the tablespaces 'cause I noticed weird things left over in the distant past versions... check with the tablespace map option of OEM before and after import.

See the Utilities guide about creating indexes after the import.

jg

--
I'm not going to tell you three times, so it might all be lies.