OraFAQ Forum: Server Utilities » How to increase SQL Loader performance

Home » RDBMS Server » Server Utilities » How to increase SQL Loader performance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to increase SQL Loader performance [message #653130]

Tue, 28 June 2016 05:27

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Hi,

I am new to SQL loader and create a batch file to insert the data in remote oracle server.It takes to much time to insert.
I try both conventional and direct path load but the performance is very poor.

This is my conventional path file
sqlldr ab004@test101/**** DATA='P:\Projects\Conventional\Report.CSV' CONTROL=P:\Projects\Conventional\Insert.ctl rows=2500 bindsize=20000000 LOG=Insert.log BAD=Insert.bad LOAD=20000

This Direct path file
sqlldr ab004@test101/**** DATA='P:\Projects\Conventional\Report.CSV' CONTROL=P:\Projects\Conventional\Insert1.ctl DIRECT=true LOG=Directload.log BAD=Insert.bad LOAD=20000

In both case I inserted 20k rows. In conventional path it took 84sec while in direct load it took 80sec.
Well I heard it upload millions of rows in sec but It seems it not work well.Help in if i am missing something in it.

Thanks in advance

Report message to a moderator

Re: How to increase SQL Loader performance [message #653134 is a reply to message #653130]

Tue, 28 June 2016 05:57

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think 80 seconds is a good time.
What is the IO rate you can afford?
What are the statistics at OS level? disk? cpu?...

Report message to a moderator

Re: How to increase SQL Loader performance [message #653137 is a reply to message #653134]

Tue, 28 June 2016 06:22

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

80 sec is too much when i have 2 million of rows to insert.
I need something to insert 1000 in 1sec.
I am using vista, 4Gb ram

Report message to a moderator

Re: How to increase SQL Loader performance [message #653138 is a reply to message #653137]

Tue, 28 June 2016 06:32

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Vista?! Who is using Vista for a professional need?
4GB?! no comment.

You didn't answer my questions. I bet your problem is the IO.

Remove the LOAD parameter, increase the ROWS one.
Note that using BINDSIZE overrides ROWS parameter, so it is useless to use both at the same time.
Your BINDSIZE of 20M is far too small for goods performances, use at least 10 times larger (but can you do it with a Vista with 4 GB?).

Report message to a moderator

Re: How to increase SQL Loader performance [message #653140 is a reply to message #653138]

Tue, 28 June 2016 06:54

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Also, since you speak of a "remote oracle server", how remote is it? Gigabit LAN or 14.4k Modem?

Report message to a moderator

Re: How to increase SQL Loader performance [message #653148 is a reply to message #653138]

Tue, 28 June 2016 07:49

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Michel Cadot :- Sorry for asking what is IO?

And the server machine has 48GB RAM. I also used only ROWS parameter but it takes same amount of time.

[Updated on: Tue, 28 June 2016 07:50]

Report message to a moderator

Re: How to increase SQL Loader performance [message #653152 is a reply to message #653148]

Tue, 28 June 2016 08:07

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

IO
OK so local RAM is important to set BINDSIZE/ROWS and server IO is important to insert the data into the table and, as Thomas said, between them network bandwidth is important.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653153 is a reply to message #653152]

Tue, 28 June 2016 08:18

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

I just check the I/O speed.
Check that pls

Attachment: speed.JPG
(Size: 70.89KB, Downloaded 2055 times)

Report message to a moderator

Re: How to increase SQL Loader performance [message #653157 is a reply to message #653153]

Tue, 28 June 2016 08:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

transfer rate of under 100Kbit/second is Bronze Age SLOW!
what is total amount of raw data that needs to be transferred?

Report message to a moderator

Re: How to increase SQL Loader performance [message #653158 is a reply to message #653157]

Tue, 28 June 2016 08:44

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

8GB file

Report message to a moderator

Re: How to increase SQL Loader performance [message #653160 is a reply to message #653158]

Tue, 28 June 2016 08:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Ashique wrote on Tue, 28 June 2016 06:44

8GB file

I am pleased to report that the transfer will complete in under 1 year!

SQL> select 8000000000/10000 seconds, 8000000000/10000/60 minutes, 8000000000/10000/60/60 days from dual;

   SECONDS    MINUTES	    DAYS
---------- ---------- ----------
    800000 13333.3333 222.222222

Report message to a moderator

Re: How to increase SQL Loader performance [message #653163 is a reply to message #653157]

Tue, 28 June 2016 09:18

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Its 100000Kbit.
Thats why I asked How to improve the performance.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653164 is a reply to message #653163]

Tue, 28 June 2016 09:24

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Quote:

8000000000/10000/60/60 days from dual;

That's hours, not days

Report message to a moderator

Re: How to increase SQL Loader performance [message #653165 is a reply to message #653164]

Tue, 28 June 2016 09:25

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Any reason why you can't run sqlloader on the server of the remote DB?
That would remove the network and client from consideration.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653166 is a reply to message #653163]

Tue, 28 June 2016 09:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Ashique wrote on Tue, 28 June 2016 07:18

Its 100000Kbit.
Thats why I asked How to improve the performance.

I don't know where you get 100000Kbit when screen shot clearly says 74Kbits.
I rounded up to 100Kbits which equals about 10kbytes/second.

Somewhere a bottleneck exists on your network.
My WAN supports in excess of 10Mbytes/second

SQL> select 8000000000/10000000 seconds, 8000000000/10000000/60 minutes from dual;

   SECONDS    MINUTES
---------- ----------
       800 13.3333333

Root cause & fix are external to Oracle & exist at networking level

Report message to a moderator

Re: How to increase SQL Loader performance [message #653168 is a reply to message #653166]

Tue, 28 June 2016 09:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>That's hours, not days
Correct.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653171 is a reply to message #653168]

Tue, 28 June 2016 09:37

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

My net is not that slow.
Pls check the image. I used another website.

Attachment: spee.JPG
(Size: 24.66KB, Downloaded 1837 times)

Report message to a moderator

Re: How to increase SQL Loader performance [message #653172 is a reply to message #653166]

Tue, 28 June 2016 09:38

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

BlackSwan wrote on Tue, 28 June 2016 16:27

I don't know where you get 100000Kbit when screen shot clearly says 74Kbits.

It might actually be worse. If he really UPloads that data from his local machine "into the cloud" or to wherever the DB server is he only has 2.7KBit

[Updated on: Tue, 28 June 2016 09:38]

Report message to a moderator

Re: How to increase SQL Loader performance [message #653173 is a reply to message #653171]

Tue, 28 June 2016 09:40

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Ashique wrote on Tue, 28 June 2016 16:37

My net is not that slow.
Pls check the image. I used another website.

And most of those test are pretty irrelevant, since they only measure the network speed "to somewhere in the internet", not the network speed "to the database server". You probably have to talk to people who know your infrastructure.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653174 is a reply to message #653173]

Tue, 28 June 2016 09:49

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Ok sure I ll talk to them.
It basically network issue right?
But currently i am inserted 20k in 80 sec.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653175 is a reply to message #653171]

Tue, 28 June 2016 09:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Ashique wrote on Tue, 28 June 2016 07:37

My net is not that slow.
Pls check the image. I used another website.

I REALLY don't care!
Create a "big" text file in the range of at least 100MB up to 1GB.
Do a network file copy from client to server & measure the time.
Calculate the average transfer rate.
Compute how long it will take to transfer the whole 8GB.

Oracle is the victim; not the culprit.
Oracle depends OS resources to move the data & is rate limited by available OS resources.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653176 is a reply to message #653174]

Tue, 28 June 2016 09:50

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Simplest way to find out if it's actually network is to upload the file onto the remote server and run sqlloader from there.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653177 is a reply to message #653175]

Tue, 28 June 2016 09:52

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Sure I ll check that

Report message to a moderator

Re: How to increase SQL Loader performance [message #653178 is a reply to message #653176]

Tue, 28 June 2016 09:53

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

cookiemonster :- Its giving sqlloader authentication error and I don't have any right to change there. Sad

Sad

[Updated on: Tue, 28 June 2016 09:55]

Report message to a moderator

Re: How to increase SQL Loader performance [message #653180 is a reply to message #653178]

Tue, 28 June 2016 09:57

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then get someone to fix it.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653219 is a reply to message #653175]

Wed, 29 June 2016 06:50

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

BlackSwan :- I tried to upload a file size of 200MB on server and it took 5min 30 sec to complete.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653220 is a reply to message #653219]

Wed, 29 June 2016 07:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

200MB*40=8GB
5.5min.*40=220minutes to transfer 8GB or almost 4 hours
Average transfer rate is only 0.6MB/sec. (200MB/330sec)
Oracle can't make your hardware any faster than it actually is.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653222 is a reply to message #653220]

Wed, 29 June 2016 07:46

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

BlackSwan But this is by file transfer to network but right now I insert data in oracle with sql loader it took 70mins for 70MB file. Sad

Sad

Don't know what to do?

[Updated on: Wed, 29 June 2016 07:46]

Report message to a moderator

Re: How to increase SQL Loader performance [message #653223 is a reply to message #653222]

Wed, 29 June 2016 07:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Ashique wrote on Wed, 29 June 2016 05:46

BlackSwan But this is by file transfer to network but right now I insert data in oracle with sql loader it took 70mins for 70MB file.
Don't know what to do?

70MB/420sec=.166666MB/sec

enable SQL Trace to learn where slowdown occurs

Report message to a moderator

Re: How to increase SQL Loader performance [message #653224 is a reply to message #653222]

Wed, 29 June 2016 07:53

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I told you what to do.

Report message to a moderator

Re: How to increase SQL Loader performance [message #653225 is a reply to message #653223]

Wed, 29 June 2016 07:55

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Its 70 mins Sad

Sad

not 7

Report message to a moderator

Re: How to increase SQL Loader performance [message #653226 is a reply to message #653224]

Wed, 29 June 2016 07:57

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Michel Cadot :- But I try on other machine also with rows=2500 and without load parameter with 48GB Ram and it took 70mins for 70GB file

[Updated on: Wed, 29 June 2016 07:57]

Report message to a moderator

Re: How to increase SQL Loader performance [message #653227 is a reply to message #653226]

Wed, 29 June 2016 08:06

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What did I say about ROWS and BINDSIZE?
What did I say you have to FIRST look at?

Report message to a moderator

Re: How to increase SQL Loader performance [message #653228 is a reply to message #653227]

Wed, 29 June 2016 08:20

Ashique
Messages: 15
Registered: June 2016
Location: Mumbai

Junior Member

Michel Cadot : So should I use 20M * 10 Bindsize without row parameter?

Report message to a moderator

Re: How to increase SQL Loader performance [message #653229 is a reply to message #653228]

Wed, 29 June 2016 08:24

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is one sure thing to do, but not the first one, the first one is to know what IO rate you can have on the server.

Report message to a moderator

Previous Topic:	To perform restore of two oracle database from a single backup
Next Topic:	Sql Loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 07 16:32:00 CDT 2026