Home » RDBMS Server » Server Utilities » Import Performance using sql *loader (Oracle 10g, Linux)
Import Performance using sql *loader [message #450307] Tue, 06 April 2010 05:38 Go to next message
nawasthi
Messages: 5
Registered: April 2010
Location: Nepal
Junior Member
I have a text file of 175G with the delimeter as '|*'. I want to import it using sql *loader. I know that sql *loader can load data parallely.
I can employ two schemes for importing it using sql *loader
a) with the text file being imported all at once using option parallel = true
b) with the single text file being broken down into files of smaller size(say 5G each or more) and then imported in parallel using parallel = true.
I can import it in somewhat powerful server with 128G of ram, 16 intel CPus and abundant hard disk space.
Now my question is
which option would be better in performance (in terms of time) it takes to import data?
I donot have sufficient time to test both of these approaches.
Please suggest me the faster approach of importing. I would be more than thankful to get the suggestion.
with regards
Nawa Raj

[Updated on: Tue, 06 April 2010 06:00]

Report message to a moderator

Re: Import Performance using sql *loader [message #450872 is a reply to message #450307] Sat, 10 April 2010 01:16 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
nawasthi!
Your problem is too difficult to test. I don't know if you are still looking for an answer. If you have tested and found out the soln, post it here. So that it could be useful to us.
But when you say, you can import it in two different schemas, I think splitting option should be faster. Atlease for following reasons:
1)If you split the file you can put them at differnt devices so while reading from two differnt devices, it is generally faster, compared to reading it from the same file.
2)Again if the two schemas or say two different tablespaces are located at different devices then writing to these devices could be faster then writing to single device.
Again splitting a file to smaller files could be an extra burden, but split fuction in fast enough in linux. I don't know about Windows. Hope you are using linux.
I am also creating a big text file for test purpose. Once i test it at my system, i will post the performance issues here.
Keep looking.

Re: Import Performance using sql *loader [message #451494 is a reply to message #450307] Thu, 15 April 2010 02:28 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Sql Loader will be faster, make sure you give Direct = true
2/ You can Break file in small chunks but it will take time.
3/ Disable ALL Indexes on Destination table.
4/ You might want to Run ONLY import process on your PC if you have resource problems.
Re: Import Performance using sql *loader [message #452245 is a reply to message #451494] Tue, 20 April 2010 22:54 Go to previous messageGo to next message
nawasthi
Messages: 5
Registered: April 2010
Location: Nepal
Junior Member
Thanks to all
maheshmhs I am waiting for your performance test.
I got it imported anyways but i expect suggestions for next time import!!!
Re: Import Performance using sql *loader [message #452246 is a reply to message #450307] Tue, 20 April 2010 22:57 Go to previous messageGo to next message
nawasthi
Messages: 5
Registered: April 2010
Location: Nepal
Junior Member
Maheshmhs
you have mistakenly considered scheme as schema.....Its not importing into 2 schemas.. i can have flexibility of using any of the two plans(scheme) i listed earlier.. to split the file or to import it all at once...
Re: Import Performance using sql *loader [message #452343 is a reply to message #452245] Wed, 21 April 2010 08:02 Go to previous message
joy_division
Messages: 4951
Registered: February 2005
Location: East Coast USA
Senior Member
nawasthi wrote on Tue, 20 April 2010 23:54

I got it imported anyways but i expect suggestions for next time import!!!


YES SIR!
Previous Topic: Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb)
Next Topic: SQL LOAD
Goto Forum:
  


Current Time: Sun Sep 19 20:27:15 CDT 2021