Re: How to speed up import

From: Steve Perry <sperry_at_sprynet.com>
Date: Thu, 05 Sep 2002 06:08:25 -0800
Message-ID: <F001.004C8602.20020905060825@fatcity.com>

If believe that prior to 8i if you had a table with long or lob data and you used direct=y, only the DDL was exported. The data was left behind. In 8i and above, Oracle switches from direct to conventional and exports both the ddl and data for tables containing long/lob data. You will get a warning in the export log.

steve

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, September 05, 2002 5:48 AM

Are there any Limitations with using the 2 exp parameters :- DIRECT=Y
RECORDLENGTH=65535
-----Original Message-----

Sent: Wednesday, September 04, 2002 7:09 PM To: Multiple recipients of list ORACLE-L

First of all, export/import may not be a fool-proof method for backing up the database. Future releases of Oracle may not have FULL=Y option (so I heard/read somewhere). Having said that, following is an excerpt from my old posting discussing some ideas to improve export/import performance.

HTH...
- Kirti

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 (if rollback segments are

okay, do not use this).
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 no proportional 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 and such, I keep the quota on the target
tablespace to 0 to make it fail during import (something I just find easier to remember).
8) Set log= to some log file name to capture all (good and bad) messages from the import process.
9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint etc. indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc.
12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener.

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 for

users.
17) Time to hit the door..

Hope this helps...

Kirti Deshpande Verizon Information Services http://www.superpages.com

-----Original Message-----

Sent: Tuesday, September 03, 2002 4:35 PM To: Multiple recipients of list ORACLE-L

I do backup database nightly using export utility. When I restore database using import, it takes more than 6 hours to finish. Is there a way to speed up import process? Please advise.

Thanks,
David
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nguyen, David M
INET: david.m.nguyen_at_xo.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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.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

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Steve Perry
INET: sperry_at_sprynet.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