Shulou(Shulou.com)06/01 Report--

Recently, there is an industry project that needs to import big data from the file to oracle.

We use oracle's sqlldr to import data, and it takes too long to import when the amount of data is large: it takes 2.5 hours to import a table with 80 million records, which is too slow.

Later, the parallel import method of sqlldr was used to reduce the time to 0.8 hours. Here is a record of the use process.

Train of thought

Split the imported data file into 10 pieces, and then use multitasking to execute the sqlldr command for the corresponding number of data copies in parallel (of course, the same number of control files are required), and multiple clients import data into the database at the same time.

Here are two more important points, 1, how to automatically generate a number of sqlldr commands and a number of control files (a bit tired to write); 2, how to execute in parallel (one by one to execute more tired).

Here, with the help of the aggregator, the command and control files are automatically generated and executed in parallel.

Implement the step main program

Responsible for task control, task assignment, call subroutine.

Subroutine

Generate specific control files and sqlldr commands, and execute import commands to complete data loading

[note] here the parallel mechanism of the aggregator is used to execute multiple sqlldr commands at the same time; the system function is used to call system commands.

Actual effect

Because it is a program-controlled parallel task, the number of parallel tasks can be set according to the actual needs to optimize the machine performance.

The following figure records the import speed of sqlldr with different number of parallelism. The overall growth is linear. The more concurrent tasks, the faster the import speed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.