Import too slow

Hi all ,

I am trying to import a database in orcle 8. My export file
size is 2.4 GB. My DB block size is 8192 . It's taking around
12 hours . There are some big indexes on a massaive table.
Still.... is there are ways to improve on this time ?
Is this 12 hours to import is something normal ?

Regards

Definitely, 12 Hrs is not a normal, for a 2.4GB export file. What is the OS? Check if there is any other process going on in your server? Check your CPU utilization also.

Have you tried to import without indexes first, and after do an import with indexfile ... After that modify the index creation script with nologging, parallel and with sort_area_size high?

Cheers

If you are recalculating your statistics, it will add a lot of time.

Hi all ,

My Operating system is windows NT. How will
stop calculating statistics. ( or How will I know
it's calculating statistics )

Regards

Pls increase ur BUFFER(one of the Import Parameter) value other than default one and try.

Regards,
Sudheer
Project Lead,India

Most importantly, have you checked the alert log if at all there are any error whil import was in progress, specifically related with temporary tablespace?
as you said indexes are big, they will need a huge sort area in temporary tablespace for the same.
Are you using temporary tablespace as LMT ?

Can you attach a copy of the parfile you used.
Did you set BUFFER= and COMMIT= in the parfile?

MH

I was mot specifying the buffer size , so
the system was using the default.

Can someone recommand a better buffer size

How does COMMIT parameter going to affect the
performance.

Also can someone suggest a beter SORT_AREA_SIZE.
Presently the value for sort area & retian size
is 264000.

try BUFFER=20000000

and to build the index separately issue the below commands - if you have enough memory you can give more than 20Megs.
alter session set sort_area_size = 20000000 ;
alter session set sort_area_retain_size = 20000000 ;

In addition for large index :
Use the noLogging feature so the logging does not take place.
Use parallel degree creation to create the Index in parallel.
Make sure you give a Lot of Space on the Temp Tablespace.
Put the Temp Tablespace on a Separate Controller so that there is no contention.
Also if possible make sure this is the only session which is accesing the temp tablespace.

another suggestion. while importing use the parameter analyze=n

Quote:

---

Originally posted by tharakab
another suggestion. while importing use the parameter analyze=n

---

Hi,
can u explain why should I use analyze
while import..
And how the commit will slowdown the import process..
thanx in advance.
Pratheesh

Hi

Analyze=n means that your asking the import utility not to recalculate statistics..

You can try this..
create an index file of all sql statements containing the indexes..

and then import the table and data..
run the sql file for indexes in sqlplus with the nologiing cluase appended..

and also try to put a high value for buffer parameter depending upon available memory..

regards
Hrishy