-2

I am trying to upload a csv file into Oracle server through sqlldr for my batch file automation. It takes too much time to upload the file into Oracle server with sqlldr. Through batch it uploads 2100 rows in a minute. When I try to import data directly from the csv file from Oracle SQL Developer it takes 100k rows in a minute.

This is my .ctl file:

OPTIONS(SKIP=1)                                                               
LOAD DATA                                                                              
INFILE "D:\\TestBackup\Event.csv"                                 
INSERT into table EVENT                                                         
APPEND                                                                            
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'                             
TRAILING NULLCOLS        
(BRANCH_ID,TOTAL_SALES,TOTAL_LOST_SALES,TOTAL_INVENTORY,INSERTDATE sysdate
 )

This is my .bat file:

sqlldr abc123@test/demo1 DATA='D:\Event.csv"      
CONTROL=D:\Insert.ctl LOG=Insert.log BAD=Insert.bad

How to improve the performance?

Ashique Sheikh
  • 155
  • 2
  • 4
  • 13
  • Is your quoting correct on the `sqlldr` command or is it just not pasted in correctly? – Glenn Jun 23 '16 at 12:48
  • Have you looked at the documentation? You might want to consider changing BINDSIZE and/or READSIZE. [And see this](https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL1135). – Alex Poole Jun 23 '16 at 13:30
  • Try some changes in BINDSIZE value and speed up to 12 times now the insertion rate is 400 rows/sec. Any idea to increase around 1000 rows/sec. Thanks – Ashique Sheikh Jun 25 '16 at 05:47

1 Answers1

1

You can use the alter table statement to turn off logging on the table during the load. It would be interesting to see the difference with and without logging. Does the table have triggers that could be suspended too? Maybe drop indexes before the load and recreate after instead of them updating during the load?

Just throwing out some ideas that may speed things up a little. Please report back what, if any, works. Looking forward to see what has the most benefit.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • It's run fine on server. Firstly I run this from local therefor its takes too much time. Now it took 3 mins for million rows – Ashique Sheikh Sep 22 '16 at 12:59