Import takes long time

Printable View

03-29-2008, 06:19 AM
iris

Import takes long time

hi, why does import takes longer time compared to export?

thanks,
03-29-2008, 07:24 AM
davey23uk

have you thought about what it is actually doing compared to export
03-29-2008, 10:16 AM
iris

Import takes long time

Hi, In my opinion import takes long time due to following factors:

1. enable constraints
2. creates tables
3. creates indexes
4. executes procedures, functions.

Iam I right ? Please confirm.

thanks,
03-30-2008, 02:26 PM
PAVB

It does not executes procedures, functions.
03-30-2008, 11:36 PM
syedfarukhali

Quote:

Originally Posted by iris

hi, why does import takes longer time compared to export?

thanks,

Hi,

It takes longer time because of indexes.
So Import the database with parameter indexes=no
import will be fast after that import the indexes.
03-31-2008, 02:18 AM
PAVB

Quote:

Originally Posted by syedfarukhali

...after that import the indexes.

huh? Did you mean "build" the indexes after?

Also, be sure you do not import constraints. Import would try to validate them so is going to be faster if you create constraints after import is complete including the NOVALIDATE parameter.
03-31-2008, 05:27 AM
abhaysk

Quote:

Originally Posted by syedfarukhali

Hi,

It takes longer time because of indexes.
So Import the database with parameter indexes=no
import will be fast after that import the indexes.

Import will anyway build indexes after dataload.. then what's the point?.. only thing that doesnt do is "parallel".. for that use expdp/impdp..

Abhay.
03-31-2008, 05:52 AM
syedfarukhali

Quote:

Originally Posted by abhaysk

Import will anyway build indexes after dataload.. then what's the point?.. only thing that doesnt do is "parallel".. for that use expdp/impdp..

Abhay.

While importing with param index=no indexes will not load in the database
after data load in the database we can give the param indexfile to create the indexes

2)If you are really in Hurry give commit=no while importing it will load the data fast but make sure your rollbaack segment must be very huge.
03-31-2008, 02:55 PM
Mr.Hanky

Quote:

Originally Posted by syedfarukhali

While importing with param index=no indexes will not load in the database
after data load in the database we can give the param indexfile to create the indexes

2)If you are really in Hurry give commit=no while importing it will load the data fast but make sure your rollbaack segment must be very huge.

Be carefull with using no commit, if the table is very large you will blow out your undo and then that table import will rollback and you will have to re-import it, and that sucks.

I prefer using a buffer and commit to avoid that.

COMMIT=Y
BUFFER=12000000

Something like that helps, you will also avoid the rare instance (IME) where the default buffer is not large enough to swallow the longest column in the table, I've seen that once or twice.
04-03-2008, 02:30 AM
gopu_g

you can go for datapump...if you are using the oracle10g.

Other wise you can follow this steps as the above person said

COMMIT=Y
BUFFER=12000000
just like commit=y you can use nolog=y
direct=y

or

you can use incremental export

at the time of exporting you can export the indexes in to another file...
you can get lot of solution in the Google.... :-)
04-08-2008, 03:42 PM
OracleDisected

Hi,

My two cents:
1) Add DB writer processes (parameter DB_WRITER_PROCESSES, requires bounce)
2) Increase SORT_AREA_SIZE in order to reduce Temporary (disk,hence slower) usage when index creation takes place.

and DIRECT=y is only valid for export, not for import (unless it's undocumented)
04-08-2008, 06:17 PM
pando

nolog=y?

wtf LOL
04-09-2008, 01:36 AM
OracleDisected

Yeah, that too Pando... NOLOG[FILE] is for datapump.
04-09-2008, 05:05 AM
PAVB

Quote:

Originally Posted by OracleDisected

My two cents:
1) Add DB writer processes (parameter DB_WRITER_PROCESSES, requires bounce)
2) Increase SORT_AREA_SIZE in order to reduce Temporary (disk,hence slower) usage when index creation takes place.

and DIRECT=y is only valid for export, not for import (unless it's undocumented)

Are you serious? are you seriously suggesting to alter SGA, add DBWR and recycle the instance just to process an import?

If some of my guys comes to my desk with such an idea I would certainly hit him/her in the head with a frying pan but don't worry, I don't know you and I'm a very polite person so you have a free pass :rolleyes:
04-09-2008, 10:37 AM
OracleDisected

Sure, have you tried to build a 30Gb index with a 64K SORT_AREA_SIZE? it takes days... specially when storage is internal disks.

About the DBWR, well ... it works for us and takes account of queueing theory...

Regarding the bounce issue, I must say we use this approach for test and development environments ... and I know and share the principle of not-bounce the DB unless you strictly need it, but also I've realized it's a pre-Oracle 8i precept or taboo, back those days a database may not return from a shutdown and anybody in that situation surely sweated blood

In case that 'no-frying-pan' courtesy is a one day offer, I'll be prepared with butter, hotcakes and honey.:D
04-09-2008, 11:33 AM
gandolf989

I'm surprised that no one mentioned transportable tablespaces. If this is something that you want to do on a regular basis, than transportable tablespaces could work. You might also consider replication, or read only standby database, depending on what your need is.

The main reason why imports take longer than exports is that imports generate redo, and export do not. There is also the business of creating indexes and foreign keys. With a normal import there will always be some sort of redo.