Showing results for 
Search instead for 
Did you mean: 

Importing Data to oracle

Trusted Contributor

Importing Data to oracle

Hi List,

I recently installed Oracle RAC 10g without serviceguard.How long does it take to import 5 GB of data with about 40 milion records to new installed rac database.
in my test it took about 5 hours.Is it usual?
I think something is wrong.what is your idea?

Trusted Contributor

Re: Importing Data to oracle

I forgot to say, systems are two HP 7540 servers and each one has 8 GB RAM, 8GB swap and 4 750 Mhz PA-RISC CPU.Storage is a HP System Disk 2405 (VA 7110).
Steven E. Protter
Exalted Contributor

Re: Importing Data to oracle

I don't think the figure is that out of line.

If your VA7110 is set up raid 5 you can improve write performance by going with raid 1 or raid 1/0.

Oracle recommends raid 1 or 1/0 (commonly called 10) for data and redo. Other areas can be raid 5.

Steven E Protter
Owner of ISN Corporation
A. Clay Stephenson
Acclaimed Contributor

Re: Importing Data to oracle

There are not enough data to tell although at first glance it seems slow. This is a large number of small records. I suspect that the records are multiply indexed (possibly with composite keys) so each insertion could be a very expensive operation. It could also depend upon the order of the records because this can skew the construction of the underlying B-trees.

Without more data, it's not possible to tell if this is good or bad. If you do have many indices, you might find it more efficient to import with only a primary index and then add the other indices later.

You could also have severe i/o or memory bottlenecks or a very, very badly tuned database and/or OS. You just don't provide information.
If it ain't broke, I can fix that.
Brian Crabtree
Honored Contributor

Re: Importing Data to oracle

I would take a look at your buffer setting when importing. This could be low, which can cause the import to run slower. I would recommend 1-5m (1048576 = 1m).


Honored Contributor

Re: Importing Data to oracle

hi alireza,

Import is a big sequential process and to speed that, you may do the following:

a. Make sure your alert log doesn't have any "checkpoint not complete" or "archival required" messages. These messages indicates that you have insufficient log configured and you should add more.

b. Verify you SORT_AREA_SIZE for index builds, you should consider increasing it during the import.

c. Do everything in parallel. Don't just use a simple exp owner=, imp owner=. Export table by table (in parallel, just fire off several exports). Same applicable for Import.

d. Pre-create your indexes. Don't use import to create the indexes -- use imp ... indexfile=temp.sql to generate a script with the create indexes. Then, modify them to have nologging and parallel. Running them in sqlplus is much faster.

hope this helps!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
John Flanagan
Regular Advisor

Re: Importing Data to oracle

Seems slow to me.

It takes me about 1 hour to import about 1.1Gb of data into a database on an intel 1GHz (x2) Xeon with 1GB RAM.

Trusted Contributor

Re: Importing Data to oracle

Thank you.