Import Very slow

[Kishore]

I am importing schema with 4million(approx) rows in some of the tables. It takes around 8 hours. But export takes 30 Min. to 45 Min.
I am using DIRECT patch. I have increased buffer size, not commiting in between etc..

Is there anything else we can do to improve the performance.


Help on this is appreciated as this is effecting lot of other operations,as we need to do this every day to refresh other databases.

[pss]

Check the bug database on MetaLink. There is one out there associated with FIRST_ROWS optimizer being default in your Oracle instance. I had the same issue and had to switch over to all_rows optimizer.

Enjoy,
Sergey

[slimdave]

i expect that you are waiting for your indexes to be created, one-at-a-time. Verrrry slowww.

If you need to replicate every day, why not look at a proper replication methodology, instead of the execrable exp/imp method

[Kishore]

Here I am not importing into the same database every day. It could be a different database, different server. And the export also could be from different DB and server. I just gave an example in my first posting.

So replication is not a good idea.

[rohitsn]

Hi,
Consider splitting the Import into two separate processes, by using the following Import parameters :-

1) Run Import with INDEXFILE to build a file of index creation commands

2) Then run Import with
BUFFER -> set as large as you can
COMMIT=Y -> so don't run out of rollback.
INDEXES = N -> Build indexes after importing data.

3) Consider increasing SORT_AREA_SIZE to avoid/reduce sort data going to disk, but be aware that it is a per-user parameter.
Run the index creation script generated from INDEXFILE above.




Regards
Rohit S Nirkhe,
Oracle/Apps DBA
OCP 8i,9i

[Kishore]

I tried that there was some improvement, but not much. The import is still very slow.

Any other suggestions please.

[pando]

Use RAID 0

It takes me to import 15gb worth of data in 30 minutes or so, it's not that your import process is slow, it is most probably your hardware is slow, the bottleneck

[Kishore]

OK I will try. Because I tried all other stuff like increasing sort_area_size, using direct path and all other regular stuff does not give me much.

[edsperez]

Have you tried setting your extent size to a larger value, frequent allocation of extents can add up to the time to complete the import.

Do you have LOB columns in any of the tables?

[patnams]

Originally posted by Kishore
OK I will try. Because I tried all other stuff like increasing sort_area_size, using direct path and all other regular stuff does not give me much.

If you are not on Versions 8 and below, look at PGA_AGGREGATE_SIZE & WORK_AREA_SIZE=Auto, instead of SORT_AREA_SIZE. Anyway I dont expect this to solve your problem.

If your setup allows, try stopping Archiving.

You may also explore parameters like db_writer_processes, disk_asynch_io, dbwr_io_slave.

Since this is a write intensive operation, the basic problem almost always lies with Disk IO.