Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

Importing Data to oracle

Delrish
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?

Regards,
Alireza
7 REPLIES
Delrish
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.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
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).

Thanks,

Brian
Yogeeraj_1
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!

regards
yogeeraj
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.

John.
Delrish
Trusted Contributor

Re: Importing Data to oracle

Thank you.