RE: Import/Export performance

Thank you very much Kirti for providing such nicely detailed items. This is exactly what I was looking for.

Thanks again. Roy

>
>Hi Roy,
>Planning, planning and planning is required to make this a piece-of-cake..
>
>This one is a rather long one.. Sorry..
>
>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 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, 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 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 fine and 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-----
>> From: Roy Ferguson [SMTP:rferguso_at_level1.com]
>> Sent: Wednesday, April 18, 2001 2:31 PM
>> To: Multiple recipients of list ORACLE-L
>> Subject: Import/Export performance
>>
>> I will be re-building a database using export/import and would like to
>> know what
>> specific things I can look at or do prior to doing the export and the
>> import to
>> increase the time it takes as well as to limit the number of invalid
>> objects.
>>
>> The database is 8.0.5.2.1 on Sun Sparc Solaris 2.6 and has 35,000 objects.
>>
>> I will be importing into an 8.1.6 database on the same O/S.
>>
>> Thanks in advance. Roy
>>
>>
>--
>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
>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).

Roy E. Ferguson II
Intel Sacramento
916-854-1123

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Roy Ferguson
  INET: rferguso_at_level1.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).