sql loader time

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
dgupta70Asked:
Who is Participating?
 
hardikbeitCommented:
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
 
GGuzdziolCommented:
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
 
hardikbeitCommented:

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
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.

All Courses

From novice to tech pro — start learning today.