Importing Data to oracle

[Delrish]

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

[Delrish]

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]

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

[A. Clay Stephenson]

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.

[Brian Crabtree]

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]

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

[John Flanagan]

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]

Thank you.