Home » RDBMS Server » Server Utilities » SQLLDR conventional load via multiple sqlldr instances (Oracle, v11.2.0.3, Linux)
SQLLDR conventional load via multiple sqlldr instances [message #612917] Sun, 27 April 2014 16:45 Go to next message
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
Re: SQLLDR conventional load via multiple sqlldr instances [message #612919 is a reply to message #612917] Sun, 27 April 2014 17:21 Go to previous messageGo to next message
udik9999
Messages: 4
Registered: April 2014
Junior Member
Of course that the mentioned tests above were executed on a much smaller scale (total size of files was around 600 MB)
Re: SQLLDR conventional load via multiple sqlldr instances [message #612920 is a reply to message #612917] Sun, 27 April 2014 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 22726
Registered: January 2009
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>How come the performance seems to deteriorate when I use more sqlldr instances in parallel?
You can't get 9 women to make 1 baby in 1 month.

>BTW, I checked and we have 5 processors installed on our machine (0-4).
CPU is not the resource that bottlenecks the loading.

Disk the the SLOWEST resource in any computer system.
The load is only as fast as the slowest resource.

[Updated on: Sun, 27 April 2014 17:23]

Report message to a moderator

Re: SQLLDR conventional load via multiple sqlldr instances [message #612921 is a reply to message #612920] Sun, 27 April 2014 17:32 Go to previous messageGo to next message
udik9999
Messages: 4
Registered: April 2014
Junior Member
Hi BlackSwan,

Although I liked your baby example Smile I'm not in 100% agreement here, as we have programs doing tasks which us mortals can only dream of Smile
Can you please advise if there is any other way to boost our performance using non-direct load? Does it make sense to continue using the parallel sqlldr instances or is it redundant?
BTW, we are currently using ROWS=60000 (which is close to the non-direct load limit per commit point)

Any help will be greatly appreciated!

Thanks!
Re: SQLLDR conventional load via multiple sqlldr instances [message #612922 is a reply to message #612921] Sun, 27 April 2014 17:39 Go to previous messageGo to next message
BlackSwan
Messages: 22726
Registered: January 2009
Senior Member
I suspect that if you were to SQL Trace the load processes, you see they spend most of their time in I/O Wait.

Trade-offs!
In theory, you might be able to partition the table and place different partitions on different disks so you'd have only 1 or 2 concurrent processes loading data onto any specific hard disk drive.


In theory, theory and practice are identical.
In practice, theory and practice can be unrelated.
Re: SQLLDR conventional load via multiple sqlldr instances [message #612931 is a reply to message #612917] Mon, 28 April 2014 01:28 Go to previous messageGo to next message
John Watson
Messages: 4523
Registered: January 2010
Location: Global Village
Senior Member
Looking at the wait events should make it clear, and may suggest solutions. Can you post statspack or AWR reports for the periods covering your three tests?
Re: SQLLDR conventional load via multiple sqlldr instances [message #612987 is a reply to message #612931] Mon, 28 April 2014 08:30 Go to previous messageGo to next message
udik9999
Messages: 4
Registered: April 2014
Junior Member
I ran 2 tests today:
(1) Single file
(2) 4 files

AWR report was produced which includes both runs, but which part of the report you need?
Re: SQLLDR conventional load via multiple sqlldr instances [message #612988 is a reply to message #612987] Mon, 28 April 2014 08:32 Go to previous message
John Watson
Messages: 4523
Registered: January 2010
Location: Global Village
Senior Member
Upload the complete reports. Separate reports for each run, of course.
Previous Topic: Increasing parallel process in impdp in runtime
Next Topic: IMP Datapump error
Goto Forum:
  


Current Time: Tue Sep 02 14:24:17 CDT 2014

Total time taken to generate the page: 0.17665 seconds