topic Re: Importing Data to oracle in Operating System - HP-UX

Importing Data to oracle

Delrish — Mon, 24 Jan 2005 16:32:38 GMT

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

Re: Importing Data to oracle

Delrish — Mon, 24 Jan 2005 16:39:35 GMT

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).

Re: Importing Data to oracle

Steven E. Protter — Mon, 24 Jan 2005 16:42:35 GMT

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

Re: Importing Data to oracle

A. Clay Stephenson — Mon, 24 Jan 2005 16:46:35 GMT

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.

Re: Importing Data to oracle

Brian Crabtree — Mon, 24 Jan 2005 17:15:18 GMT

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

Re: Importing Data to oracle

Yogeeraj_1 — Mon, 24 Jan 2005 23:15:45 GMT

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

Re: Importing Data to oracle

John Flanagan — Tue, 25 Jan 2005 03:27:14 GMT

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.

Re: Importing Data to oracle

Delrish — Mon, 14 Feb 2005 12:44:10 GMT

Thank you.