Import too slow

[gsmathew]

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

[sai_sathish]

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.

[aarroyob]

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

[dknight]

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

[gsmathew]

Hi all ,

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

Regards

[sudheer_nm]

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

Regards,
Sudheer
Project Lead,India

[launchpad]

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 ?

[Mr.Hanky]

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

MH

[gsmathew]

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.

[Giani]

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.