batzman
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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.
Let us know what you find, good luck.
ASKER