Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: IMPORT sloooowwww
Here is a most excellent post from Kirti Deshpande,
kirti.deshpande_at_verizon.com earlier on some import /
export principles that may help you.
If you like it, pass on your thanks to him.
PEACE. Mike
Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc..
Improving Export performance:
1) Use direct=y. That will make the export process significantly faster.
2) Along with (1), setting 'recordlength' to multiples of db_block_size or
to its max value (65535) will help 'squeeze out' some more performance gain.
Although, this parameter is to be used when exporting/importing on different
OS where it has different default values, I use it for added performance
gain. You may want to give it a trial run to see if that would help.
3) I do not export indexes.
Improving Import Performance:
1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage. 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is noproportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built 7) For primary key constraint indexes, I keep the quota on the target tablespace to 0 to make it fail during import. (something I just find easier to remember).
14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db forusers.
-----Original Message-----
Sent: Thursday, August 29, 2002 8:23 AM
To: Multiple recipients of list ORACLE-L
Oracle 8.1.7 on Solaris 8
I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows).
I have tried:
In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports.
Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable.
TIA. Terry
Terry Ball, DBA
Birch Telecom
Work: 816-300-1335
FAX: 816-300-1800
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ball, Terry
INET: TBall_at_birch.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Aug 29 2002 - 13:23:48 CDT
![]() |
![]() |