Tuesday, April 19, 2011

Performance Tuning of Data Pump

The Data Pump utilities are designed especially for very large databases. If our site has very large quantities of data versus metadata, we should experience a dramatic increase in performance compared to the original Export and Import utilities. 

The Data Pump Export and Import utilities enable us to dynamically increase and decrease resource consumption for each job. This is done using the PARALLEL parameter to specify a degree of parallelism for the job. (The PARALLEL parameter is the only tuning parameter that is specific to Data Pump.) For maximum throughput, do not set PARALLEL to much more than 2x the CPU count.

There are few initialization parameters that affect Data Pump performance. Setting  certain initialization parameters can affect the performance of Data Pump Export and Import. In particular, we can try using the following settings to improve performance, although the effect may not be the same on all platforms.

DISK_ASYNCH_IO=TRUE
DB_BLOCK_CHECKING=FALSE
DB_BLOCK_CHECKSUM=FALSE

Additionally, the following initialization parameters must have values set high enough to allow for maximum parallelism:

PROCESSES
SESSIONS
PARALLEL_MAX_SERVERS

To check the performance by setting one of the parameter say of parallel. Click here 


No comments: