Solved

sql loader time

Posted on 2006-06-21
3
683 Views
Last Modified: 2008-02-01
Hi ,
   I am using sql loader to load a table having 10 columns.

data is by month and each month has approx 5 million rows.
It is taking 2.5 hours each to load the first and second month
With 3rd month time starts increasing by 1 hour which means 3rd month takes 3.5 hour fourth minth takes 4.5 hours.

Basically deterioration in the loading time.

Can anybody tell me why is this happeneing and how can this be avoided.

Also, I think it has anyhthing to do with index in the table. Every row loaded has to be updated in the index also.
If yes than how can this be avoided.

Thanks in advance
Deepak
0
Comment
Question by:dgupta70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16954940
Did You try to use direct path loading? (options (DIRECT=TRUE) in control file)

Time should increase because table is getting bigger and index is getting bigger so it's harder to update it. If You build table from scratch it could be worth dropping indexes and rebuilding them after load is finished.
0
 
LVL 3

Expert Comment

by:hardikbeit
ID: 16957305

Because of Index updation, it takes lots of time. Every row is updating the index.

You can improve your performance trought Direct path loading. DIRECT=TRUE bypass all the checking.

With regards,
Hardik
0
 
LVL 3

Accepted Solution

by:
hardikbeit earned 500 total points
ID: 16957314
Maximizing SQL*Loader Performance:

SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data loads.  These include:

1.   Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert statements.  The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format.  The fact that SQL is not being issued makes the entire process much less taxing on the database.  

2.   Disable Indexes and Constraints.  For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader.  

3.   Use a Larger Bind Array.  For conventional data loads only, larger bind arrays limit the number of calls to the database and increase performance.  The size of the bind array is specified using the bindsize parameter.  The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum length of each row.

4.   Use ROWS=n to Commit Less Frequently.  For conventional data loads only, the rows parameter specifies the number of rows per commit.  Issuing fewer commits will enhance performance.  

5.   Use Parallel Loads.  Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently.
$ sqlldr control=first.ctl  parallel=true direct=true

7.   Disable Archiving During Load.  While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.

8.   Use unrecoverable.  The unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs.  This option is available for direct path loads only.

This helps you.

Let me know in case of any query.

With regards,
Hardik
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question