Home » RDBMS Server » Server Utilities » How to increase SQL Loader performance
How to increase SQL Loader performance [message #653130] Tue, 28 June 2016 05:27 Go to next message
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
Re: How to increase SQL Loader performance [message #653134 is a reply to message #653130] Tue, 28 June 2016 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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?...

Re: How to increase SQL Loader performance [message #653137 is a reply to message #653134] Tue, 28 June 2016 06:22 Go to previous messageGo to next message
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
Re: How to increase SQL Loader performance [message #653138 is a reply to message #653137] Tue, 28 June 2016 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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?).

Re: How to increase SQL Loader performance [message #653140 is a reply to message #653138] Tue, 28 June 2016 06:54 Go to previous messageGo to next message
ThomasG
Messages: 3211
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?
Re: How to increase SQL Loader performance [message #653148 is a reply to message #653138] Tue, 28 June 2016 07:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: How to increase SQL Loader performance [message #653153 is a reply to message #653152] Tue, 28 June 2016 08:18 Go to previous messageGo to next message
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 1363 times)
Re: How to increase SQL Loader performance [message #653157 is a reply to message #653153] Tue, 28 June 2016 08:39 Go to previous messageGo to next message
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?
Re: How to increase SQL Loader performance [message #653158 is a reply to message #653157] Tue, 28 June 2016 08:44 Go to previous messageGo to next message
Ashique
Messages: 15
Registered: June 2016
Location: Mumbai
Junior Member
8GB file
Re: How to increase SQL Loader performance [message #653160 is a reply to message #653158] Tue, 28 June 2016 08:49 Go to previous messageGo to next message
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

Re: How to increase SQL Loader performance [message #653163 is a reply to message #653157] Tue, 28 June 2016 09:18 Go to previous messageGo to next message
Ashique
Messages: 15
Registered: June 2016
Location: Mumbai
Junior Member
Its 100000Kbit.
Thats why I asked How to improve the performance.
Re: How to increase SQL Loader performance [message #653164 is a reply to message #653163] Tue, 28 June 2016 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quote:

8000000000/10000/60/60 days from dual;


That's hours, not days
Re: How to increase SQL Loader performance [message #653165 is a reply to message #653164] Tue, 28 June 2016 09:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: How to increase SQL Loader performance [message #653166 is a reply to message #653163] Tue, 28 June 2016 09:27 Go to previous messageGo to next message
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
Re: How to increase SQL Loader performance [message #653168 is a reply to message #653166] Tue, 28 June 2016 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>That's hours, not days
Correct.
Re: How to increase SQL Loader performance [message #653171 is a reply to message #653168] Tue, 28 June 2016 09:37 Go to previous messageGo to next message
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 1191 times)
Re: How to increase SQL Loader performance [message #653172 is a reply to message #653166] Tue, 28 June 2016 09:38 Go to previous messageGo to next message
ThomasG
Messages: 3211
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 Go to previous messageGo to next message
ThomasG
Messages: 3211
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.


Re: How to increase SQL Loader performance [message #653174 is a reply to message #653173] Tue, 28 June 2016 09:49 Go to previous messageGo to next message
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.
Re: How to increase SQL Loader performance [message #653175 is a reply to message #653171] Tue, 28 June 2016 09:50 Go to previous messageGo to next message
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.
Re: How to increase SQL Loader performance [message #653176 is a reply to message #653174] Tue, 28 June 2016 09:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: How to increase SQL Loader performance [message #653177 is a reply to message #653175] Tue, 28 June 2016 09:52 Go to previous messageGo to next message
Ashique
Messages: 15
Registered: June 2016
Location: Mumbai
Junior Member
Sure I ll check that
Re: How to increase SQL Loader performance [message #653178 is a reply to message #653176] Tue, 28 June 2016 09:53 Go to previous messageGo to next message
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

[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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then get someone to fix it.
Re: How to increase SQL Loader performance [message #653219 is a reply to message #653175] Wed, 29 June 2016 06:50 Go to previous messageGo to next message
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.
Re: How to increase SQL Loader performance [message #653220 is a reply to message #653219] Wed, 29 June 2016 07:12 Go to previous messageGo to next message
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.
Re: How to increase SQL Loader performance [message #653222 is a reply to message #653220] Wed, 29 June 2016 07:46 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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. Sad
Don't know what to do?

70MB/420sec=.166666MB/sec

enable SQL Trace to learn where slowdown occurs
Re: How to increase SQL Loader performance [message #653224 is a reply to message #653222] Wed, 29 June 2016 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I told you what to do.

Re: How to increase SQL Loader performance [message #653225 is a reply to message #653223] Wed, 29 June 2016 07:55 Go to previous messageGo to next message
Ashique
Messages: 15
Registered: June 2016
Location: Mumbai
Junior Member
Its 70 mins Sad not 7
Re: How to increase SQL Loader performance [message #653226 is a reply to message #653224] Wed, 29 June 2016 07:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: How to increase SQL Loader performance [message #653228 is a reply to message #653227] Wed, 29 June 2016 08:20 Go to previous messageGo to next message
Ashique
Messages: 15
Registered: June 2016
Location: Mumbai
Junior Member
Michel Cadot : So should I use 20M * 10 Bindsize without row parameter?
Re: How to increase SQL Loader performance [message #653229 is a reply to message #653228] Wed, 29 June 2016 08:24 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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.

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


Current Time: Thu Mar 28 06:54:30 CDT 2024