-B parameter

[Saurabh Shukla]

If our DB size is 200 GB and we are dump-loading a table of 1 GB then what should be the -B parameter during Ascii dump-load? Means what is the optimal value of -B in this scenario which will result a faster dump-load with less paging.

We are using 4 APWs, 1 BIW and 1 AIW.

 

[TomBascom]

It depends.

Try different values and see which one works best.

Don't be surprised if fairly low values (10,000) work well. A data dump is a sequential process and caches like -B are more oriented to random data access.

There are a lot of "knobs and dials" to tweak in a dump & load and there is no "one size fits all" solution. A lot depends on the hardware that you have available. The design of both the source and target database have a very significant impact as well. For a discussion of many of the issues see:

Highly Parallel Dump & Load and Surprising Benefits of Storage Areas

I may be biased but if you have a large database to be dumped, a tight window in which to do it and little direct experience in these matters you would be well advised to consider hiring an experienced consultant.