Home » RDBMS Server » Server Utilities » SQL Loader - High Volume of Date
SQL Loader - High Volume of Date [message #586430] Thu, 06 June 2013 04:43 Go to next message
ORAGENASHOK
Messages: 187
Registered: June 2006
Location: Chennai
Senior Member
Hi,

I have 15 million of records as csv, want to load through sqlloader.
Is sqlloader is the right option to load high volume of data?

I have loaded with 2.5 lac records which has taken 4 mins to load.

so pls suggest me for better performance on this.

Thanks
Ashok T
Re: SQL Loader - High Volume of Date [message #586431 is a reply to message #586430] Thu, 06 June 2013 04:49 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e16638/glossary.htm#PFGRF95075
Quote:

SQL*Loader

Reads and interprets input files. Use this tool to load large amounts of data.


The delay is not necessarily always related to the volume of the data being loaded but it can depend also on several different factors such as: bad data being rejected due to table integrity constraint violation or when there are many different foreign key constraint to other tables, etc.

Regards,
Dariyoosh

[Updated on: Thu, 06 June 2013 04:52]

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586432 is a reply to message #586430] Thu, 06 June 2013 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so pls suggest me for better performance on this.


SQL*Loader or external tables are the best options you can use.

Regards
Michel
Re: SQL Loader - High Volume of Date [message #586454 is a reply to message #586430] Thu, 06 June 2013 07:28 Go to previous messageGo to next message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, UK
Senior Member
As well as what Dariyoosh said, you could try taking the database out of NOARCHIVELOG mode during data loads, but I suspect the problem is one of configuration.
Re: SQL Loader - High Volume of Date [message #586466 is a reply to message #586454] Thu, 06 June 2013 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Putting in NOARCHIVELOG means:
1/ Get the authorization from all applications to stop the database
2/ Stop all applications
3/ Stop the database
4/ Restart the database and change the archived log mode
5/ Load
6/ Stop the database
7/ Restart the database and change the archived log mode
8/ Take a backup
9/ Restart the applications and call the clients to tell them they can work

Is the time to load worth this? Most often, in the end, no.

Regards
Michel
Re: SQL Loader - High Volume of Date [message #586467 is a reply to message #586466] Thu, 06 June 2013 08:53 Go to previous messageGo to next message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, UK
Senior Member
I know all that. It depends on the individual situation.
Re: SQL Loader - High Volume of Date [message #586469 is a reply to message #586467] Thu, 06 June 2013 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 21951
Registered: January 2009
Senior Member
>It depends on the individual situation.
What is an example where taking the DB out of archive mode to load data is best solution?
Re: SQL Loader - High Volume of Date [message #586470 is a reply to message #586469] Thu, 06 June 2013 09:24 Go to previous messageGo to next message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, UK
Senior Member
I once worked for an organisation that wanted to set up a reporting database with around 750GB of data to be loaded. To expedite the data load, the database was put into NOARCHIVELOG mode. We put it into ARCHIVELOG before moving it into production. I was a contractor and that's what the management wanted, so that's what I did.

If it helps I went home and had a shower to cleanse myself later Laughing
Re: SQL Loader - High Volume of Date [message #586475 is a reply to message #586470] Thu, 06 June 2013 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a one shot/initialization load; of course you first put your database in NOARCHIVELOG more in this case as there is nothing in the database and so no one but you.

Regards
Michel
Re: SQL Loader - High Volume of Date [message #586678 is a reply to message #586430] Sun, 09 June 2013 08:19 Go to previous message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
ORAGENASHOK wrote on Thu, 06 June 2013 15:13

so pls suggest me for better performance on this.


Since you are more concerned about the time taken for laoding, using Direct path load would a good choice for you. Since you are using SQL*Loader utility, you can directly mention as direct=true in the command-line itself or alternatively mention it in the control file.

http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch09.htm

With many advantages, it also has limitations and disadvantages.

Data conversion occurs on the client side rather than on the server side. So, the NLS parameters in the initialization parameter file at server side won't be used. You will have to format mask in the control file.
For example, if the shell is bash or ksh then:-
% NLS_DATE_FORMAT='YYYYMMDD'
% export NLS_DATE_FORMAT


I would suggest, also you these while using direct path :
parallel=true , multithreading=true
However, I myself found an issue in multithreading, which I have posted long back, however, nobody could help me yet. Razz
http://www.orafaq.com/forum/m/585850/178722/#msg_585850

Previous Topic: Fatal error in data pump import
Next Topic: SQL Loader issue
Goto Forum:
  


Current Time: Sun Apr 20 17:04:17 CDT 2014

Total time taken to generate the page: 0.08045 seconds