DBA Tools

    ociuldr (Source) is a free utility used to unload Oracle rows to text file, which can be used to prepare data for data warehouse, or do the data migration from Oracle to other databases, or moving data between different character set Oracle databases. To get the maximum performance, take the following steps.

    Tuning the query is the most important step, for massive data unload, you can use oracle parallel query to speed up the query. Else please check the execution plan, make sure it use the best plan. I will not tell you how to do tuning here.

    Tuning the network speed. You can run ociuldr at a machine with good connection to the database host. Or tuning Oracle network TDU and SDU parameter to use large network packet size. Run on the database server always get the best performance of unloading, but it may add extra load to the database server.

    Setting the proper performance parameters value. You can set the multiple block read parameter of Oracle by "read=blocks" option, and set the sort area size for sorting with "sort=MB" option, or set the hash area size for hash join with "hash=MB" option, and the serial direct read option (tell oracle use direct path read method when accessing full table) with "serial=1" option, direct path read can avoid Oracle read the block into database buffer cache and flush existing blocks out to avoid the performance impacting of other sessions.

    Choose the right array fetch size with "array=rows" option. Usually large array size will help to improve the unload performance.