Parallel increases the speed of Oracle data import

There is a recent industry project that requires large data to be imported from files to Oracle.

We use Oracle 's sqlldr to import data, which is too long to import when the amount of data is large: Importing a table of 8 Thousands records takes 2.5 hours and is too slow.

later , the Sqlldr parallel import method was used to shorten the time to 0.8 hours, where the use process was recorded.

Ideas

split the data file you want to import into ten , and then use multitasking to execute the SQLLDR command of the corresponding number of copies (and, of course, the same number of control files). Multiple clients import data into the database at the same time.

here are two points more important,1, how to automatically generate a number of SQLLDR commands and several control files (write a little tired);2. How to execute concurrently (more tired from execution).

With the help of the collector, this tool completes the automatic generation of commands and control files, and then executes them in parallel.

implementation StepsMain program

Responsible for task control, task assignment, calling subroutine.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/C5/wKioL1PrA_ni7olrAAEtq8yTtOs482.jpg "style=" float: none; "title=" 2014-08-13_141422.jpg "alt=" Wkiol1pra_ni7olraaetq8yttos482.jpg "/>

Sub-Program

generate the specific control file and the Sqlldr command, and execute the import command to complete the data load

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/45/C3/wKiom1PrAuKgPbctAAGgb9EpE2Y398.jpg "title=" 2014-08-13_141433.jpg "style=" Float:none; "alt=" wkiom1praukgpbctaaggb9epe2y398.jpg "/>

Note: The parallel mechanism of the collector is used to execute multiple SQLLDR commands at the same time , and system commands are invoked using the systems function.

Actual effect

because it is the parallel task of program control, the number of parallel tasks can be set according to the actual need, and the machine performance is optimized.

The Sqlldr import speed is recorded in the case of different parallel numbers , the overall linear growth, the more concurrent tasks, the faster the import speed.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/45/C5/wKioL1PrA_vg5lZIAACehW8v0Y8881.jpg "title=" 2014-08-13_141443.jpg "style=" Float:none; "alt=" wkiol1pra_vg5lziaacehw8v0y8881.jpg "/>