SQLLDR conventional load via multiple sqlldr instances [message #612917] |
Sun, 27 April 2014 16:45 |
|
udik9999
Messages: 4 Registered: April 2014
|
Junior Member |
|
|
Hi all,
We are required to load huge files to our DB (~170 GB)
I'm trying to boost our performance by splitting the single big file into several smaller files and running multiple sqlldr instances (each instance will handle a different file).
We are currently using conventional load (non-direct) as we can't be sure that we will not get duplicate records (and we are counting on the PK in the table to catch these issues...)
The parallel execution of each sqlldr is done by using nohup command (i.e. nohup sqlldr <some parameters> &)
All seems perfect right?
Well, it's not!
I ran several tests (on the same machine, using the same data):
(1) Using one file --> Elapsed time was: 00:01:05.67
(2) Using 2 files --> Elapsed time was: 00:00:43.41 and 00:00:43.11
Up till now seems OK, I ran 2 instances in parallel, so the 2nd run was faster... So I expected that when I run 4 file loads in parallel, it will be faster, but:
(3) Using 4 files --> Elapsed time was: 00:02:48.15, 00:02:37.91, 00:02:36.74 and 00:02:42.48
You can see that the execution time was much longer this time...
BTW, I checked and we have 5 processors installed on our machine (0-4). This check was done using cat /proc/cpuinfo command.
Can anyone tell me if I'm doing something wrong here?
How come the performance seems to deteriorate when I use more sqlldr instances in parallel?
Many thanks,
Udi
|
|
|
|
|
|
|
|
|
|