sql loader time

Posted on 2006-06-21
Medium Priority
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
Question by:dgupta70
  • 2
LVL 14

Expert Comment

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.

Expert Comment

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,

Accepted Solution

hardikbeit earned 2000 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,

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

597 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