OraFAQ Forum: Server Utilities » SQL Loader

Home » RDBMS Server » Server Utilities » SQL Loader - High Volume of Date

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

SQL Loader - High Volume of Date [message #586430]

Thu, 06 June 2013 04:43

ORAGENASHOK
Messages: 240
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

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586431 is a reply to message #586430]

Thu, 06 June 2013 04:49

dariyoosh
Messages: 538
Registered: March 2009
Location: 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

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

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

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586454 is a reply to message #586430]

Thu, 06 June 2013 07:28

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, 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.

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586466 is a reply to message #586454]

Thu, 06 June 2013 08:43

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

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

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586467 is a reply to message #586466]

Thu, 06 June 2013 08:53

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

I know all that. It depends on the individual situation.

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586469 is a reply to message #586467]

Thu, 06 June 2013 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586470 is a reply to message #586469]

Thu, 06 June 2013 09:24

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, 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

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586475 is a reply to message #586470]

Thu, 06 June 2013 10:47

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

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

Report message to a moderator

Re: SQL Loader - High Volume of Date [message #586678 is a reply to message #586430]

Sun, 09 June 2013 08:19

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 15:58:42 CST 2025