Oracle import utility running 10 times slower than normal

I am having an issue with an import that has been running for 27 hours. Normally this import has taken 7 hours in the past. I am trying to import about 163 gig of data. The export was done using the old exp utility so I can not use datapump. I have tried the usual modifications to get the import to work faster which I will list later. Has anyone experience a slow down like this before and what can I do to resolve the issue. My UNIX team has offered very little so far so I am stuck with figuring this out on my own. I am running on an AIX server (5.3). importing from a SAN disk using a pipe that happens to be on a NAS drive to the database. In the past as I said this import took about 7 hours. So far I have tried the following to speed up the import to no avail.

Used a larger BUFFER size  we used 90M instead of 64M and saw an increase in performance but nothing significant. I think I sehould have increased it even higher and will next time I attempt the import.
 INDEXES=N during import  we did this also.
The database is not in archive log mode.

Will award extra points for a viable solution

batzmanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mrjoltcolaConnect With a Mentor Commented:
Its not really a buffer issue, its probably more of a "how fast can we write redo and data to disk" issue. You could decrease the checkpoint intervals by tweaking the MTTR_RECOVERY_TARGET. Check that your redo logs are on dedicated volumes or spindles.

Increasing SGA might help indirectly by increasing the redo log buffer, but otherwise, the increased cache wont help much, I think.

I think NFS was probably the biggest bottleneck. I was not aware that you were using NFS vs previously using some other SAN protocol (iSCSI?).

Anyway, good luck, I hope it works out. Feel free to ping back here if you get more info, even if the question is closed.
0
 
mrjoltcolaConnect With a Mentor Commented:
Perhaps are you importing over the network?

Has the SAN configuration changed?

Is CPU consumption high on the database server?

Were your past imports done in the same time of day with the same system load?

0
 
batzmanAuthor Commented:
thanks for your comment. Not importing over a network. Yes CPU consumption is high. The import was started over the weekend during a very light system load.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mrjoltcolaConnect With a Mentor Commented:
CPU consumption is high from your import or from other activity on the system?

I would be asking the SA guys whether something changed on the SAN or if your network link was reprovisioned to a different switch, etc.

Anything in the alert log of interest?

Its highly unusual for that much of a symptom change if nothing else on the system changed. You could try to review AWR/ADDM reports if you had a baseline to go from. If you had no baseline, that might not be much help except at least to identify the _current_ bottleneck.
0
 
batzmanAuthor Commented:
yes CPU usage is very high and right now I cant do anything about this. I have increased the buffer parm to double what is was. From 120 to 250 M. I also was using a pipe that existed on an NFS mount. I beleive that this was hurting me also. I moved the pipe to a local SAN disk. I am rerunning the import again now. I ran the import with trace on for 30 minutes and sent the output to Oracle for review. I also increased my SGA to doulble the size it was and turned off logging for all indexes. I could not turn of the index logging for LOBS but I will have to make do with what I did. Hopefully what I added here will help someone in the future. I think I will close this question as there appears to be no magic bullet for this. If I see significant increase in performance I will update this question before I close it.
0
 
batzmanAuthor Commented:
No solution. I am continuing working with Oracle to discover a resolution. I will bounce the server the next scheduled maintenance  to start fresh on the next attempt of this import. I have read that AIX sometimes does not release memory when proceses complete. Until then I did complete the import. It took 62 hours to complete
0
 
batzmanAuthor Commented:
As usual thanks to the community for responding. I have not completly solved my issue but I apprciate all of the feedback. I will continue to work this issue with Oracle support.
0
 
mrjoltcolaCommented:
Let us know what you find, good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.