OraFAQ Forum: Server Utilities » sql loader direct path is taking more time

Home » RDBMS Server » Server Utilities » sql loader direct path is taking more time (Oracle 10.2.0.1.0, Redhat Linux)

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

sql loader direct path is taking more time [message #323704]

Thu, 29 May 2008 05:14

mahik
Messages: 2
Registered: May 2008

Junior Member

Hi,

I wants to load a csv file into oracle database, Here the Database software is of Release 10.2.0.1.0 and the operating system of Redhat Linux.

The CSV is having data of 120 million records, firstly i tried with the option of using insert /*+ APPEND */ into CAPTOR_TDR_MSC
where this table is an external table, to complete this task it has taken 40 mins.

Then i tried with the option :
sqlldr captor/captor control=captor_csv.ctl log=direct.log errors=500000 direct=true

it has taken 01:21:00.30 much of time. I assume that direct = true directly writes to data, and is better option than using insert /*+ APPEND */ into.... option.

Can you please explain me whats happened exactly for the slowness to load the data using direct=true option.

Any suggestions to improve this situation with direct=true.

Thanks in Advance
Mahik

Report message to a moderator

Re: sql loader direct path is taking more time [message #323707 is a reply to message #323704]

Thu, 29 May 2008 05:20

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

it has taken 01:21:00.30 much of time. I assume that direct = true directly writes to data, and is better option than using insert /*+ APPEND */ into.... option.

No, APPEND is also a direct path.

Quote:

Any suggestions to improve this situation with direct=true.

Use external table as you did. Why don't you want to use the good solution you have?

Regards
Michel

Report message to a moderator

Re: sql loader direct path is taking more time [message #323981 is a reply to message #323707]

Fri, 30 May 2008 03:51

mahik
Messages: 2
Registered: May 2008

Junior Member

I wants to reduce the time, from 40 mins also. Just trying for that.

Report message to a moderator

Re: sql loader direct path is taking more time [message #323993 is a reply to message #323704]

Fri, 30 May 2008 04:14

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

check this link.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6611962171229

It explain in more detail what @Michael has emphasised.

Regards

Raj

Report message to a moderator

Re: sql loader direct path is taking more time [message #323994 is a reply to message #323981]

Fri, 30 May 2008 04:14

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is better to reduce from the best one than from the worst one.
Add parallelism, if your CPU and disks allow it.

Regards
Michel

Report message to a moderator

Previous Topic:	EXPORT import error
Next Topic:	export and partitions (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 05:37:56 CDT 2024