Oracle-L: RE: Import to Oracle9i extremely slow

From: BALA,PRAKASH (HP-USA,ex1) <prakash.bala_at_hp.com>
Date: Wed, 26 Mar 2003 07:39:01 -0800
Message-ID: <F001.00573352.20030326073901@fatcity.com>

Here is an old email from Kirti that can help you.

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-----
Sent: Tuesday, March 25, 2003 15:00
To: Multiple recipients of list ORACLE-L

Hello List,

When I tried to import the same export file (made by Oracle 8.1.6.3) to both

Oracle8i(8.1.6.3) and Oracle9i (9.2.0.3) server, I found the import to Oracle9i
is much slower than import to Oracle8i.

Both servers have the same physical configuration (SUN E4500, 8G RAM, 8 CPUs,
EMC disks,HBA same setting, NIC 100 full duplex). During the import, there was no paging and swapping. The only difference which I could find is that with Oracle9i server, I use LMT (but not ASS) and AUM.

I did enable the 10046 trace and found the ela columns for wait event "SQL*NET message from/to
client" on the Oracle9i server is much bigger. I tested with different setting (sort_area_size, buffer, commit=y/n, no indexes, noarchivelog), I did not get any luck.

Does anybody have the same experience?

Thanks,

Michael

This e-mail contains privileged attorney-client communications and/or confidential information, and is only for the use by the intended recipient. Receipt by an unintended recipient does not constitute a waiver of any applicable privilege.

Reading, disclosure, discussion, dissemination, distribution or copying of this information by anyone other than the intended recipient or his or her employees or agents is strictly prohibited. If you have received this communication in error, please immediately notify us and delete the original material from your computer.

Sempra Energy Trading Corp. (SET) is not the same company as SDG&E or SoCalGas, the utilities owned by SET's parent company. SET is not regulated by the California Public Utilities Commission and you do not have to buy SET's products and services to continue to receive quality regulated service from the utilities.

--

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

Author: Michael Wu
INET: xwu_at_sempratrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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.net
--

Author: BALA,PRAKASH (HP-USA,ex1)
INET: prakash.bala_at_hp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------