Oracle-L: RE: trouble importing

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 16 May 2002 10:22:36 -0800
Message-ID: <F001.00462A1A.20020516102236@fatcity.com>

Bill - You don't say whether your system has multiple CPUs. That will SERIOUSLY affect the advantage from multiple import jobs. You will have to experiment with the number of import jobs that seem to produce the greatest overall performance.

You can have multiple import jobs read the same import file. Obviously you must divide the work somehow between them so they don't all try to load the same records, either divide based on user or table. If you divide by table, you end up specifying each table, kind of a hassle.

Since import is doing a lot of disk work, you can speed it up by carefully selecting the disk drives for the dump file and the Oracle files so they don't step on each other. This is the real advantage for having multiple copies of your dump file or multiple dump files.

A better idea is to go back and do the export again, specifying separate export files for the largest tables. Since export is so much faster than import, this shouldn't delay your overall project by much.

I would encourage you to consider alternatives to import. In order of speed, I have found the following to be true:

Clone the database. Basically the speed of your tape/disk drives. The only reason you might not want to do this is if the machines are different.
Transportable tablespaces. Like cloning, but by tablespace rather than the entire database.
Use the SQL*Net COPY command. I've found this to be about twice as fast as import. You can run multiple processes here also.
Export/Import
SQL*Plus - forget it!

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, May 16, 2002 12:41 PM
To: Multiple recipients of list ORACLE-L

will try multiple imports - can they go against the same physical dump file? or do I need to copy the dump file for each separate import?

will also restart with analyze=n - we're using RBO anyway

seem to be two ways with COMMIT param - COMMIT = Y and a large buffer (someone else's post) COMMIT = N and a large RBS (oracle recommendation) any thoughts on this?

I would love to use TTS but physically cannot ftp the 70G datafiles from our remote source (a client site) - the compressed dump file is just under 1Gb

-----Original Message-----
Sent: Thursday, May 16, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L

Bill -
http://www.orafaq.com/faqiexp.htm#SPEED for some tips. Do you have any alternatives to importing? Transportable tablespaces, database cloning, SQL*Net, for example?
If your server has multiple CPUs, you can start multiple import sessions. Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, May 16, 2002 11:09 AM
To: Multiple recipients of list ORACLE-L

I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8).

the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done.

have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import.

any ideas how to speed this up?

thx
--

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

Author: Magaliff, Bill
INET: Bill.Magaliff_at_lendware.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: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------