export performance

[gregmorris]

i am trying to export a 120 GB 8.1.7 DB from solaris box to Linux ASE but the export and import are taking 4.5 and 8 hours respectively which far beyond acceptable range.
i am using direct=y recordlength=16K additionally.

how can i improve the performance of both export and import.
i want to use parallelism with both these processes but don't know how to?

thanks

[slimdave]

If you can split the export into multiple parallel steps, that would help. For example, export schemas independently of each other at the same time, or isolate four or five big tables and dedicate an export session to each of them alone. The aim is to maximize i/o here -- once you have reached the limit, that's about all you can do.

The major overheadon import is that Oracle will build indexes sequentially -- this can lead to greatly under-used resources. parallel imports of the multiple export files will help, but consider that you may still be building indexes on multiple tables simultaneously. For faster index building you really want to be building all indexes on a particular table at the same time. You reduce the chances that you will be re-reading blocks from disk many times, once for each index.

A solution to this problem is to ...

i) extract the index ddl statements from the export dump file using import with "indexfile = "

ii) run the regular import(s) with "indexes=n" (noting that some index creation is unavoidable, for unique constraint enforcement for example)

iii) submit multiple index creation statements at the same time (optionally with nologging and parallel for each index).

It would be very worthwhile boosting your sort_area_size before the import also.

Just to illustrate the benefits of submitting these index builds in parallel and in the right order, I recently had to rebuild index partitions on a fifty partition DW fact table -- sixteen indexes altogether. First time i did it i submitted index rebuilds in parallel, eight at a time (using DBMS_JOB), but i submitted them in the order of index name, then partition name. the result was that every index had to read physical i/o -- it took forever, effectively reading about 1600Gb of physical io. When i submitted the rebuilds in the order of partition then index name, each partition fitted within sga, total physical io was about 80gb, and it finished while i drank a cup of coffee.

Well that was a longer answer than i expected, but good luck with it. Max out those resources.

[Orca777]

Originally posted by slimdave
It would be very worthwhile boosting your sort_area_size before the import also.

Yes!!
If your maxchine has enaough men the sort_area_size could be as large as your greatest index.
Your sort_area_retianes_size should be large too.
Please rfecognize that every parallel process allocates the sort_area_size for it self!

also there is a init.ora -param some like parallel_auto_tuning;
Set this param and allocate enaugh LARGE_POOL, it's the best communiion-area for parallel Processes.

switch off archive log mode and after switching on again make a full hot-backup
do not place your Dump-file on the same Disk Oracle has it's files because then the import lasts a lot longe due to immense disk-head-movements

Orca

[slimdave]

Originally posted by Orca777
do not place your Dump-file on the same Disk Oracle has it's files because then the import lasts a lot longe due to immense disk-head-movements

that will certainly affect the non-index import, but the disk head movement is going to happen anyway -- probably a raid system. If i/o on the device the dump file is on then you could certainly spread them out over multiple devices. the index builds will not be affected by this, of course.

Good point on the parallel process sort_area_size orca. don't want to choke up the memory.

[gregmorris]

thanks Slimdave and Orca777
i'll put in these suggestions and let's see how it goes.

[ckwan]

U also can consider to use the TRANSPORTABLE TABLESPACE. This is much faster than conventional export method. But there are some limitation ... Pls Read the documentation to find out.

[sysdba]

I saw performance gain a lot by changing
buffer size if your have a lot free memory on
OS level.