Parallelism Enhances Oracle Data Import Speed
Improving Computing Capability
By: Jessica Qiu
May. 26, 2014 12:03 PM
Recently, we skillfully handled an industry project to import a great amount of data from file to Oracle in comparatively short time.
Later, by adopting the parallel import of sqlldr, the time is shortened to 0.8 hour. Here are the full details:
Train of thoughts
Split the data file to import into 10 shares. Then, with the multi-task parallelism, execute the sqlldr command for the corresponding shares. Needless to say, prepare the control files of the same amount. Then, multiple clients will start to import data to database all at the same time.
Please note these two things: 1. The way to generate multiple sqlldr commands and corresponding number of control files - a bit tried if writing them one by one; 2, The way to perform parallelly - ever more tired if performing one by one.
In this case, we use the tool, esProc by name, to generate the commands and control files automatically and then run parallelly.
Responsible for task control, task distribution, and calling sub-program.
Generate the specific control file and sqlldr command, and execute the import command to complete the data loading
Note: In this case, the parallelism feature of esProc is used to execute multiple sqlldr commands; The function system is used to call the system commands.
Because of the programmable-controlled parallel tasks, the number of parallel tasks can be set as necessary to tap into the machine’s full performance potential.
The below figure illustrates the sqlldr import speed for different degree of parallelism - linear increasing on the whole - the more parallel tasks, the faster the import would be.
Latest Cloud Developer Stories
Subscribe to the World's Most Powerful Newsletters
Subscribe to Our Rss Feeds & Get Your SYS-CON News Live!
SYS-CON Featured Whitepapers
Most Read This Week